Entity Framework from Database First – CLI

Entity Framework (EF) is a Microsoft .NET ORM (Object Relational Mapping) framework for connecting to databases and abstracting away the necessary raw SQL from interacting with entities. It makes it way more efficient to have engineers focus on the logic of the application, rather than the syntax of the queries. Forget SQL Injection attacks as EF will automatically turn variables into SQL safe parameters in your queries and insert statements.

You can start from an existing database and map to entities and a db context in your project with a simple connection string. Here’s an example taking an existing postgres database and quickly preparing it for use within a .NET application.

dotnet new console -o ef-from-database
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design                                                                                   
dotnet ef dbcontext scaffold "Server=localhost;Database=YOURDATABASE;uid=username;password=password;" Npgsql.EntityFrameworkCore.PostgreSQL -o Data

After invoking the scaffold, you will have a folder in your project with models that represent each of the tables in your database as well as a context class. The context class is essentially your connection string and the models provide attributes that map to the columns in your database.

internal class Program
{
    private static void Main(string[] args)
    {
        Console.WriteLine("Example DB EF Existing Schema");

        var dbConnectOptions = NpgsqlDbContextOptionsBuilderExtensions.UseNpgsql<yourdatabaseContext>(
            new DbContextOptionsBuilder<yourdatabaseContext>(), 
            @"Server=localhost;Database=YOURDATABASEHERE;uid=username;password=password;");

        var context = new yourdatabaseContext(dbConnectOptions.Options);
        foreach (var x in context.YOURENTITYNAME.ToList())
        {
            Console.WriteLine(x.Name);
        }
    }
}

If your connecting to a MSSQL server, just swap out the package for MSSQL and then swap the Npgsql extensions for the MSSQL equivalent.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet ef dbcontext scaffold "Server=localhost;Database=YOURDATABASE;uid=username;password=password;" Microsoft.EntityFrameworkCore.SqlServer -o Data

internal class Program
{
    private static void Main(string[] args)
    {
        Console.WriteLine("Example DB EF Existing Schema");

        var optionsBuilder = new DbContextOptionsBuilder<yourdatabaseContext>();
        optionsBuilder.UseSqlServer(@"Server=localhost;Database=YOURDATABASEHERE;uid=username;password=password;");
        var context = new yourdatabaseContext(dbConnectOptions.Options);
        foreach (var x in context.YOURENTITYNAME.ToList())
        {
            Console.WriteLine(x.Name);
        }
    }
}