Entity Framework (EF) Core is a lightweight, extensible, open source and cross-platform version of the popular Entity Framework data access technology, which can serve as an object-relational mapper (O/RM). [1]

  • With EF, data access is performed using a model, which is made up of entity classes and a context object that represents a session to query and save data with the database. EF supports the following model development approaches:

    • Generate a model from an existing database.

    • Hand-code a model to match the database.

    • Once a model is created, use EF Migrations to create a database from the model, which allow evolving the database as the model changes.

  • Instances of the entity classes are retrieved from the database using Language Integrated Query (LINQ).

  • Data is created, deleted, and modified in the database using instances of the entity classes.

1. Installing Entity Framework Core

Entity Framework Core (EF Core) is distributed as NuGet packages, with the necessary packages determined by the database system (SQL Server, SQLite, etc.) and required EF Core features. [2][3]

  • Make sure to install the same version of all EF Core packages shipped by Microsoft.

    • For example, if version 5.0.3 of Microsoft.EntityFrameworkCore.SqlServer is installed, then all other Microsoft.EntityFrameworkCore.* packages must also be at 5.0.3.

  • Use of tooling for EF Core migrations and reverse engineering (scaffolding) from an existing database requires installation of the appropriate tooling package:

    Always use the version of the tools package that matches the major version of the runtime packages.

    • dotnet-ef for cross-platform command line tooling

      # dotnet ef must be installed as a global or local tool
dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
      # dotnet ef can also be used as a local tool
dotnet new tool-manifest
dotnet tool install dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design

    • Microsoft.EntityFrameworkCore.Tasks for MSBuild tasks allowing build-time integration.

    • Microsoft.EntityFrameworkCore.Tools for PowerShell tooling that works in the Visual Studio Package Manager Console

2. DbContext

A DbContext is a combination of the Unit Of Work and Repository patterns that represents a session with the database and can be used to query and save instances of entities.

  • A DbContext instance, designed for a single unit-of-work, has a typically short lifespan, beginning with its creation and ending with its disposal.

  • A typical unit-of-work when using Entity Framework Core (EF Core) involves:

  • A DbContext is not thread-safe and doesn’t support parallel operations.

    • Don’t share contexts between threads.

    • Make sure to await all async calls before continuing to use the context instance.

    • Use separate DbContext instances for concurrent operations

    • Any code that explicitly executes multiple threads in parallel should ensure that DbContext instances aren’t ever accessed concurrently.

2.1. Lifetime, Configuration, and Initialization

  • Each DbContext instance must be configured to use one and only one database provider using a specific Use* call.

    public class ApplicationDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // use the SQL Server database provider
        optionsBuilder.UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }
}

  • Other DbContext configuration can be chained either before or after (it makes no difference which) the Use* call.

    builder.Services.AddDbContext<ApplicationDbContext>(
    options => options
        .EnableSensitiveDataLogging()
        .UseSqlServer(
            @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0")
        );

  • EF Core can be integrated into applications using dependency injection, typically via the AddDbContext method.

    • The AddDbContext extension method registers DbContext types with a scoped lifetime by default.

      In many web applications, each HTTP request corresponds to a single unit-of-work. 
      var connectionString =
    builder.Configuration.GetConnectionString("DefaultConnection")
        ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");

builder.Services.AddDbContext<ApplicationDbContext>(options =>
     options.UseSqlServer(connectionString));
// builder.Services.AddDbContext<ApplicationDbContext>(
//     options => options.UseSqlServer(connectionString),
//     contextLifetime: ServiceLifetime.Scoped,
//     optionsLifetime: ServiceLifetime.Scoped);

    • The ApplicationDbContext class must expose a public constructor with a DbContextOptions<ApplicationDbContext> parameter.

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

  • DbContext can be created with new, configured either by overriding OnConfiguring or by passing options to the constructor.

    public class ApplicationDbContext : DbContext
{
    private readonly string? _connectionString;

    public ApplicationDbContext() : this(
        connectionString: @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0")
    { }

    public ApplicationDbContext(string connectionString) => _connectionString = connectionString;

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) { }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (_connectionString is not null) optionsBuilder.UseSqlServer(_connectionString);
    }
}
    using var context = new ApplicationDbContext();
    using var context = new ApplicationDbContext(
    connectionString: @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    var contextOptions = new DbContextOptionsBuilder<ApplicationDbContext>()
    .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0")
    .Options;
using var context = new ApplicationDbContext(contextOptions);

  • In applications where dependency injection doesn’t provide a suitable DbContext lifetime or multiple units-of-work are needed within a scope, AddDbContextFactory can register a factory for creating individual DbContext instances.

    builder.Services.AddDbContextFactory<ApplicationDbContext>(
    options => options.UseSqlServer(
        @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0"));
    public sealed class MyService(IDbContextFactory<ApplicationDbContext> contextFactory)
{
    public async Task DoWorkAsync()
    {
        var tasks = Enumerable.Range(1, 10).Select(_ =>
        {
            using var context = contextFactory.CreateDbContext();
            // ...
            return Task.CompletedTask;
        });
        await Task.WhenAll(tasks);
    }
}

2.2. DbContext Pooling

While generally lightweight, creating and disposing DbContext instances can introduce overhead in high-performance scenarios, which EF Core mitigates through context pooling, reusing instances from an internal pool to minimize setup costs at program startup, rather than continuously.

Note that context pooling is orthogonal to database connection pooling, which is managed at a lower level in the database driver.

  • To enable context pooling, simply replace AddDbContext with AddDbContextPool:

    builder.Services.AddDbContextPool<ApplicationDbContext>(
    options => options.UseSqlServer(
        @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0"),
    poolSize: 1024);

    • The poolSize parameter of AddDbContextPool sets the maximum number of instances retained by the pool (defaults to 1024).

    • Once poolSize is exceeded, new context instances are not cached and EF falls back to the non-pooling behavior of creating instances on demand.

  • To use context pooling without dependency injection, initialize a PooledDbContextFactory and request context instances from it:

    var options = new DbContextOptionsBuilder<PooledBloggingContext>()
    .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0")
    .Options;

var factory = new PooledDbContextFactory<PooledBloggingContext>(options);

using var context = factory.CreateDbContext();
var allPosts = await context.Posts.ToListAsync();

2.3. Connection Pooling

With most databases, a long-lived connection is required for performing database operations, and such connections can be expensive to open and close.

  • EF Core relies on the underlying database driver (e.g., ADO.NET) for connection pooling, a client-side mechanism that reuses database connections to reduce the overhead of opening and closing connections repeatedly.

  • While generally enabled by default and configurable at the driver level (e.g., via connection string), connection pooling is separate from EF Core’s context pooling, which optimizes DbContext instance reuse.

  • EF Core typically opens and closes connections around each operation, returning them to the pool quickly.

3. Model

EF Core uses a metadata model to describe how the application’s entity types are mapped to the underlying database.

EF models are built using a combination of three mechanisms: conventions, mapping attributes, and the model builder API.

  • The model is built using a set of conventions - heuristics that look for common patterns.

  • The model can then be customized to override the configuration performed by conventions using mapping attributes (a.k.a., data annotations) and/or calls to the ModelBuilder methods (a.k.a., fluent API) in OnModelCreating.

    • Fluent API configuration has the highest precedence and will override conventions and data annotations.

      internal sealed class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
           .Property(b => b.Url)
           .IsRequired();
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string? Url { get; set; }
}

    • Data annotations will override conventions, but will be overridden by Fluent API configuration.

      internal sealed class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options)
    {
    }
}

[Table("Blogs")]
public class Blog
{
    public int BlogId { get; set; }

    [Required]
    public string? Url { get; set; }
}

    • EF Core model building conventions are classes that contain logic that is triggered based on changes being made to the model as it is being built.

      • EF Core includes many model building conventions that are enabled by default, which can be found in the list of classes that implement the IConvention interface.

      • Applications can remove or replace any of these conventions, as well as add new custom conventions that apply configuration for patterns that are not recognized by EF out of the box.

        internal sealed class BloggingContext(DbContextOptions<BloggingContext> options) : DbContext(options)
{
    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Conventions.Remove(typeof(ForeignKeyIndexConvention));
    }
}

  • While most configuration is data store-agnostic, providers can offer store-specific configurations and ignore unsupported ones.

  • The model builder debug view can be accessed in the debugger or directly from code.

    Console.WriteLine(context.Model.ToDebugString());
    Model:
  EntityType: Blog
    Properties:
      BlogId (int) Required PK AfterSave:Throw ValueGenerated.OnAdd
      Url (string) Required
    Keys:
      BlogId PK

  • A DbSet included in a DbContext registers its type as an entity in EF Core’s model, enabling EF Core to read/write instances to/from the database and, for relational databases, create tables via migrations.

