Overview of Entity Framework Core

EF Core is the official .NET data access technology. It’s an ORM (Object Relational Mapper), providing some framework for how to work with the data layer. It allows accessing Tables, Stored Procedures, Views, and more.

NuGet

Microsoft.EntityFrameworkCore - just the core logic, no providers;
Microsoft.EntityFrameworkCore.SqlServer - SQL Server provider, installing it will also pull in the above package as its dependency.
Microsoft.EntityFrameworkCore.Design - a package that is needed for invoking migrations

DbContext

public class SamuraiContext : DbContext
{
    public DbSet<Samurai> Samurais { get; set; }
    public DbSet<Quote> Quotes { get; set; }
}

The tables names are inferred from the DbSet<> names.

The DbSet properties are not required. We can have a table without a DbSet pointing to it. DbSet is a convenience that:

simplifies navigation to that table from the DbContext
provides a name for the table

Without a DbSet defined in the DbContext, we can retrieve it as follows:

var set = context.Set<BattleSamurai>();

SaveChanges

The DbContext tracks changes so that they can be applied when we call SaveChanges() on it.

A SaveChanges() call applies all the requests to the database wrapped in a transaction, so failure of some requests will not corrupt the database.

NoTracking

In some cases, we’re not interested in DbContext’s tracking capabilities, especially when building web APIs where we often fire some query to the DB and dispose of the connection. In such cases we can enable NoTracking to improve performance.

We can do that in a few ways:

in queries - useful when we normally want tracking, but for some specific query we don’t:
```
var samurai = _context.Samurais.AsNoTracking().FirstOrDefault();
```

on DbContext - makes all queries NoTracking by default:

public class MyContext: DbContext
{
    public MyContext()
    {
        ChangeTracker.QueryTrackingBehaviour = QueryTrackingBehaviour.NoTracking;
    }
}

in Dependency Injection registration:

builder.Services.AddDbContext<SamuraiContext>(options =>
{
    options
        .UseSqlServer(builder.Configuration.GetConnectionString("SamuraiDb"))
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
});

Connection

In EF Core we need to explicitly provide info on which provider to use and what is the connection string. Here’s the simplest way to do it:

public class SamuraiContext : DbContext
{
    public DbSet<Samurai> Samurais { get; set; }
    public DbSet<Quote> Quotes { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=localhost;Database=SamuraiAppData;User Id=sa;Password=Qwerty1!;");
    }
}

In a real scenario, we’d obviously not hardcode the connection string.

Conventions

EF Core has a bunch of conventions that are applied by default. Here’re some of them:

Property called Id or <Class>Id will become a Primary Key
for every Foreign Key, an index is created
a string type is turned into nvarchar(MAX) in the DB (that’s a default from SQL Server provider, not the EF Core itself)
When C#‘s Nullable feature is enabled, reference types are “not nullable” by default - the same applies to DB table’s columns

DB tables names come from DbSet’s name or, if DbSet was not defined, from a class’s name.

// We can also name the table whatever we want
modelBuilder.Entity<Horse>().ToTable("MyHorses");

The conventions can be tweaked with:

Data Annotations
Fluent API (more powerful)

Migrations

Every change of data model is a prompt to do a migration. This way the database’s shape corresponds to our code model. Migrations are files and they are supposed to land in the VCS.

Migrations history is kept in the DB itself, in the “__EFMigrationsHistory” table.

Creating Migration

Command: dotnet ef migrations add <NAME>. It should be invoked from a directory of a .NET project containig DbContext.

The command tries to instantiate DbContext. By default it looks for that logic in the project in the current directory. If a different .NET project has this logic (e.g. some ASP.NET Core project), we need to point to it. Here’s an example:

dotnet ef migrations add -s ../WebApi/WebAPI.csproj MyMigration

