Entity Framework Core: For People In a Hurry

A quick and dirty guide to getting up to speed with EF Core

Foreword

Photo by Jan Antonin Kolar on Unsplash

While the debate rages on about object relational mappers (ORMs), it’s a near certainty you’ll need to know Entity Framework (EF) for modern Dotnet. I personally was not excited about EF for .net FW, but as a former Dapper user, I can tell you that EF Core is worth using. This is your chance to learn the basics in a hurry if you’re converting from something else. I’ve got a working project here that I’ll be referencing throughout the article.

The DB context

The DB context (DBC) is the main object you’ll use when interacting with the database. For this example, I’ve used Postgres which I’ve come to enjoy as a replacement to SqlServer. The DBC will take care of the following:

1. DB Connections

2. References to your schemas and tables

3. Configurations

4. Migrations

Perhaps the single biggest value-add for using EF is the migrations. Used properly, you can really get a lot out of this ORM.

Configuration

In order to use EF, you’ll need to install the following Nuget packages:

1. EF Core

2. EF Core Design (for migrations)

3. EF Core Conventions (if you wanna configure things like snake case)

Next we need to create a simple class that inherits DbContext, I’ve named mine AppDbContext:

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{}

Next we should create a method to register our DB context with our app services. In this method we’ll pass in our connection string and also go with a “snake case” naming convention for names:

public static void AddPostgresDatabase(this IServiceCollection services)
    {
        services.AddDbContext<AppDbContext>((_, options) =>
        {
            //configures us to use a postgres instance with the given connection string
            options.UseNpgsql($"Host=localhost:5432;Database=postgres;Username=postgres;Password=postgres;")
                .UseSnakeCaseNamingConvention();
        });
    }

It is very important to know that EF is not thread-safe, please make sure you use one instance per scope.

Now let’s call this method with our services container:

//setup DI
var services = new ServiceCollection();

services.AddPostgresDatabase();

var provider = services.BuildServiceProvider();

//get a reference to the DB, keep in mind EF is not thread-safe
var dbContext = provider.GetRequiredService<AppDbContext>();

Now that we can easily resolve a DB context, we’ll next talk about adding tables and schemas to your project.

Tables, Schemas and Columns

For my example project, I'll have just two tables, customers and orders. In order to use each table in code, we must first describe the tables in a way that the DB context can understand. There’s a few ways to do it in EF, you can use attributes and also a fluent configuration file. For my example I’ll be using my preference which is a separate configuration file per table. You should know that EF uses a lot of conventions to help auto-wire up some things. To make the orders table, I’ve done several things:

1. Defined each table as a standalone class, here the CLR data type will implicitly map to a SQL type. You can even use JSONB with the proper configurations.

2. Defined a configuration file for when I need to add things the conventions won’t. While we could put this in the AppDbContext file itself, I’ve chosen to keep things tidy by moving it out to a separate file. Make sure you add this line in the AppDbContext class or it won’t find the config files.

• Added DbSet<TEntity> entries to the AppDbContext so we can reference the tables.

So far so good, but we’re not quite there yet, we haven’t figured out the magic that handles the actual table creation. Fortunately this is where EF excels.

Migrations

Photo by Stijn te Strake on Unsplash

The single biggest value-proposition that EF makes over something like Dapper, is the in-built ability to handle migrations. Of course you can handle this yourself and/or use a third-party library — but EF makes a strong case to use the one that ships with it.

If you’re coming from Dapper, I promise EF Core is much more palatable than the EF6 of .net FW.

Before we get into the nitty-gritty, let’s take a quick high-level overview of how EF Core handles migrations:

1. First write your table/schema/relationships in the expressive bits we already did for orders and customers.

2. Next run a command that generates the C# that will ultimately generate the SQL.

3. After careful review of the emitted code, ask EF to perform the migration.

4. EF will keep internal state of things in both a EF-specific migration table and emitted C# in designer files.

Now for the nitty-gritty, let’s first ensure we have indeed created tables in C# and defined their relationships (and added them as DbSet<TEntity>). Reminder to make sure you have the design Nuget installed. Next, it’s time to use the EF Migration CLI:

1. Install it the CLI tools by running this command: dotnet tool install --global dotnet-ef

2. Next instruct EF to examine changes made since the last migration and create a set of files to, be sure give the migration a name. Do that by running this command: dotnet ef migrations add <name_of_your_migration>

3. At this point you’ll need to review the files generated, it will create an up and a down migration. If anything seems off, delete the files generated; adjust your configs and try again.

4. Once you’re satisfied, instruct EF to perform the migration by issuing the command: dotnet-ef database update