3.1. Entity Types

  • A model includes entities through DbSet properties exposed on the context, specified in the OnModelCreating, and any types that are found by recursively exploring the navigation properties of other discovered entity types.

    internal sealed class MyContext : DbContext
{
    // exposed in a DbSet property
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // explicitly specified in OnModelCreating
        modelBuilder.Entity<AuditEntry>();
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    // discovered via the Blog.Posts navigation property.
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public Blog Blog { get; set; }
}

public class AuditEntry
{
    public int AuditEntryId { get; set; }
    public string Username { get; set; }
    public string Action { get; set; }
}

  • To exclude types from the model:

    // Data Annotations
[NotMapped]
public class BlogMetadata
{
    public DateTime LoadedFromDatabase { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Ignore<BlogMetadata>();
}

  • To exclude from migrations:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<IdentityUser>()
        .ToTable("AspNetUsers", t => t.ExcludeFromMigrations());
}

  • By convention, each entity type will be set up to map to a database table with the same name as the DbSet property that exposes the entity. If no DbSet exists for the given entity, the class name is used.

    // Data Annotations
[Table("blogs")]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .ToTable("blogs");
}

  • When using a relational database, tables are by convention created in the database’s default schema.

    // Data Annotations
[Table("blogs", Schema = "blogging")]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .ToTable("blogs", schema: "blogging");
}
    // define the default schema at the model level
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("blogging");
}

  • Entity types can be mapped to database views using the Fluent API.

    modelBuilder.Entity<Blog>()
    .ToView("blogsView", schema: "blogging");

    • It’s possible to map an entity type to a parameterless table-valued function (TVF) instead of a table in the database.

      modelBuilder.Entity<BlogWithMultiplePosts>().HasNoKey().ToFunction("BlogsWithMultiplePosts");

  • An arbitrary text comment can be set on the database table to document the schema in the database.

    // Data Annotations
[Comment("Blogs managed on the website")]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().ToTable(
        tableBuilder => tableBuilder.HasComment("Blogs managed on the website"));
}

  • Shared-type entity types (those using the same CLR type) require a unique name for configuration and usage, necessitating a Set call for the corresponding DbSet property.

    internal class MyContext : DbContext
{
    public DbSet<Dictionary<string, object>> Blogs => Set<Dictionary<string, object>>("Blog");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.SharedTypeEntity<Dictionary<string, object>>(
            "Blog", bb =>
            {
                bb.Property<int>("BlogId");
                bb.Property<string>("Url");
                bb.Property<DateTime>("LastUpdated");
            });
    }
}

3.2. Entity Properties

Each entity type in a model has properties that EF Core reads and writes from the database; in relational databases, these properties map to table columns.

  • By convention, all public properties with a getter and a setter will be included in the model.

    Specific properties can be excluded as follows:

    // Data Annotations
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    [NotMapped]
    public DateTime LoadedFromDatabase { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Ignore(b => b.LoadedFromDatabase);
}

  • By convention, when using a relational database, entity properties are mapped to table columns having the same name as the property.

    // Data Annotations
public class Blog
{
    [Column("blog_id")]
    public int BlogId { get; set; }

    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.BlogId)
        .HasColumnName("blog_id");
}

  • When using a relational database, the database provider selects a data type based on the .NET type of the property.

    // Data Annotations
public class Blog
{
    public int BlogId { get; set; }

    [Column(TypeName = "varchar(200)")]
    public string Url { get; set; }

    [Column(TypeName = "decimal(5, 2)")]
    public decimal Rating { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>(
        eb =>
        {
            eb.Property(b => b.Url).HasColumnType("varchar(200)");
            eb.Property(b => b.Rating).HasColumnType("decimal(5, 2)");
        });
}

  • Configuring a maximum length provides a hint to the database provider about the appropriate column data type to choose for a given array data type property, such as string and byte[].

    // Data Annotations
public class Blog
{
    public int BlogId { get; set; }

    [MaxLength(500)]
    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Url)
        .HasMaxLength(500);
}

  • Some relational data types support the precision and scale facets; these control what values can be stored, and how much storage is needed for the column.

    // Data Annotations
public class Blog
{
    public int BlogId { get; set; }
    [Precision(14, 2)]
    public decimal Score { get; set; }
    [Precision(3)]
    public DateTime LastUpdated { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Score)
        .HasPrecision(14, 2);

    modelBuilder.Entity<Blog>()
        .Property(b => b.LastUpdated)
        .HasPrecision(3);
}

  • Text properties are configured as Unicode by default.

    // Data Annotations
public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }

    [Unicode(false)]
    [MaxLength(22)]
    public string Isbn { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>()
        .Property(b => b.Isbn)
        .IsUnicode(false);
}

  • When mapping to a relational database schema, required properties are created as non-nullable columns, and optional properties are created as nullable columns.

    • A property is considered optional if it is valid for it to contain null.

    • If null is not a valid value to be assigned to a property then it is considered to be a required property.

    • By convention, a property whose .NET type can contain null will be configured as optional, whereas properties whose .NET type cannot contain null will be configured as required.

    • If nullable reference types are disabled, all properties with .NET reference types are configured as optional by convention (for example, string).

      // Without NRT
public class CustomerWithoutNullableReferenceTypes
{
    public int Id { get; set; }

    [Required] // Data annotations needed to configure as required
    public string FirstName { get; set; }

    [Required] // Data annotations needed to configure as required
    public string LastName { get; set; }

    public string MiddleName { get; set; } // Optional by convention
}
      // Without NRT with Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<CustomerWithoutNullableReferenceTypes>(c =>
    {
        c.Property(c => c.FirstName).IsRequired();
        c.Property(c => c.LastName).IsRequired();
    });
}

    • If nullable reference types are enabled, properties will be configured based on the C# nullability of their .NET type: string? will be configured as optional, but string will be configured as required.

      // With NRT
public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; } // Required by convention
    public string LastName { get; set; } // Required by convention
    public string? MiddleName { get; set; } // Optional by convention

    // Note the following use of constructor binding, which avoids compiled warnings
    // for uninitialized non-nullable properties.
    public Customer(string firstName, string lastName, string? middleName = null)
    {
        FirstName = firstName;
        LastName = lastName;
        MiddleName = middleName;
    }
}

  • A collation can be defined on text columns, determining how they are compared and ordered.

    // column level
modelBuilder.Entity<Customer>().Property(c => c.Name)
    .UseCollation("SQL_Latin1_General_CP1_CI_AS");
    // database level
modelBuilder.UseCollation("SQL_Latin1_General_CP1_CI_AS");

3.3. Keys

A key (primary key or alternate key) serves as a unique identifier for each entity instance.

  • By convention, a property named Id or <type name>Id will be configured as the primary key of an entity.

    internal class Car
{
    public string Id { get; set; }

    public string Make { get; set; }
    public string Model { get; set; }
}