When adding a migration, the file Migrations/*ModelSnapshot.cs is loaded and compared with the current DbContext. Based on the difference between these, a new migration file is generated.

A migration file’s name contains a timestamp and migrations’s name. It is a class with 2 methods:

Up - work to do when the migration is applied
Down - work to do when the particular migration is revoked

Applying Migration

The EF Tool can either execute the migration (good in DEV), or it can generate an SQL file with all the migration steps (good for PROD) - we can then execute it later on.

Generating a script: dotnet ef migrations script <FROM> <TO> - the SQL is printed to stdout. We can specify the migration to start from and the one we want to get to. By default it would generate SQL for all the migrations.

When applying migration with EF tool directly, it makes sure that the database exists and creates it if needed. When using the SQL script, it’s our responsibility to provide an existing database.

A fragment of logs when running the dotnet ef database update -v:

...
Opening connection to database 'MyNewDB' on server 'localhost'.
An error occurred using the connection to database 'MyNewDB' on server 'localhost'.
Opening connection to database 'master' on server 'localhost'.
Opened connection to database 'master' on server 'localhost'.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='60']
CREATE DATABASE [SamuraiAppData];
Executed DbCommand (461ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
...

Just like with migration creation, we need to point the EF Tool to the project that contains DbContext creation logic:

dotnet ef database update -s ../WebAPI/WebAPI.csproj

Tools

The migration tools can be installed as a NuGet package, or as a dotnet CLI tool (dotnet tool install --global dotnet-ef). The CLI tool is invoked with dotnet ef.

Additionally, we’d need to install the Microsoft.EntityFrameworkCore.Design NuGet package to access the Migration APIs from code.

Scaffolding

The “Code First” approach is recommended, but it is also possible to kind of reverse engineer a DB into models. The EF Tool has the scaffold subcommand for such a usecase.

Relations

One-to-Many

If an entity contains a collection of another entity, EF Core treats it as a one-to-many relationship.

The child entity can optionally have a property of a type of a parent - it will be a reference. The child may also have an ID property that would be a Foreign Key to the parent. EF Core will initialize these properties for us.

public class Quote
{
    public int Id { get; set; }
    public string Text { get; set; }

    // optional
    public Samurai Samurai { get; set; } // Navigation Property (reference to parent)
    public int SamuraiId { get; set; } // FK - it helps EF Core to know that Quote is  a "child"
}

Many-to-Many

To create many-to-many, we just need two entities referring to each other via collections:

public class Samurai
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Battle> Battles { get; set; } = new List<Battle>();
}

public class Battle
{
    public int BattleId { get; set; }
    public string Name { get; set; }
    public List<Samurai> Samurais { get; set; } = new List<Samurai>();
}

EF Core has a convention that understands it and it will create three tables:

Samurais
Battles
BattleSamurai - association of BattleIds with SamuraiIds (the PK will be a combination of these two FKs)

It’s called Skip Navigation, because in code we can forget about the intermediary table and just work with the two entities being related.

Join Class

For simplest cases, the above is enough. For some more advanced scenarios, we need to create additional class that joins our entities together.

Here’s an example of a class joining two entites (with some metadata):

public class BattleSamurai
{
    public int SamuraiId { get; set; }
    public int BattleId { get; set; }
    public DateTime DateJoined { get; set; }
}

This class represents the connection between a Samurai and a Battle. It’s similar to the SQL table that EF Core creates behind the sceness. We’ve added a DateJoined parameter as some metadata about the connection (often called “Payload”).

Additionally, we have to update the DbContext to explicitly specify the relationship:

public class SamuraiContext : DbContext
{
    public DbSet<Samurai> Samurais { get; set; }
    public DbSet<Battle> Battles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Samurai>()
            // Many-to-many
            .HasMany(s => s.Battles)
            .WithMany(b => b.Samurais)
            // Use the class we've created (BattleSamurai)
            .UsingEntity<BattleSamurai>(
                bs => bs.HasOne<Battle>().WithMany(),
                bs => bs.HasOne<Samurai>().WithMany())
            // Configure default value for DateJoined
            .Property(bs => bs.DateJoined)
            .HasDefaultValueSql("getdate()");
    }
}

For the simplest cases we wouldn’t have to do such configuration.

One-to-One

By default, the “principal” does not have to have any “dependant”, while the “dependant” has to have a “principal”.

In the DB, the “dependant” will have FK to the “principal”. The “principal” will not have FK to the “dependant”.

Example:

// Principal
public class Samurai
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Horse Horse { get; set; } // Naigation Property to the Dependant
}

// Dependant
public class Horse
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int SamuraiId { get; set; } // FK to the Principal
}

Queries

EF Core automatically generates SELECT queries when accessing objects. We can also customize the query that EF Core executes on the DbContext level.

Execution Methods

The following methods allow us to query on DbSets:

ToList()
First()
FirstOrDefault()
Single() - expects just one result to be found in the DB, throws otherwise
SingleOrDefault()
Last() - should be preceed by the OrderBy call
LastOrDefault() - should be preceed by the OrderBy call
Count()
LongCount()
Min()
Max()
Average()
Sum()
Find(PK_value) - not LINQ, it’s DbSet’s method that looks for a row with a specified key

Parametrizing

When invoking queries on the DB, the parameters that we provide may be parametrized or not. If we use a variable to provide some parameter, SQL will be parametrized. If we provide a hardcoded string, it will not be parametrized.

// Parametrized
var name = "Sampson";
var samurais = _context.Samurais.Where(s => s.Name == name).ToList();

// Not parametrized
var samurais = _context.Samurais.Where(s => s.Name == "Sampson").ToList();

By default, when pulling some entities, their related entities are not fetched. E.g., if I have a 1-to-Many relationship between samurais and quotes, if I query for the samurai, the list of quotes will be empty.

Eager Loading

With Eager Loading and its Include method, we can specify Navigation Properties that should be populated with the query:

var samuraiWithQuotes = _context.Samurais
    .Include(s => s.Quotes).ToList();

Filtered Query

When including some child, we can filter it as well:

var samuraiWithQuotes = _context.Samurais
    .Include(s => s.Quotes.Where(q => q.Text.Contains("abc")))
    .ToList();

Other Variations

Here are some other sceanarios for Eager Loading:

Include children and grandchildren:

context.Samurais
    .Include(s => s.Quotes) // many-to-many
    .ThenInclude(q => q.Translations); // one-to-many

Include just grandchildren:

context.Samurais
    .Include(s => s.Quotes.Translations);

Include different children:

context.Samurais
    .Include(s => s.Quotes)
    .Include(s => s.Clan);

Explicit Loading

Explicit Loading allows to load related data when the “base” entity is already loaded:

var samurai = context.Samurais.Find(13);
context.Entry(samurai).Collection(s => s.Quotes).Load(); // one-to-many
context.Entry(samurai).Reference(s => s.Horse).Load(); //one-to-one

Lazy Loading

Lazy Loading fetches the data from the DB as soon as we try to access some related data via a Navigation Property.

Limiting Properties

We can limit the properties to be returned with Select:

var someProps = context.Samurais.Select(s => new { s.Id, s.Name }).ToList();

In this case a list of anoymous objects will be returned, but we could use some known type as well.

An interesting usecase of that is to bring in just the count of some related data:

var someProps = context.Samurais
    .Select(s =>
        new { s.Id, s.Name, QuotesCount = s.Quotes.Count })
    .ToList();

LIKE

We can use SQL’s LIKE with the EF.Functions.Like function:

var samurais = _context.Samurais.Where(s => EF.Functions.Like(s.Name, "J%")).ToList();

Removing Data

Here’s how to remove some entity

var samurai = _context.Samurais.Find(4);
_context.Samurais.Remove(samurai);
_context.SaveChanges();

Many-to-Many

Here’s how to remove a Many-to-Many relationship:

var battle = _context.Battles.Include(n => n.Samurais).FirstOrDefault();
battle.Samurais.RemoveAll(s => true); // remove all samurais from a battle
// battle.Samurais.Remove(battle.Samurais.First()); // remove a single samurai from a battle
_context.SaveChanges();

The Include above is crucial. If we fetched battles without samurais, DbContext would not “know” that any samurais were in the battle, and the Remove call would not have any result.

In case when we’re using a more explicit Many-to-Many relationship, with a Join table class defined, we can remove an association as follows:

var bs = _context.Set<BattleSamurai>()
    .SingleOrDefault(bs => bs.BattleId == 1 && bs.SamuraiId == 10);
if (bs is not null)
{
    _context.Remove(b_s);
    _context.SaveChanges();
}

Adding Rows

We can add new rows:

context.Samurais.Add(newSamurai);

// or
context.Add(newSamurai);

Updates

We can update the entities as follows:

var samurai = _context.Samurais.FirstOrDefault();
samurai.Name += "San";
_context.SaveChanges();

Since the same DbContext instance was used to retrieve the object and to update it, EF Core knows which properties have been updated. The SQL sent to the DB will contain only the modified values.

In disconnected scenarios, the case is that we have to update some entity without retrieving it first (e.g., in some Web API). Then, we can do that as follows:

// 'data' could be delivered in the body of a PUT request

using var context = new SamuraiContext();
context.Update(data);
context.SaveChanges();

In such a case, since the context is fresh and does not “know” which properties have been changed, the SQL sent to the DB will contain all the properties of the entity to be updated.

Attach

When using Update in the disconnected scenarios with related data, EF Core is going to send unneeded requests to the DB. Here’s an example:

var samurai = _context.Samurais.Find(samuraiId);
samurai.Quotes.Add(
    new Quote
    {
        Text = "Some quote"
    });

// Disconnected
using (var newContext = new SamuraiContext())
{
    newContext.Samurais.Update(samurai);
    newContext.SaveChanges();
}

In this case, not only a new quote will be created, but also samurai’s properties will be updated (all of them). The newContext is fresh, it did not track anything, and it doesn’t “know” what exactly has changed. To make sure that DB is in proper state it will send to it everything.

We can use Attach to circumvent that.

// Disconnected
using (var newContext = new SamuraiContext())
{
    newContext.Samurais.Attach(samurai);
    newContext.SaveChanges();
}

When using Attach the DbContext (newContext in this case) will not treat the provided data as “Modified”. Instead, it will treat it as “Unchanged”. However, EF Core sees that one of the quotes in the samurai does not have a value for the Id (PK), and for the SamuraiId (FK). It will understand that this data needs to be sent to the DB.

Another approach would be to add the Quote to the DB directly, instead of doing that via a samurai. For this to work, we need to have a FK property of Quote:

var quote = new Quote { Text = "Some text", SamuraiId = samuraiId };
using var newContext = new SamuraiContext();
newContext.Quotes.Add(quote);
newContext.SaveChanges();

A similar issue that we had with a single entity when updating it in a Disconnected scenario occurs if we want to update some entity with relations:

var quote = context.Samurais
    .Include(s => s.Quotes)
    .First(s => s.Id == 2)
    .Quotes
    .First();

quote.Text += "Some update";

// Disconnected
using var newContext = new SamuraiContext();
newContext.Quotes.Update(quote);
newContext.SaveChanges();

The Update() call here might be very “heavy”. It will not only update the quote, but also the samurai, and all the other quotes that it has! A fresh DbContext instance does not know what the previous state was, so it will try to update everything.

Replacing Update with Attach method will not help in this case, because it will just mark all entities as “Unmodified” and EF Core will not issue any updates at all. The modified quote already has Id and SamuraiId, so the previous solution doesn’t work. We should use the following approach:

newContext.Entry(quote).State = EntityState.Modified;
newContext.SaveChanges();

We’re manually informing EF Core that this quote was modified. Only this one entity will be updated, related data is left unchanged - just one UPDATE command will be sent to the DB.

Keyless Entities

EF Core 3.0 introduced the possibility to have entities without PK. Here’s how we can use it:

Create an entity:

public class SamuraiBattleStat
{
    public string Name { get; set; }
    public int? NumberOfBattles { get; set; }
    public string EarliestBattle { get; set; }
}

Add a DbSet to the DbContext:

public DbSet<SamuraiBattleStat> SamuraiBattleStats { get; set; }

Configure the entity to be keyless in OnModelCreating of the DbContext:

modelBuilder.Entity<SamuraiBattleStat>().HasNoKey();

Views

In case the SamuraiBattleStat is supposed to be a View in the DB, we need to configure it as follows:

modelBuilder.Entity<SamuraiBattleStat>().HasNoKey().ToView("SamuraiBattleStats");

Without that, EF Core would create a new table.

Raw SQL

In special cases, we can use raw SQL in EF Core. It might be more performant than relying on EF Core’s queries.

Migrations

For example, we might want to have a View or a Stored Procedure in our DB. To create that, we’d create an empty migration and add SQL to that migration. Here’s an example:

public partial class AddStoredProcedures : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
        @"CREATE PROCEDURE dbo.DeleteQuotesForSamurai
            @samuraiId int
            AS
            DELETE FROM Quotes
            WHERE Quotes.SamuraiId=@samuraiId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Remove
    }
}

Querying

Here’s an example of how we can invoke raw SQL with a DbContext:

var samurais = context.Samurais.FromSqlRaw("SELECT * FROM Samurais").ToList();

Since we used a DbSet, the result will be mapped to the entity of that DbSet. The entities will be tracked as well.

Stored Procedures

var samurais = _context.Samurais.FromSqlInterpolated(
    $"EXEC dbo.HomesWithNRooms {rooms}").ToList();

Other Requests

There is also another way to invoke SQL commands:

context.Database.ExecutrSqlRaw("some SQL");

As a response we’ll get just a number of rows affected by the command. We could use it to execute some stored procedure that does not return data.

Logging

We can configure logging in multiple ways.

Tagging

Every interaction with the DbContext may be tagged with a comment that will be visible on the SQL Server side.

var data = _context.Samurais.TagWith("Some comment").ToList();

LogTo

A DbContext class may be configured with a LogTo call:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.LogTo(Console.WriteLine);
}

We’ll see logs generated by EF Core in the console.

By default, all values are hidden, since they might be sensitive. We can disable that hiding with:

optionsBuilder.LogTo(Console.WriteLine)
    .EnableSensitiveDataLogging();

Generic Host

For apps using the Logging infrastructure, we don’t have to do anything, logs will already be there.

Batching

If at least 4 operations have been added to the DbContext, EF Core will execute a batch request instead of sending these operations/requests separately.

Testing →

Overview of Entity Framework Core

NuGet

DbContext

SaveChanges

NoTracking

Connection

Conventions

Migrations

Creating Migration

Applying Migration

Tools

Scaffolding

Relations

One-to-Many

Many-to-Many

Join Class

One-to-One

Queries

Execution Methods

Parametrizing

Related Data

Eager Loading

Filtered Query

Other Variations

Explicit Loading

Lazy Loading

Limiting Properties

LIKE

Removing Data

Many-to-Many

Adding Rows

Updates

Attach

Updating Related Entity

Keyless Entities

Views

Raw SQL

Migrations

Querying

Stored Procedures

Other Requests

Logging

Tagging

LogTo

Generic Host

Batching