EF allows you to go up and down, you’ll notice a __EfMigrationsHistory table in your database — this is what EF uses to determine the instance of the needs a migration.

When it completes, be sure to make sure the changes were made to your liking. So now we have tables, it would sure be nice to use them. We’ll do some operations next.

Insert/Update/Tracker

An important concept in most ORM’s is the tracker. The tracker is a state-management object that determines what SQL and what traffic to the DB must be generated. The tracker enables EF to avoid roundtrips to the DB but because of this, EF isn’t thread-safe. Thread-safety isn’t a defect, rather it’s a heuristic to be aware of so you don’t mix operations with concurrent threads.

Let’s now go thru a few basic examples. EF emulates many different types of SQL operations and I’ll cover some of the more common ones.

A basic insert involves creating an object, adding it to a collection and then instructing the DbContext to finish the unit of work by calling SaveChangesAsync():

//add a customer
var customerId = Guid.NewGuid();

dbContext.Customers.Add(new Customer
{
    Id = customerId,
    FirstName = "Homer",
    LastName = "Simpson"
});

//complete a unit of work
await dbContext.SaveChangesAsync();

We’ll do similar now for an update:

//get and track changes
var customer = await dbContext.Customers.SingleAsync(x => x.Id == customerId);

//update first name
customer.FirstName = "Bart";

//save a unit of work
await dbContext.SaveChangesAsync();

EF can be instructed to avoid using the change tracker. This saves memory and forces EF to do a roundtrip to the DB. If an entity is changed, but not tracked — the change will not be saved:

//get without tracking, by default it won't be writable since it's not tracked
customer = await dbContext.Customers.AsNoTracking().SingleAsync(x => x.Id == customerId);

customer.FirstName = "Marge";

await dbContext.SaveChangesAsync(); // won't save Marge b/c the entity isn't being tracked

One common smell of ORM’s is an implied SELECT *, to avoid this we can use an anonymous object to create a projection with a subset of columns:

var customProjection = await dbContext
    .Customers
    //queryable
    .Where(x => x.Id == customerId)
    //custom SELECT
    .Select(x => new { x.Id, x.FirstName })
    //materialize it
    .SingleAsync(); //Anonymous object Id: <guid>, FirstName: Bart

There is also the concept of Include() and Join() in EF. Keep in mind the complexity starts to ratchet up and also the amount of glut that might come across the wire:

//add an order
dbContext.Add(new Order
{
    CustomerId = customerId,
    Quantity = 1,
    Amount = 1.23m
});

await dbContext.SaveChangesAsync();

//works, but not ideal
var includedListOfOrdersFromPeopleNamedBart = await dbContext
                       .Orders
                       .Include(x => x.Customer)
                       .Where(x => x.Customer.FirstName == "Bart")
                       .ToListAsync();

//inner join
var joinedListOfOrdersFromPeopleNamedBart = await dbContext
    .Orders
    .Join<Order, Customer, Guid, object>(
        dbContext.Customers,
        x => x.CustomerId,
        x => x.Id,
        (orderTable, customerTable) => new
        {
            //things to select
            orderTable.CustomerId,
            customerTable.FirstName
        }
    //anon list
    ).ToListAsync();

If you want to run raw SQL, EF has you covered as well:

var result = await dbContext.Database.SqlQuery<Customer>($"""
                                                       SELECT *
                                                       FROM billing.customers
                                                    """).ToListAsync();

Lastly, you can also do some bulk operations (bulk update/bulk delete) with some additional syntactic sugar:

await dbContext.Customers
    .ExecuteUpdateAsync(
        x => x.SetProperty(y => y.FirstName, "Homer")
    );

Queryable vs Materialized

If you’re unfamiliar with IQueryable vs IEnumerable, please take some time to go deeper than I can offer here. In the end IQueryable offers deferred execution while IEnumerable forces rows from the table into memory. In the end you will ask IQueryable to “materialize” the results into memory as well. You do need to have a grasp of how the order of your LINQ statements affects the efficiency of your query. In general you want to materialize the query at the end while doing the filtering early in the chain.

Danger Will Robinson

ORM’s are great but abstractions can easily lead anyone astray. If you have trouble with EF performance, you can easily see what SQL is being emitted by using the .ToSqlQuery() extension method (built-in) on an IQueryable. Other traps you might find yourself in is the dreaded N + 1 smell or lazy/eager loading to much.

It takes a long time to master SQL and takes longer to master the abstraction controlling it. In the end, performance and code-cleanliness might be at odds with each other.

Thanks for reading, happy coding!