internal class Truck
{
    public string TruckId { get; set; }

    public string Make { get; set; }
    public string Model { get; set; }
}

  • An entity’s primary key can also be explicitly configured using a single property or a composite key (multiple properties).

    // Data Annotations
internal class Car
{
    [Key]
    public string LicensePlate { get; set; }

    public string Make { get; set; }
    public string Model { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Car>()
        .HasKey(c => c.LicensePlate);
}
    // Data Annotations
[PrimaryKey(nameof(State), nameof(LicensePlate))]
internal class Car
{
    public string State { get; set; }
    public string LicensePlate { get; set; }

    public string Make { get; set; }
    public string Model { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Car>()
        .HasKey(c => new { c.State, c.LicensePlate });
}

  • By convention, on relational databases primary keys are created with the name PK_<type name>.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasKey(b => b.BlogId)
        .HasName("PrimaryKey_BlogId");
}

  • An alternate key serves as an alternate unique identifier for each entity instance in addition to the primary key; it can be used as the target of a relationship.

    • When using a relational database this maps to the concept of a unique index/constraint on the alternate key column(s) and one or more foreign key constraints that reference the column(s).

    • For simple uniqueness constraints, use a unique index; alternate keys in EF Core are read-only and can also serve as foreign key targets.

    • Alternate keys are usually created automatically by EF Core when a non-primary key property is used as the target of a relationship, so manual configuration is typically unnecessary.

    • By convention, the index and constraint that are introduced for an alternate key will be named AK_<type name>_<property name> (for composite alternate keys <property name> becomes an underscore separated list of property names).

3.4. Generated Values

Database columns can have their values generated in various ways: primary key columns are frequently auto-incrementing integers, other columns have default or computed values, etc.

  • On relational databases, a column can be configured with a default value; if a row is inserted without a value for that column, the default value will be used.

    // a default value
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Rating)
        .HasDefaultValue(3);
}
    // a SQL fragment
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Created)
        .HasDefaultValueSql("getdate()");
}

  • On most relational databases, a column can be configured to have its value computed in the database, typically with an expression referring to other columns:

    // a virtual computed column
modelBuilder.Entity<Person>()
    .Property(p => p.DisplayName)
    .HasComputedColumnSql("[LastName] + ', ' + [FirstName]");
    // // a physical computed column
modelBuilder.Entity<Person>()
    .Property(p => p.NameLength)
    .HasComputedColumnSql("LEN([LastName]) + LEN([FirstName])", stored: true);

  • By convention, non-composite primary keys of type short, int, long, or Guid are set up to have values generated for inserted entities if a value isn’t provided by the application.

  • A property can be explicitly configured to have its value generated on add or update:

    // Data Annotations
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public DateTime Inserted { get; set; }
}

// Fluent API
// protected override void OnModelCreating(ModelBuilder modelBuilder)
// {
//     modelBuilder.Entity<Blog>()
//         .Property(b => b.Inserted)
//         .ValueGeneratedOnAdd();
// }
    // Data Annotations
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime LastUpdated { get; set; }
}

// Fluent API
// protected override void OnModelCreating(ModelBuilder modelBuilder)
// {
//     modelBuilder.Entity<Blog>()
//         .Property(b => b.LastUpdated)
//         .ValueGeneratedOnAddOrUpdate();
// }

  • Unlike with default values or computed columns, how the values are to be generated depends on the database provider being used.

    • Database providers may automatically set up value generation for some property types, but others may require to manually set up how the value is generated.

    • For example, on SQL Server, when a GUID property is configured as a primary key, the provider automatically performs value generation client-side, using an algorithm to generate optimal sequential GUID values.

    • Similarly, byte[] properties that are configured as generated on add or update and marked as concurrency tokens are set up with the rowversion data type, so that values are automatically generated in the database.

  • A common request is to have a database column which contains the date/time for when the row was first inserted (value generated on add), or for when it was last updated (value generated on add or update).

    • Configuring a date/time column to have the creation timestamp of the row is usually a matter of configuring a default value with the appropriate SQL function.

      protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Created)
        .HasDefaultValueSql("getdate()");
}

    • Although stored computed columns seem like a good solution for managing last-updated timestamps, databases usually don’t allow specifying functions such as GETDATE() in a computed column. As an alternative, set up a database trigger to achieve the same effect:

      CREATE TRIGGER [dbo].[Blogs_UPDATE] ON [dbo].[Blogs]
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;

    UPDATE B
    SET LastUpdated = GETDATE()
    FROM dbo.Blogs AS B
    INNER JOIN INSERTED AS I
        ON B.BlogId = I.BlogId
END

  • To override value generation with an explicit value, simply set the property to any value that is not the CLR default value for that property’s type (null for string, 0 for int, Guid.Empty for Guid, etc.).

    var product = new Product
{
    // To override the auto-generated CreatedDate, set it to something other than DateTime.MinValue:
    CreatedDate = DateTime.Now.AddYears(-10)
};
await context.AddAsync(product);
await context.SaveChangesAsync();

public class Product
{
    public Guid Id { get; set; } // Will be auto-generated by default (often)
    public string? Name { get; set; }
    // modelBuilder.Entity<Product>().Property(p => p.CreatedDate).HasDefaultValueSql("getdate()");
    public DateTime CreatedDate { get; set; } // Will be auto-generated (often)
}

  • To disable value generation that has been set up by convention.

    // Data Annotations
public class Blog
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int BlogId { get; set; }

    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.BlogId)
        .ValueGeneratedNever();
}

3.5. Shadow and Indexer Properties

Shadow properties are properties that aren’t defined in .NET entity class but are defined for that entity type in the EF Core model, which are maintained purely in the Change Tracker and are useful when there’s data in the database that shouldn’t be exposed on the mapped entity types.

Indexer properties are entity type properties, which are backed by an indexer in .NET entity class to add additional properties to the entity type without changing the CLR class.

  • Shadow properties are most often used for foreign key properties, where they are added to the model by convention when no foreign key property has been found by convention or configured explicitly.

    • The relationship is represented by navigation properties, but in the database it is enforced by a foreign key constraint, and the value for the foreign key column is stored in the corresponding shadow property.

    • The property will be named <navigation property name><principal key property name> (the navigation on the dependent entity, which points to the principal entity, is used for the naming).

    • If the principal key property name starts with the name of the navigation property, then the name will just be <principal key property name>.

    • If there is no navigation property on the dependent entity, then the principal type name concatenated with the primary or alternate key property name is used in its place <principal type name><principal key property name>.

      internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    // Since there is no CLR property which holds the foreign
    // key for this relationship, a shadow property is created.
    public Blog Blog { get; set; }
}
      var post = new Post { Blog = new() };
await context.AddAsync(post);
await context.SaveChangesAsync();
var blogId = (int)context.Entry(post).Property(nameof(Blog.BlogId)).CurrentValue!;
Debug.Assert(post.Blog.BlogId == blogId);

  • Shadow properties can be configured using the Fluent API to call the string overload of Property<TProperty>(String) to chain any of the configuration calls for other properties.

    internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .Property<DateTime>("LastUpdated");
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

  • Shadow property values can be referenced in LINQ queries via the EF.Property static method or obtained and changed through the ChangeTracker API.

    var blogs = context.Blogs
    .OrderBy(b => EF.Property<DateTime>(b, "LastUpdated"));
    context.Entry(myBlog).Property("LastUpdated").CurrentValue = DateTime.Now;

  • Indexer properties can be configured using the Fluent API to call the method IndexerProperty to chain any of the configuration calls for other properties.

    internal class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().IndexerProperty<DateTime>("LastUpdated");
    }
}

public class Blog
{
    private readonly Dictionary<string, object> _data = new Dictionary<string, object>();
    public int BlogId { get; set; }

    public object this[string key]
    {
        get => _data[key];
        set => _data[key] = value;
    }
}

  • Indexer properties can be referenced in LINQ queries via the EF.Property static method or by using the CLR indexer property.

    var blog = new Blog();
blog["LastUpdated"] = DateTime.Now;
await context.AddAsync(blog);
await context.SaveChangesAsync();
var lastUpdated = await context.Blogs
    .Select(b => EF.Property<DateTime>(b, "LastUpdated"))
    .FirstAsync();
Console.WriteLine(lastUpdated);

  • Entity types that contain only indexer properties are known as property bag entity types.

    internal class MyContext : DbContext
{
    public DbSet<Dictionary<string, object>> Blogs => Set<Dictionary<string, object>>("Blog");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.SharedTypeEntity<Dictionary<string, object>>(
            "Blog", bb =>
            {
                bb.Property<int>("BlogId");
                bb.Property<string>("Url");
                bb.Property<DateTime>("LastUpdated");
            });
    }
}

3.6. Relationships

  • A relationship mapping is all about mapping the primary key/foreign key representation used in a relational database to the references between objects used in an object model.

  • A one-to-many relationship is used when a single entity is associated with any number of other entities.

    CREATE TABLE [Blogs] (
    [Id] int NOT NULL IDENTITY,
    CONSTRAINT [PK_Blogs] PRIMARY KEY ([Id])
);

CREATE TABLE [Posts] (
    [Id] int NOT NULL IDENTITY,
    [BlogId] int NOT NULL,
    CONSTRAINT [PK_Posts] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Posts_Blogs_BlogId]
        FOREIGN KEY ([BlogId]) REFERENCES [Blogs] ([Id]) ON DELETE CASCADE
);

CREATE INDEX [IX_Posts_BlogId] ON [Posts] ([BlogId]);
    // Principal (parent)
public class Blog
{
    public int Id { get; set; } (1)
    public ICollection<Post> Posts { get; } = []; // Collection navigation containing dependents (3)
}

// Dependent (child)
public class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; } // Required foreign key property (2)
    public Blog Blog { get; set; } = null!; // Required reference navigation to principal (4)
    // public int? BlogId { get; set; } // Optional foreign key property
    // public Blog? Blog { get; set; } // Optional reference navigation to principal
}

    A one-to-many relationship is made up from:

    1 One or more primary or alternate key properties on the principal entity; that is the "one" end of the relationship.
    2 One or more foreign key properties on the dependent entity; that is the "many" end of the relationship.
    3 Optionally, a collection navigation on the principal entity referencing the dependent entities.
    4 Optionally, a reference navigation on the dependent entity referencing the principal entity.

    • For cases where the navigations, foreign key, or required/optional nature of the relationship are not discovered by convention, these things can be configured explicitly.

      protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasMany(e => e.Posts)
        .WithOne(e => e.Blog)
        .HasForeignKey(e => e.BlogId)
        .IsRequired();
        // .IsRequired(false);
}

    • As with all relationships, it is exactly equivalent to start with dependent entity type (Post) and use HasOne followed by WithMany.

      protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasOne(e => e.Blog)
        .WithMany(e => e.Posts)
        .HasForeignKey(e => e.BlogId)
        .IsRequired();
        // .IsRequired(false);
}

  • A one-to-one relationship is used when one entity is associated with at most one other entity.

    CREATE TABLE [Blogs] (
    [Id] int NOT NULL IDENTITY,
    CONSTRAINT [PK_Blogs] PRIMARY KEY ([Id])
);

CREATE TABLE [BlogHeader] (
    [Id] int NOT NULL IDENTITY,
    [BlogId] int NOT NULL,
    CONSTRAINT [PK_BlogHeader] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_BlogHeader_Blogs_BlogId]
        FOREIGN KEY ([BlogId]) REFERENCES [Blogs] ([Id]) ON DELETE CASCADE
);

CREATE UNIQUE INDEX [IX_BlogHeader_BlogId] ON [BlogHeader] ([BlogId]); -- UNIQUE INDEX
    // Principal (parent)
public class Blog
{
    public int Id { get; set; } (1)
    public BlogHeader? Header { get; set; } // Reference navigation to dependent (3)
}

// Dependent (child)
public class BlogHeader
{
    public int Id { get; set; }
    public int BlogId { get; set; } // Required foreign key property (2)
    public Blog Blog { get; set; } = null!; // Required reference navigation to principal (4)
    // public int? BlogId { get; set; } // Optional foreign key property
    // public Blog? Blog { get; set; } // Optional reference navigation to principal

}

    A one-to-many relationship is made up from:

    1 One or more primary or alternate key properties on the principal entity; that is the "one" end of the relationship.
    2 One or more foreign key properties on the dependent entity; that is the "many" end of the relationship.
    3 Optionally, a reference navigation on the principal entity referencing the dependent entities.
    4 Optionally, a reference navigation on the dependent entity referencing the principal entity.

    • For cases where the navigations, foreign key, or required/optional nature of the relationship are not discovered by convention, these things can be configured explicitly.

      protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasOne(e => e.Header)
        .WithOne(e => e.Blog)
        .HasForeignKey<BlogHeader>(e => e.BlogId)
        .IsRequired();
}

    • As with all relationships, it is exactly equivalent to start with dependent entity type (BlogHeader) instead.

      protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BlogHeader>()
        .HasOne(e => e.Blog)
        .WithOne(e => e.Header)
        .HasForeignKey<BlogHeader>(e => e.BlogId)
        .IsRequired();
}

  • A many-to-many relationship is used when any number entities of one entity type is associated with any number of entities of the same or another entity type.

    • A many-to-many relationship requires a join entity (and table) to link the two sides, as a single foreign key is insufficient.

      CREATE TABLE [Posts] (
    [Id] int NOT NULL IDENTITY,
    CONSTRAINT [PK_Posts] PRIMARY KEY ([Id])
);

CREATE TABLE [Tag] (
    [Id] int NOT NULL IDENTITY,
    CONSTRAINT [PK_Tag] PRIMARY KEY ([Id])
);

CREATE TABLE [PostTag] (
    [PostsId] int NOT NULL,
    [TagsId] int NOT NULL,
    CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostsId], [TagsId]),
    CONSTRAINT [FK_PostTag_Posts_PostsId]
        FOREIGN KEY ([PostsId]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Tag_TagsId]
        FOREIGN KEY ([TagsId]) REFERENCES [Tag] ([Id]) ON DELETE CASCADE
);

CREATE INDEX [IX_PostTag_TagsId] ON [PostTag] ([TagsId]);

    • EF Core can hide the join entity type and manage it behind the scenes that allows the navigations of a many-to-many relationship to be used in a natural manner, adding or removing entities from each side as needed.

      public class Post
{
    public int Id { get; set; }
    public List<Tag> Tags { get; } = [];
}

public class Tag
{
    public int Id { get; set; }
    public List<Post> Posts { get; } = [];
}

3.6.1. Foreign and Principal keys

One-to-one and one-to-many relationships are defined by a foreign key on the dependent entity referencing the principal key (primary or alternate) of the principal entity. Many-to-many relationships are formed by two one-to-many relationships, each with its own foreign key referencing a principal key.

  • The property or properties that make up foreign key are often discovered by convention, and can also be configured explicitly using either mapping attributes or with HasForeignKey in the model building API.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasMany(e => e.Posts)
        .WithOne(e => e.Blog)
        .HasForeignKey(e => e.ContainingBlogId); // lambda expression.
        // .HasForeignKey("ContainingBlogId");   // property name

        // composite foreign key
        // .HasForeignKey(e => new { e.ContainingBlogId1, e.ContainingBlogId2 });
        // .HasForeignKey("ContainingBlogId1", "ContainingBlogId2");
}

  • By convention, foreign keys are constrained to the primary key at the principal end of the relationship. However, an alternate key can be used instead using HasPrincipalKey on the model building API.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasMany(e => e.Posts)
        .WithOne(e => e.Blog)
        .HasPrincipalKey(e => e.AlternateId); // lambda expression.
        // .HasPrincipalKey("AlternateId");   // property name

        // composite key
        // .HasPrincipalKey(e => new { e.AlternateId1, e.AlternateId2 });
        // .HasPrincipalKey("AlternateId1", "AlternateId2");
}

    • The order of the properties in the principal and foreign key must match, which is also the order in which the key is defined in the database schema.

    • There is no need to call HasAlternateKey to define the alternate key on the principal entity when HasPrincipalKey is used with properties that are not the primary key properties.

  • In many-to-many relationships, the foreign keys are defined on the join entity type and mapped to foreign key constraints in the join table.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasMany(e => e.Tags)
        .WithMany(e => e.Posts)
        .UsingEntity(
            l => l.HasOne(typeof(Tag)).WithMany().HasConstraintName("TagForeignKey_Constraint"),
            r => r.HasOne(typeof(Post)).WithMany().HasConstraintName("PostForeignKey_Constraint"));
}

EF Core relationships are defined by foreign keys.

  • Navigations are layered over foreign keys to provide a natural, object-oriented view for reading and manipulating relationships.

  • By using navigations, applications can work with graphs of entities without being concerned with what is happening to the foreign key values.

  • A navigation from dependent to principal is required if the relationship is required, which in turn means that the foreign key property is non-nullable. Conversely, the navigation is optional if the foreign key is nullable, and the relationship is therefore optional.

  • Reference navigations are simple object references to another entity, which represent the "one" side(s) of one-to-many and one-to-one relationships.

    • Reference navigations must have a setter, although it does not need to be public.

    • Reference navigations should not be automatically initialized to a non-null default value; doing so is equivalent to asserting that an entity exists when it does not.

    • When using C# nullable reference types, reference navigations must be nullable for optional relationships:

    • Reference navigations for required relationships can be nullable or non-nullable.

      public Blog? TheBlog { get; set; }

  • Collection navigations are instances of a .NET collection type; that is, any type implementing ICollection<T>, which represent the "many" side(s) of one-to-many and many-to-many relationships.

    • Collection navigations do not need to have a setter.

    • It is common to initialize the collection inline, thereby removing the need to ever check if the property is null.

      public ICollection<Post> ThePosts { get; } = [];
      Don’t accidentally create an expression bodied property, such as public ICollection<Post> ThePosts => [];, which will create a new, empty collection instance each time the property is accessed, and will therefore be useless as a navigation.

3.7. Indexes

Indexes are a common concept across many data store to make lookups based on a column (or set of columns) more efficient.

// Data Annotations
[Index(nameof(Url))]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
// Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasIndex(b => b.Url);
}
By convention, an index is created in each property (or set of properties) that are used as a foreign key.

  • A composite index, spanning more than one column, speeds up queries which filter on index’s columns, but also queries which only filter on the first columns covered by the index.

    // Data Annotations
[Index(nameof(FirstName), nameof(LastName))]
public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .HasIndex(p => new { p.FirstName, p.LastName });
}

  • By default, indexes aren’t unique: multiple rows are allowed to have the same value(s) for the index’s column set.

    // Data Annotations
[Index(nameof(Url), IsUnique = true)] // UNIQUE INDEX
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}
    // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasIndex(b => b.Url)
        .IsUnique(); // UNIQUE INDEX
}

  • In most databases, each column covered by an index can be either ascending or descending.

    • For indexes covering only one column, this typically does not matter: the database can traverse the index in reverse order as needed.

    • However, for composite indexes, the ordering can be crucial for good performance, and can mean the difference between an index getting used by a query or not.

    • In general, the index columns' sort orders should correspond to those specified in the ORDER BY clause of a query.

    • The index sort order is ascending by default.

      // Data Annotations
[Index(nameof(Url), nameof(Rating), AllDescending = true)] // make all columns as descending order
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}
      // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasIndex(b => new { b.Url, b.Rating })
        .IsDescending(); // make all columns as descending order
}

    • The index may also be specified the sort order on a column-by-column basis.

      // Data Annotations
[Index(nameof(Url), nameof(Rating), IsDescending = new[] { false, true })]
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public int Rating { get; set; }
}
      // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasIndex(b => new { b.Url, b.Rating })
        .IsDescending(false, true);
}

  • To create multiple indexes over the same set of properties, pass a name to the HasIndex, which will be used to identify the index in the EF model, and to distinguish it from other indexes over the same properties.

    // Fluent API
modelBuilder.Entity<Person>()
    .HasIndex(p => new { p.FirstName, p.LastName }, "IX_Names_Ascending");

modelBuilder.Entity<Person>()
    .HasIndex(p => new { p.FirstName, p.LastName }, "IX_Names_Descending")
    .IsDescending();
    // Data Annotations
[Index(nameof(FirstName), nameof(LastName), Name = "IX_Names_Ascending")]
[Index(nameof(FirstName), nameof(LastName), Name = "IX_Names_Descending", AllDescending = true)]
public class Person
{
    public int PersonId { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
}

  • A filtered index is an index that includes a WHERE clause, effectively indexing only a subset of the rows in a table to index only a subset of a column’s values, reducing the index’s size and improving both performance and disk space usage. [4]

    Filtered indexes are not supported by all RDBMS. 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .HasIndex(b => b.Url)
        .HasFilter("[Url] IS NOT NULL");
}

  • An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. [5]

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasIndex(p => p.Url)
        .IncludeProperties(
            p => new { p.Title, p.PublishedOn });
}
    Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data isn’t accessed resulting in fewer disk I/O operations.

3.8. Check Constraints

  • Check constraints are a standard database feature that enforces a condition on all table rows; inserts or updates violating the condition will fail.

  • Check constraints are similar to non-null and unique constraints but allow for arbitrary SQL expressions.

    modelBuilder.Entity<Product>()
    .ToTable(b =>
    {
        b.HasCheckConstraint("CK_Prices", "[Price] > [DiscountedPrice]");
        b.HasCheckConstraint("CK_PositivePrice", "[Price] > 0");
        b.HasCheckConstraint("CK_PositiveDiscountedPrice", "[DiscountedPrice] >= 0");
    });

4. Schemas

EF Core offers two ways to synchronize a model and database schema: Migrations (model as the source of truth, incrementally applying schema changes) and Reverse Engineering (database as the source of truth, scaffolding a model from the existing schema).

4.1. Migrations

The migrations feature in EF Core provides a way to incrementally update the database schema to keep it in sync with the application’s data model while preserving existing data in the database.

  • When a data model change is introduced, the developer uses EF Core tools to add a corresponding migration describing the updates necessary to keep the database schema in sync.

  • EF Core compares the current model against a snapshot of the old model to determine the differences, and generates migration source files; the files can be tracked in the project’s source control like any other source file.

  • Once a new migration has been generated, it can be applied to a database in various ways.

  • EF Core records all applied migrations in a special history table, allowing it to know which migrations have been applied and which haven’t.

Create and Drop APIs

The EnsureCreatedAsync and EnsureDeletedAsync methods provide a lightweight alternative to Migrations for managing the database schema, which are useful in scenarios when the data is transient and can be dropped when the schema changes, such as during development.

EnsureCreatedAsync and Migrations don’t work well together. 
// Drop the database if it exists
await dbContext.Database.EnsureDeletedAsync();
// Create the database if it doesn't exist
dbContext.Database.EnsureCreatedAsync();
// Get the SQL used by EnsureCreatedAsync
var sql = dbContext.Database.GenerateCreateScript();
Install the EF Core command-line tools.
# globally
dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
# locally
dotnet new tool-manifest
dotnet tool install dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
Set up a .NET generic host for app startup and lifetime management.
# create a new project
dotnet new console -o getting-started && cd getting-started/

# install SQLite provider
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

# install generic host package
dotnet add package Microsoft.Extensions.Hosting
// Program.cs
var builder = Host.CreateApplicationBuilder(args);

builder.Services.AddDbContext<BloggingContext>(options =>
    options.UseSqlite("Data Source=:memory:")); // ":memory:" creates in-memory DB

var app = builder.Build();

// Create and Drop APIs
// using (var scope = app.Services.CreateScope())
// {
//     using var context = scope.ServiceProvider.GetRequiredService<BloggingContext>();
//     await context.Database.EnsureDeletedAsync();
//     await context.Database.EnsureCreatedAsync();
// }

sealed class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    public BloggingContext(DbContextOptions<BloggingContext> options) : base(options)
    {
    }
}

public class Blog
{
    public int Id { get; set; }
    public string? Name { get; set; }
}
4.1.1. Managing Migrations

As the model changes, migrations are added and removed as part of normal development, and the migration files are checked into the project’s source control.

  • dotnet ef migrations add

    // add a migration
dotnet ef migrations add InitialCreate

    • EF Core will create a directory called Migrations in the project, and generate some files.

      $ tree ./Migrations/
./Migrations/
├── 20250213080400_InitialCreate.cs (1)
├── 20250213080400_InitialCreate.Designer.cs (2)
└── BloggingContextModelSnapshot.cs (3)
      1 The main migrations file that contains the operations necessary to apply the migration (in Up) and to revert it (in Down).
      2 The migrations metadata file that contains information used by EF.
      3 A snapshot of the current model used to determine what changed when adding the next migration.

    • It’s a good idea to inspect what exactly EF Core generated - and possibly amend it.

  • dotnet ef migrations list

    $ dotnet ef migrations list
Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
20250213080400_InitialCreate (Pending)

  • dotnet ef migrations remove

    Avoid removing any migrations which have already been applied to production databases. 
    $ dotnet ef migrations remove
Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*) FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Removing migration '20250213080400_InitialCreate'.
Removing model snapshot.
Done.
4.1.2. Applying Migrations

  • The recommended way to deploy migrations to a production database is by generating SQL scripts.

    • SQL scripts can be reviewed for accuracy; this is important since applying schema changes to production databases is a potentially dangerous operation that could involve data loss.

    • In some cases, the scripts can be tuned to fit the specific needs of a production database.

    • SQL scripts can be used in conjunction with a deployment technology, and can even be generated as part of a CI process.

    • SQL scripts can be provided to a DBA, and can be managed and archived separately.

      # generate a SQL script from a blank database to the latest migration
dotnet ef migrations script
# generate a SQL script from the given migration to the latest migration.
dotnet ef migrations script AddNewTables
# generate a SQL script from the specified from migration to the specified to migration
dotnet ef migrations script AddNewTables AddAuditTable
# generate idempotent migrations
dotnet ef migrations script --idempotent

  • The EF command-line tools can be productively used to apply migrations during local development and testing, but are not ideal for managing production databases.

    • The SQL commands are applied directly by the tool, without giving the developer a chance to inspect or modify them, which can be dangerous in a production environment.

    • The .NET SDK and the EF tool must be installed on production servers and requires the project’s source code.

      # update database to the latest migration
dotnet ef database update
# update or roll back database to a given migration
dotnet ef database update AddNewTables

  • Migration bundles are single-file executables that can be used to apply migrations to a databaseto ddress some of the shortcomings of the SQL script and command-line tools.

    • Executing SQL scripts requires additional tools.

    • The transaction handling and continue-on-error behavior of these tools are inconsistent and sometimes unexpected, which can leave the database in an undefined state if a failure occurs when applying migrations.

    • Bundles can be generated as part of a CI process and easily executed later as part of the deployment process.

    • Bundles are executable without the .NET SDK, EF Tool, or even the .NET Runtime (if self-contained), and they don’t require source code.

      # generate a bundle
dotnet ef migrations bundle
# generate a self-contained bundle for Linux
dotnet ef migrations bundle --self-contained -r linux-x64

    • The resulting executable is named efbundle by default, which can be used to update the database to the latest migration.

      $ ./efbundle --help
Entity Framework Core Migrations Bundle 9.0.2

Usage: efbundle [arguments] [options] [[--] <arg>...]]

Arguments:
  <MIGRATION>  The target migration. If '0', all migrations will be reverted. Defaults to the last migration.

Options:
  --connection <CONNECTION>  The connection string to the database. Defaults to the one specified in AddDbContext or OnConfiguring.
  --version                  Show version information
  -h|--help                  Show help information
  -v|--verbose               Show verbose output.
  --no-color                 Don't colorize output.
  --prefix-output            Prefix output with level.

  • The migrations can be programmatically applied by calling context.Database.MigrateAsync() for local development and testing.

    var builder = Host.CreateApplicationBuilder(args);
// . . .
var app = builder.Build();
using (var scope = app.Services.CreateScope())
{
    using var context = scope.ServiceProvider.GetRequiredService<BloggingContext>();
    await context.Database.MigrateAsync();
}

5. Querying

Entity Framework Core uses Language-Integrated Query (LINQ) to query data from the database.

  • EF Core passes a representation of the LINQ query to the database provider.

  • Database providers in turn translate it to database-specific query language (for example, SQL for a relational database).

  • Queries are always executed against the database even if the entities returned in the result already exist in the context.

    // SELECT [b].[Id], [b].[Name], [b].[Url]
// FROM [Blogs] AS [b]
var blogs = await context.Blogs.ToListAsync();

// SELECT TOP(1) [b].[Id], [b].[Name], [b].[Url]
// FROM [Blogs] AS [b]
// WHERE [b].[Id] = 1
var blog = await context.Blogs.SingleOrDefaultAsync(b => b.Id == 1);

// SELECT [b].[Id], [b].[Name], [b].[Url]
// FROM [Blogs] AS [b]
// WHERE [b].[Url] IS NOT NULL AND [b].[Url] LIKE N'%dotnet%'
var filteredBlogs = await context.Blogs
    .Where(b => b.Url != null && b.Url.Contains("dotnet")).ToListAsync();

Tracking behavior controls if Entity Framework Core keeps information about an entity instance in its change tracker.

  • If an entity is tracked, any changes detected in the entity are persisted to the database during SaveChanges.

  • EF Core also fixes up navigation properties between the entities in a tracking query result and the entities that are in the change tracker.

  • By default, queries that return entity types are tracking.

    • If EF Core finds an existing entity, then the same instance is returned, which can potentially use less memory and be faster than a no-tracking query.

    • EF Core doesn’t overwrite current and original values of the entity’s properties in the entry with the database values.

    • If the entity isn’t found in the context, EF Core creates a new entity instance and attaches it to the context.

    • Query results don’t contain any entity which is added to the context but not yet saved to the database.

      var blog = await context.Blogs.SingleOrDefaultAsync(b => b.BlogId == 1);
blog.Rating = 5;
await context.SaveChangesAsync();

5.1. Tracking vs. no-tracking

  • No-tracking queries are useful when the results are used in a read-only scenario, which are generally quicker to execute because there’s no need to set up the change tracking information.

    • If the entities retrieved from the database don’t need to be updated, then a no-tracking query should be used.

    • An individual query can be set to be no-tracking.

      var blogs = await context.Blogs
    .AsNoTracking()
    .ToListAsync();

    • A no-tracking query also give results based on what’s in the database disregarding any local changes or added entities.

    • The default tracking behavior can be changed at the context instance level.

      context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var blogs = await context.Blogs.ToListAsync();

    • To make all the queries no-tracking by default.

      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying.Tracking")
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}
      // You can still add AsTracking to make specific queries tracking.
var blogs = await context.Blogs.AsNoTracking().ToListAsync();

5.2. Pagination

Keyset pagination is appropriate for pagination interfaces where the user navigates forwards and backwards, but does not support random access, where the user can jump to any specific page.

  • A common way to implement pagination with databases is to use the Skip and Take LINQ operators (OFFSET and LIMIT in SQL).

    var posts = await context.Blogs
    .OrderBy(b => b.Id)
    .Skip(20) // page index
    .Take(10) // page size
    .ToListAsync();
    info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (14ms) [Parameters=[@__p_0='20', @__p_1='10'], CommandType='Text', CommandTimeout='30']
      SELECT [b].[Id], [b].[Name], [b].[Url]
      FROM [Blogs] AS [b]
      ORDER BY [b].[Id]
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

  • The recommended alternative to offset-based pagination - sometimes called keyset pagination or seek-based pagination - is to simply use a WHERE clause to skip rows, instead of an offset.

    int lastId = 55;
var posts = await context.Blogs
    .OrderBy(b => b.Id)
    .Where(b => b.Id > lastId)
    .Take(10)
    .ToListAsync();
    info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (136ms) [Parameters=[@__p_1='10', @__lastId_0='55'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(@__p_1) [b].[Id], [b].[Name], [b].[Url]
      FROM [Blogs] AS [b]
      WHERE [b].[Id] > @__lastId_0
      ORDER BY [b].[Id]

5.3. Indexes

  • A good way to spot indexing issues is to first pinpoint a slow query, and then examine its query plan via a database’s favorite tool.

    • While indexes speed up queries, they also slow down updates since they need to be kept up-to-date.

    • Avoid defining indexes which aren’t needed, and consider using index filters to limit the index to a subset of the rows.

    • Composite indexes can speed up queries which filter on multiple columns, but they can also speed up queries which don’t filter on all the index’s columns - depending on ordering.

      • For example, an index on columns A and B speeds up queries filtering by A and B as well as queries filtering only by A, but it does not speed up queries only filtering over B.

    • If a query filters by an expression over a column (e.g. price / 2), a simple index cannot be used.

      • However, a stored persisted column can be defined for the expression, and create an index over that.

      • Some databases also support expression indexes, which can be directly used to speed up queries filtering by any expression.

5.4. Projections

  • For read-only queries with multiple columns, project into anonymous types, but be aware that updates become more complex since EF Core change tracking relies on entities, though partial updates are possible with advanced techniques.

    // SELECT TOP(2) [p].[FirstName], [p].[LastName]
// FROM [People] AS [p]
// WHERE [p].[FirstName] = N'John'
var p = await context.People
    .Select(p => new { p.FirstName, p.LastName }) // projection
    .SingleAsync(b => b.FirstName == "John");

5.5. SQL queries

Entity Framework Core allows dropping down to SQL queries when working with a relational database, which are useful if the query can’t be expressed using LINQ, or if a LINQ query causes EF to generate inefficient SQL.

  • Basic SQL queries

    // a LINQ query based on a SQL query
var blogs = await context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToListAsync();
    // execute a stored procedure
var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToListAsync();
    //  string interpolation
var user = "johndoe";

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();
    // named parameters
var user = new SqlParameter("user", "johndoe");

var blogs = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToListAsync();
    // executing non-querying SQL
var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
    // executing non-querying SQL
var tranMode = context.Database.SqlQueryRaw<string>(
    "SELECT IIF(@@OPTIONS&2 = 0, 'Implicit Transaction Off', 'Implicit Transaction On') AS TranMode")
    .ToList()
    .FirstOrDefault();
    // use the underlying ADO.NET connection directly
using var connection = context.Database.GetDbConnection();
connection.Open();
using var command = connection.CreateCommand();
command.CommandText =
    "SELECT IIF(@@OPTIONS&2 = 0, 'Implicit Transaction Off', 'Implicit Transaction On') AS TranMode";
var tranMode = command.ExecuteScalar() as string;

6. Saving

Entity Framework Core (EF Core) supports two approaches for persisting data to the database: change tracking with SaveChanges() and direct execution with ExecuteUpdate and ExecuteDelete.

6.1. SaveChanges, ExecuteUpdate and ExecuteDelete

  • EF Core tracks changes maked to data (adding, updating, or deleting) and SaveChanges() turns those changes into the SQL commands needed to update the database.

    • It is the more common and often preferred method for saving data in EF Core.

    • For large-scale bulk updates, SaveChanges() can be less efficient than direct execution methods.

      // Add a new entity
var newProduct = new Product { Name = "New Widget", Price = 9.99 };
context.Products.Add(newProduct);

// Modify an existing entity
var existingProduct = context.Products.Find(1);
if (existingProduct != null)
{
    existingProduct.Price = 12.99;
}

// Remove an entity
var productToRemove = context.Products.Find(2);
if (productToRemove != null)
{
    context.Products.Remove(productToRemove);
}

// Save all changes
context.SaveChanges();

  • The ExecuteUpdate and ExecuteDelete offer a direct way, bypassing change tracker, to update or delete data using LINQ queries, which is often more efficient for bulk operations or when needing precise control over the SQL.

    // Update multiple products
context.Products
    .Where(p => p.Category == "Electronics")
    .ExecuteUpdate(setters => setters.SetProperty(p => p.Price, 19.99));

// Delete multiple products
context.Products
    .Where(p => p.Discontinued)
    .ExecuteDelete();

6.2. Transactions

  • By default, if the database provider supports transactions, all changes in a single call to SaveChanges are applied in a transaction.

  • While all relational database providers support transactions, other providers types may throw or no-op when transaction APIs are called.

  • The DbContext.Database API can be used to begin, commit, and rollback transactions.

    using var context = new BloggingContext();
using var transaction = await context.Database.BeginTransactionAsync();

try
{
    context.Blogs.Add(new Blog { Url = "https://devblogs.microsoft.com/dotnet/" });
    await context.SaveChangesAsync();

    await transaction.CreateSavepointAsync("BeforeMoreBlogs");

    context.Blogs.Add(new Blog { Url = "https://devblogs.microsoft.com/visualstudio/" });
    context.Blogs.Add(new Blog { Url = "https://devblogs.microsoft.com/aspnet/" });
    await context.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch (Exception)
{
    // If a failure occurred, we rollback to the savepoint and can continue the transaction
    await transaction.RollbackToSavepointAsync("BeforeMoreBlogs");

    // TODO: Handle failure, possibly retry inserting blogs
}

6.3. Optimistic concurrency

EF Core implements optimistic concurrency, which assumes that concurrency conflicts are relatively rare.

  • In contrast to pessimistic approaches - which lock data up-front and only then proceed to modify it - optimistic concurrency takes no locks, but arranges for the data modification to fail on save if the data has changed since it was queried.

  • Optimistic concurrency failure is reported to the application, which deals with it accordingly, possibly by retrying the entire operation on the new data.

  • In EF Core, optimistic concurrency is implemented by configuring a property as a concurrency token.

    • The concurrency token is loaded and tracked when an entity is queried - just like any other property.

    • Then, when an update or delete operation is performed during SaveChanges(), the value of the concurrency token on the database is compared against the original value read by EF Core.

    • The concurrency token can be managed natively by some databases.

      • The rowversion type shown below is a SQL Server-specific feature; the details on setting up an automatically-updating concurrency token differ across databases, and some databases don’t support these at all (e.g. SQLite).

        // Data Annotations
public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Timestamp]
    public byte[] Version { get; set; }
}
        // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .Property(p => p.Version)
        .IsRowVersion();
}
        // UPDATE [People] SET [FirstName] = @p0
// WHERE [PersonId] = @p1 AND [Version] = @p2;
using var context = scope.ServiceProvider.GetRequiredService<BloggingContext>();
var person = await context.People.SingleAsync(b => string.Equals(b.FirstName, "John"));
person.FirstName = "Paul";
await context.SaveChangesAsync();

    • The concurrency token can also be managed in application code, rather than have the database manage it.

      // Data Annotations
public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }

    [ConcurrencyCheck]
    public Guid Version { get; set; }
}
      // Fluent API
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .Property(p => p.Version)
        .IsConcurrencyToken();
}
      // UPDATE [People] SET [FirstName] = @p0, [Version] = @p1
// WHERE [PersonId] = @p2 AND [Version] = @p3;
var person = await context.People.SingleAsync(b => b.FirstName == "John");
person.FirstName = "Paul";
person.Version = Guid.NewGuid(); // assign it in application whenever persisting changes
await context.SaveChangesAsync();

7. Logging, events, and diagnostics

Entity Framework Core (EF Core) contains several mechanisms for generating logs, responding to events, and obtaining diagnostics.

Entity Framework Core (EF Core) fully integrates with Microsoft.Extensions.Logging. 
// simple logging
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.LogTo(Console.WriteLine);                     // logging to console
    // .LogTo(message => Debug.WriteLine(message));                 // logging to debug window
    // .LogTo(new StreamWriter("log.txt", append: true).WriteLine); // logging to file
    // .EnableSensitiveDataLogging(); // logging data values
    // .EnableDetailedErrors();       // logging more detailed errors.

8. Performance diagnosis

Database performance is a vast and complex topic, spanning an entire stack of components: the database, networking, the database driver, and data access layers such as EF Core, which can be broken down into the following broad categories:

Premature optimization is the root of all evil - Donald Knuth.

  • Pure database performance.

    • With relational database, EF translates the application’s LINQ queries into the SQL statements getting executed by the database; these SQL statements themselves can run more or less efficiently.

    • The right index in the right place can make a world of difference in SQL performance, or rewriting the LINQ query may make EF generate a better SQL query.

  • Network data transfer and roundtrips.

    • As with any networking system, it’s important to limit the amount of data going back and forth on the wire.

    • Beyond the amount of data going back and forth, the network roundtrips, since the time taken for a query to execute in the database can be dwarfed by the time packets travel back and forth between the application and the database.

  • EF runtime overhead.

    • Finally, EF itself adds some runtime overhead to database operations:

      • compile the queries from LINQ to SQL (although that should normally be done only once)

      • change tracking adds some overhead (but can be disabled), etc.

  • Cache.

    • To maximize performance, especially for scalable apps, minimize database access through caching, as databases are harder to scale than application servers.

8.1. Identifying slow database commands via logging

  • If a certain query is taking too much time (e.g. because an index is missing), this can be seen discovered by inspecting command execution logs and observing how long they actually take.

    builder.Logging.AddFilter("Microsoft.EntityFrameworkCore.Database.Command", LogLevel.Information);
    info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (99ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] (1)
      SELECT TOP(2) [p].[PersonId], [p].[FirstName], [p].[LastName], [p].[Version]
      FROM [People] AS [p]
      WHERE [p].[FirstName] = N'John'
    1 The above command took 99 milliseconds.

  • It’s recommended to only keep logging on for a short interval of time to gather data - while carefully monitoring an application - or to capture logging data on a pre-production system.

  • Databases typically come with their own tracing and performance analysis tools, which usually provide much richer, database-specific information beyond simple execution times; the actual setup, capabilities and usage vary considerably across databases.

8.2. Correlating database commands to LINQ queries

  • One problem with command execution logging is that it’s sometimes difficult to correlate SQL queries and LINQ queries: the SQL commands executed by EF can look very different from the LINQ queries from which they were generated.

    var person = await context.People
    .TagWith("This is my spatial query!") (1)
    .SingleAsync(b => b.FirstName == "John");
    info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (111ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      -- This is my spatial query! (1)

      SELECT TOP(2) [p].[PersonId], [p].[FirstName], [p].[LastName], [p].[Version]
      FROM [People] AS [p]
      WHERE [p].[FirstName] = N'John'
    1 It’s often worth tagging the major queries of an application to make the command execution logs more immediately readable.

8.3. Inspecting query execution plans

  • To optimize slow queries, analyze their execution plans—database-generated blueprints showing the steps and costs involved in query execution.

  • Databases sometimes generate different query plans depending on actual data in the database.

    • For example, if a table contains only a few rows, a database may choose not to use an index on that table, but to perform a full table scan instead.

    • If analyzing query plans on a test database, always make sure it contains data that is similar to the production system.

9. Dapper

Dapper is an open-source micro object-relational mapping (ORM) library for .NET applications, which uses the ADO.NET data providers to work with any existing database technology that supports ADO.NET. [6]

dotnet add package Dapper

Dapper supports a variety of database providers, including the popular Microsoft SQL Server, Oracle, MySQL, MariaDB, PostgreSQL, SQLite, etc.

dotnet add package Microsoft.Data.SqlClient # SQL Server
dotnet add package System.Data.SQLite.Core  # SQLite
IDbConnection connection = ...

// 1. Scalar Query (Single Value):
var blogCount = await connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM Blogs");

// 2. Row Query (Single Row):
var firstBlog = await connection.QuerySingleOrDefaultAsync<Blog>("SELECT * FROM Blogs ORDER BY Id ASC");

// 3. Multi-Result Query (Multiple Rows):
var allBlogs = await connection.QueryAsync<Blog>("SELECT * FROM Blogs");

// 4. Projection (Selecting Specific Columns):
var blogNamesAndUrls = await connection.QueryAsync(
    "SELECT Name, Url FROM Blogs",
    (string name, string url) => new { Name = name, Url = url }); // Mapping to anonymous type

//Or use anonymous type directly
var blogNamesAndUrls2 = await connection.QueryAsync(
    "SELECT Name, Url FROM Blogs",
    (dynamic item) => new { Name = item.Name, Url = item.Url }); // Mapping to anonymous type

// 5. Parameterized Query (Preventing SQL Injection):
var blogsStartingWithB = await connection.QueryAsync<Blog>(
    "SELECT * FROM Blogs WHERE Name LIKE @NameStart", new { NameStart = "B%" }); // named parameter

// 6. Dynamic Parameter Query:
var parameters = new DynamicParameters();

string sql = "SELECT * FROM Blogs WHERE 1=1 "; // Start with a true condition

if (!string.IsNullOrEmpty("Blog")) // Example condition
{
    sql += "AND Name LIKE @NamePart ";
    parameters.Add("@NamePart", "%" + "Blog" + "%");
}

var dynamicBlogs = await connection.QueryAsync<Blog>(sql, parameters);

// Using IN clause with Dynamic Parameters
var blogIds = new List<int> { 1, 2, 5 };

// Transform the list of IDs into a format suitable for the IN clause
var inClauseParams = new DynamicParameters();

for (var i = 0; i < blogIds.Count; i++)
{
    inClauseParams.Add($"@Id{i}", blogIds[i]);
}

var inClauseSql = $"SELECT * FROM Blogs WHERE Id IN ({string.Join(",", inClauseParams.ParameterNames)})";

var blogsInList = await connection.QueryAsync<Blog>(inClauseSql, inClauseParams);

// 7. Insert, Update, Delete (using parameters):
var newBlogId = await connection.ExecuteScalarAsync<int>(
    "INSERT INTO Blogs (Name, Url) VALUES (@Name, @Url); SELECT SCOPE_IDENTITY();",
    new { Name = "Another Blog", Url = "http://another.com" });

var updatedRows = await connection.ExecuteAsync(
    "UPDATE Blogs SET Url = @NewUrl WHERE Id = @Id",
    new { Id = newBlogId, NewUrl = "http://updated.com" });

var deletedRows = await connection.ExecuteAsync("DELETE FROM Blogs WHERE Id = @Id", new { Id = newBlogId });

References