A Guide to Entity Framework (EF) Core 5

Entity Framework (EF) is an object relational mapper for .NET that abstracts database interactions that includes the ability to translate LINQ expressions into SQL and bi-directional tooling to keep the application data model in sync with the actual state in the database. This can make data access code clear and streamline development. EF Core 5 has introduced several new features including table-per-type inheritance and full many-to-many relationship support.

A Guide to EF Core 5 | Overview

Index

In this guide, we will cover the basics of using Entity Framework in a .NET 5 application as well as some of the new features introduced in EF Core 5. The approach used will be geared toward the practical application of EF features that are compatible with production workflows. This will include working with SQL migration scripts and separating functionality in separate projects.

The key to getting the most out of EF is successfully keeping the model known to the application in sync with the state in the persistent database. The EF tools can help generate entity classes and SQL but a good understanding of the SQL queries and table structures behind is still required.

Audience

Developers familiar with database access via ADO.NET, Dapper, or other methods outside of a full object relational mapper (ORM) should be able to relate to the workflow involved with Entity Framework (EF). A direct comparison these methods would be misleading as EF is a set of tools built on top of basic access methods. Those looking to choose between one method another may be able to see the benefits of hybrid approaches in their applications if needed.

Why Use Entity Framework?

There are two good reasons to use Entity Framework!

Make data access code concise using strongly-typed C# and support comparison of model versions to create migrations.

Keep the model known to the application in sync with the state in the persistent database. EF tools can automate many changes to database to assist with schema changes during development.

Comparison of Database Access Techniques

Accessing a database with ADO .NET alone requires explicit specification of how database fields should be mapped to C# classes.

using (SqlConnection conn = new SqlConnection(
               connectionString))
{
    SqlCommand cmd = new SqlCommand(queryString, conn);
    command.Connection.Open();
    command.CommandText = @"SELECT SalesOrder.* FROM SalesOrder WHERE ProductId = @ProductId;";
    command.Parameters.Add("@ProductId", productId);
    var reader = command.ExecuteReader();
    while (reader.Read()) {
        int salesOrderId = (int) reader["SalesOrderId"];
        // .. and so on for all fields ...        
    }
}

With the above method, the developer will be responsible for mapping the columns in the database to your C# classes. This creates code that can become redundant and verbose.

One way to simplify would be to eliminate the mapping part of this process with Dapper which will map the columns in the result rows to the fields in your classes. This is demonstrated in the code below.

string sql =  @"SELECT SalesOrder.* FROM SalesOrder WHERE ProductId = @ProductId;";

using (var conn = new SqlConnection(connectionString))
    orders = await conn.QueryAsync<SalesOrder>(sql, new { ProductId = productId } )).ToList();

This method is intended to make just the mapping piece of reading from the database cleaner. When queries become more complicated, packages such as SqlKata can make dynamically building complex queries easier.

var salesOrders = db.Query("SalesOrder")
    .Select("SalesOrder.*")            
    .Where("ProductId", productId)        
    .Get<SalesOrder>();

When accessing the database with EF, all entities and field names are strongly-typed and relationships can be used via navigation properties.

var salesOrders = await storeContext
    .SalesOrders
    .Where(it => it.ProductId == ProductId)        
    .ToListAsync();

Concepts in Entity Framework

The components of Entity Framework include:

The entities that are used in a DbSet are mapped to a table. These are simple C# objects that can also have properties defined with data annotations.

The application database model created by the developer in the DbContext contains definitions which are not inferred by the migration tools. This can include relationship types, inheritance, and any changes that are made to naming schemes from the database to the application.

The explicit application model definition in the ModelSnapshot created with migrations. This includes the datatype for every field for the database.

Migrations include changes to the database schema from an initial state. This initial state can be redefined later in the development of the application. Migrations scripts can be generated to recreate the whole database or only the differences from one version to another. The purpose of keeping migrations is to enable the creation of SQL scripts to change other instances of the database outside of a development machine.

Index

A Guide to EF Core 5 | Solution Setup

Index

The example solution will aim toward a 3-tier architecture and consist of a project for a web API, business logic, data access, and domain objects.

There are two ways to get started with EF.

1) One option to get started with EF is to create C# classes and then allow the dotnet ef commands to create tables in the database for you via an initial migration.

2) The other way of getting started is to create your database tables and use the dotnet ef dbcontext scaffold command to create C# classes for the tables in your database.

When learning about EF it is helpful to scaffold first to see how the model is defined in the generated OnModelCreating method. After the model has been created it is generally more efficient to work from code-first migrations.

If you prefer to work from an existing database, skip to the scaffolding section below. If not, see the DbContext example and migration setup section.

Solution Structure

For either method of getting started, an example solution based around a ficticious store may contain projects such as:

StoreAPI – WebAPI | References: StoreService, StoreDomain

StoreService – Class Library | References: StoreDataAccess, StoreDomain

StoreDataAccess – Class Library | Contains references to EF packages and where the DbContext is located.

StoreDomain – Class Library | Domain objects shared across all projects in the solution.

Scaffolding

An existing database can be scaffolded. After scaffolding, set up an initial migration for the application. This way any changes to the model you make can be updated and limited to only the fields that were changed in the last revision. These migrations can be rolled up into a new initial create migration after development.

Dependencies

The data access project of your solution should reference the following packages for working with SQL Server

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.SqlServer

Microsoft.EntityFrameworkCore.Tools

CLI Parameters

In a terminal in that project directory, use the following command:

dotnet ef dbcontext scaffold "[yourConnectionStringHere]" Microsoft.EntityFrameworkCore.SqlServer --context StoreDbContext --context-dir Scaffolded --output-dir ..StoreDomainEntities"

--no-onconfiguring is a useful option when the connection string is injected to the DbContext via the DbContextOptions. Otherwise the OnConfiguring method will contain the hard-coded connection string used by the scaffolding tool.

Since the database context and domain object classes may not always exists within the same project, it can be useful to specify different locations to output these:

--output-dir – The path of the generated Entity classes.

--context – The name of the generated DbContext.

--context-dir – The path to place the generated DbContext class.

To allow overwriting existing classes on any subsequent times the scaffolding is run, use the --force option.

After running the CLI tools, there should be classes populated in the StoreDomain/Entities project as well as a StoreDbContext class in the StoreDataAccessEF project.

When changes to your database occur, recreating the entity classes from the database is supported. Of course, before running this tool, have a clean state of your application committed to version control so that you’ll be able to undo and re-run if you discover you need to change parameters in the CLI tool. The solution will need to be in a buildable state in order for scaffolding to run!

Code-First Migrations

Migrations can be generated and kept as C# or converted to SQL. The SQL option allows customization and makes the migration portable to other database instances.

Model Definition

Model properties can either be defined in data annotations directly on the fields in C# classes or specified in the OnModelCreating method in the DbContext. Calling methods on the ModelBuilder object in the parameter of OnModelCreating will provide the most features and specific definitions.

The DbContext for an application establishes entities and relationships to abstract the interaction with the database for CRUD operations. Typically there is a single context per database. The constructor for a database context requires configuration options that include the database provider option as well as connection information. OnModelCreating is where the model is defined via Fluent API method calls.

public class StoreContext : DbContext
{
    public DbSet<MarginResult> MarginResults { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<ProductCategory> ProductCategories { get; set; }
    public DbSet<ProductSubcategory> ProductSubcategories { get; set; }
    public DbSet<SalesOrder> SalesOrders { get; set; }
    public DbSet<SalesOrderMargin> SalesOrderMargins { get; set; }        
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Developer> Developers { get; set; }        
    public DbSet<SalesPerson> SalesPeople { get; set; }
    public DbSet<Shipment> Shipments { get; set; }
    public DbSet<Skill> Skills { get; set; }
    public DbSet<Receipt> Receipts { get; set; }    
    
    public StoreContext(DbContextOptions<StoreContext> options)
    : base(options)
    {
    }   

    protected override void OnModelCreating(ModelBuilder modelBuilder) { 

         modelBuilder.Entity<Product>(entity =>
            {
                entity.ToTable("Product");

                entity.Property(e => e.ProductId).HasColumnName("ProductID");                    

                entity.HasOne(d => d.ProductSubcategory)
                    .WithMany(p => p.Products)
                    .HasForeignKey(d => d.ProductSubcategoryId)
                    .OnDelete(DeleteBehavior.Cascade)
                    .HasConstraintName("FK_Product_ProductSubCategory");
            });

        // and so on for other entities . . . 
    }
}

Naming of Tables vs Entities

It is possible to have an entity map to a table name different than the name of the class. For example if you would like to create the Developer DbSet as:

public DbSet<Developer> Developers { get; set; }

The difference in the plural name and the singular table name can be resolved like so:

modelBuilder.Entity<Developer>(entity =>
    {
        entity.ToTable("Developer")                
    });

In EF6, there is an option to disable pluralized table names by default. This option hasn’t yet made its way into EF Core.

Design Time DbContext Factory

In many guides about EF, the DbContext will located within the same project as the application or API. It is generally a better idea to create a data access project in order to separate concerns and increase modularity. This makes working with the configuration of the solution a little more interesting. The IConfiguration used is determined by the startup project. In many cases this is the application layer. This is fine for injecting variables to the DAL at runtime. But the EF migration tools require a configuration to provide for the DbContext separate from the main application. Scaffolding from the database is already enabled but in order to create changes to the code and have the database updated, we’ll need to do one more step.

public class StoreContextFactory : IDesignTimeDbContextFactory<StoreContext>
  {   
      public StoreContext CreateDbContext(string[] args)
      {
          var configuration = new ConfigurationBuilder()
              .SetBasePath(Directory.GetCurrentDirectory())
              .AddJsonFile("appsettings.json") // you will need an appsettings.json in the DAL project directory
              .Build();
          var dbContextBuilder = new DbContextOptionsBuilder<StoreContext>();
          var connectionString = configuration
                      .GetConnectionString("storedb"); // defined in appsettings.json as ConnectionStrings.storedb
          dbContextBuilder.UseSqlServer(connectionString);
          return new StoreContext(dbContextBuilder.Options);
      }
  }

Note that the IDesignTimeDbContextFactory is only required for the EF CLI migration tools.

Creating and Running Migrations

In this part we are working with a code model that is already in sync with the database tables. We’ll add a new column in a table to see the process for updating the database based on code changes. The migrations can either be run via the dotnet ef database update command or by dotnet ef migrations script which creates a SQL script directly.

To create an initial migration from a blank or already populated database:

dotnet ef migrations add InitialCreate

Create the SQL script for the initial migration. Running the script commands without specifying a migration name will result in the SQL to recreate the entire data model.

dotnet ef migrations script -o initial-migration.sql

Make sure the resulting SQL matches the actual state or your database. The output.sql file specified can is only to make accessing the script easier and does not need to be added to project source control. By default the SQL will be dumped to the terminal.

Be careful! The initial migration script will need modification if working with a database that has already been created as it contains SQL to create those tables without checking if they already exist.

public abstract class Shipment
{    
    public int ShipmentId { get; set; }
    public int EstimatedDays { get; set; }        
    public int NewShipmentField { get; set; } // New field from the InitialCreate state
}

After making this modification, a migration can be created that will update the database.

dotnet ef migrations add AddedShippingField

And then create the SQL script for this migration:

dotnet ef migrations script InitialCreate AddedShippingField -o migration.sql

The resulting SQL file will contain the SQL to sync the database with the model for the application:

ALTER TABLE [Shipment] ADD [NewShipmentField] int NOT NULL DEFAULT 0;

Also included in the migration SQL is an INSERT to the migration history table to mark this migration as applied.

How does EF know what to change?

The __EFMigrationHistory table will contain a record indicating that the named migration has already been applied. The model snapshots created under the Migrations folder contain a definition of each version of the model in the migrations that have been created.

Migration Management

The decision to use the EF migration tools is optional. Keeping the model defined in the application in sync with the state of the database is what is important. This table is not required in a production instance so long as you have the SQL to make any changes past the version that was last deployed.

When using migrations, previously generated ones will collect in the application. As you can imagine, over time this can get out of hand. This is why another important decision for your team is to define when a new initial state should be established. When there is a known initial state for the database, only the migrations after that point need to be kept for usage later. A practical use may be to have an initial state that matches the current production database schema.

Working with migrations in production should be limited to SQL scripts that are reviewed and edited to preserve data. In some cases, only the differences in the schema should be provided and a DBA will handle the migration of data if required.

A Guide to EF Core 5 | Database Interaction

Index

Basic CRUD operations with EF

About Intertech

Founded in 1991, Intertech delivers software development consulting to Fortune 500, Government, and Leading Technology institutions, along with real-world based corporate education services. Whether you are a company looking to partner with a team of technology leaders who provide solutions, mentor staff and add true business value, or a developer interested in working for a company that invests in its employees, we’d like to meet you. Learn more about us.

Create

await storeContext
    .SalesOrders
    .AddAsync(order);
bool success = await storeContext.SaveChangesAsync() == 1;

SaveChangesAsync returns a int indicating the number of items updated in the transaction. As a side note, transactions are implicit within the lifetime of a DbContext.

Generated SQL:

INSERT INTO [SalesOrder] ([DiscountAmount], [OrderDate], [OrderQuantity], [ProductID], [UnitCost], [UnitPrice])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
SELECT [SalesOrderID]
FROM [SalesOrder]
WHERE @@ROWCOUNT = 1 AND [SalesOrderID] = scope_identity();
',N'@p0 decimal(18,2),@p1 datetime2(7),@p2 int,@p3 int,@p4 decimal(18,2),@p5 decimal(18,2)',@p0=0,@p1='2021-09-2304:12:01.4301996',@p2=2,@p3=956,@p4=91.05,@p5=198.00

Notice how there is a Select after the insert. This is used by the tracking features of EF to update the order object with the SalesOrderID value set by the database. As a side effect of tracking, the order object identity column value is assigned after the new record is inserted to the database.

Creating Nested/Inherited Entities

When working with inherited objects, a derived type can be created and EF will handle the creation of the base record as in the following example:

storeContext
    .Developers
    .Add(new Developer
    {
        EmployeeTypeId = (int)EmployeEmployeeType.Developer,
        FirstName = "John",
        LastName = "Smith",
        LinesOfCodeWritten = 32000
    });

storeContext.SaveChanges();

Before running this query, there was no corresponding EmployeeId in the Employee table, but after calling Add with the new developer object, the ID is set for both the base and derived entity.

Read

Single

await storeContext
    .SalesOrders            
    .Where(it => it.SalesOrderID == orderId)
    .FirstOrDefaultAsync();

Resulting SQL:

SELECT TOP(1) [s].[SalesOrderID], [s].[DiscountAmount], [s].[OrderDate], [s].[OrderQuantity], [s].[ProductID], [s].[UnitCost], [s].[UnitPrice]
FROM [SalesOrder] AS [s]
WHERE [s].[SalesOrderID] = @__orderId_0',N'@__orderId_0 int

Single with Navigation

The use of the .Include and nested .ThenInclude methods will join on the Product, ProductSubcategory, and ProductCategory tables.

await storeContext
    .SalesOrders
    .Include(a => a.Product)
    .ThenInclude(b => b.ProductSubcategory)
    .ThenInclude(c => c.ProductCategory)
    .Where(it => it.SalesOrderID == orderId)
    .FirstOrDefaultAsync();

Generated SQL:

SELECT TOP(1) [p].[ProductID], [p].[ProductDescription], [p].[ProductLabel], [p].[ProductName], [p][ProductSubcategoryID], [p].[UnitCost], [p].[UnitPrice], [p0].[ProductSubcategoryID], [p0][ProductCategoryID], [p0].[ProductSubcategoryDescription], [p0].[ProductSubcategoryName], [p1][ProductCategoryID], [p1].[ProductCategoryDescription], [p1].[ProductCategoryName]
FROM [Product] AS [p]
INNER JOIN [ProductSubcategory] AS [p0] ON [p].[ProductSubcategoryID] = [p0].[ProductSubcategoryID]
INNER JOIN [ProductCategory] AS [p1] ON [p0].[ProductCategoryID] = [p1].[ProductCategoryID]
WHERE [p].[ProductID] = @__productId_0',N'@__productId_0 int

Many with Navigation

await storeContext
    .Products
    .Include(a => a.ProductSubcategory)
    .ThenInclude(b => b.ProductCategory)
    .ToListAsync();

Generated SQL:

SELECT [p].[ProductID], [p].[ProductDescription], [p].[ProductLabel], [p].[ProductName], [p].[ProductSubcategoryID], [p].[UnitCost], [p].[UnitPrice], [p0].[ProductSubcategoryID], [p0].[ProductCategoryID], [p0].[ProductSubcategoryDescription], [p0].[ProductSubcategoryName], [p1].[ProductCategoryID], [p1].[ProductCategoryDescription], [p1].[ProductCategoryName]
FROM [Product] AS [p]
INNER JOIN [ProductSubcategory] AS [p0] ON [p].[ProductSubcategoryID] = [p0].[ProductSubcategoryID]
INNER JOIN [ProductCategory] AS [p1] ON [p0].[ProductCategoryID] = [p1].[ProductCategoryID]

Pagination

int page = 5; 
int pageSize = 10;

await storeContext
    .Products
    .Include(a => a.ProductSubcategory)
    .ThenInclude(b => b.ProductCategory)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

Generated SQL:

SELECT [t].[ProductID], [t].[ProductDescription], [t].[ProductLabel], [t].[ProductName], [t[ProductSubcategoryID], [t].[UnitCost], [t].[UnitPrice], [p0].[ProductSubcategoryID], [p0[ProductCategoryID], [p0].[ProductSubcategoryDescription], [p0].[ProductSubcategoryName], [p1[ProductCategoryID], [p1].[ProductCategoryDescription], [p1].[ProductCategoryName]
FROM (
SELECT [p].[ProductID], [p].[ProductDescription], [p].[ProductLabel], [p].[ProductName], [p[ProductSubcategoryID], [p].[UnitCost], [p].[UnitPrice]
FROM [Product] AS [p]
ORDER BY (SELECT 1)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY ) AS [t]INNER JOIN [ProductSubcategory] AS [p0] ON [t].[ProductSubcategoryID] = [p0].[ProductSubcategoryID]INNER JOIN [ProductCategory] AS [p1] ON [p0].[ProductCategoryID] = [p1].[ProductCategoryID]

Update

Update a record by first retrieving it and making changes. The call to Update will only start tracking changes to the entity provided. These changes are not applied to the database until SaveChanges is called on the context.

var salesOrder = storeContext
            .Products
            .Where(it => it.ProductId == 1)
            .FirstOrDefault();

string newDescription = "This is a new test description 1";            
salesOrder.ProductDescription = newDescription;

storeContext.Products.Update(salesOrder); // ensures that changes are being tracked on this entity. In this case, changes were already being tracked.
storeContext.SaveChanges();

Generated SQL:

UPDATE [Product] SET [ProductDescription] = @p0, [ProductLabel] = @p1, [ProductName] = @p2,[ProductSubcategoryID] = @p3, [UnitCost] = @p4, [UnitPrice] = @p5
WHERE [ProductID] = @p6;
SELECT @@ROWCOUNT;

Delete

In order to delete items with EF, the items need be attached and marked for deletion.

One way of doing this by ID is to use Attach:

var salesOrder = new SalesOrder { SalesOrderId = 10 };
storeContext.SalesOrders.Attach(salesOrder);
storeContext.SalesOrders.Remove(salesOrder);
storeContext.SaveChanges();

Items loaded with tracking are already attached, so calling Remove or including items in a RemoveRange call will also stage the items for deletion when SaveChanges is called:

Delete Multiple

// fetch arbitrary set of 10 orders
var salesOrders = storeContext
                .SalesOrders
                .OrderBy(it => it.SalesOrderId)
                .Skip(12500)
                .Take(10);

var allList = salesOrders.ToList();
var subList = allList.GetRange(2,2);

storeContext.SalesOrders.RemoveRange(subList);
storeContext.SaveChanges();

A Guide to EF Core 5 | Change Tracking, Relationships, & Inheritance

Index

Change Tracking:

EF Change Tracking “tracks” changes made to existing records.

Relationships:

EF Relationships define how entities relate to one another.

Inheritance:

EF Inheritance maps all entities in a particular hierarchy to the base Entity’s single table in a storage schema, or the rules of how the data relates to specific logical rules.

Change Tracking

When retrieving records from a context, changes made to fields of these records are cached in the dbContext instance so that they may be updated when SaveChanges is called. Since this is not always necessary and use extra memory it is helpful to fetch with the AsNoTracking option:

var salesOrder = context.SalesOrder    
    .Include(it => it.Product)
    .AsNoTracking()           
    .FirstOrDefault();        

context.SaveChanges();

It is also possible to check if tracked entities have changes by calling dbContext.ChangeTracker.HasChanges().

These assertions are true:

var salesOrders = storeContext
    .SalesOrders
    .OrderBy(it => it.SalesOrderId)
    .Skip(12500)
    .Take(10);

var allList = salesOrders.ToList();
var subList = allList.GetRange(2, 2);

Assert.False(storeContext.ChangeTracker.HasChanges());

foreach (var it in subList)            
        it.UnitCost = 99;

Assert.True(storeContext.ChangeTracker.HasChanges());

When tracking is enabled, these changes are automatically added to the cache of changed records on the DbContext. These can be accessed via:

dbContext.YourCollection.Local

When saving these changes, a transaction would be executed to persist the changes in the database.

A Note about Working with Identity Columns (Auto Increment)

When creating a new object without specifying the autoincrement ID column value such as this:

var order = new SalesOrder
    {
        OrderDate = DateTime.UtcNow,
        ProductId = productId,
        OrderQuantity = orderQuantity,
        UnitCost = product.UnitCost,
        UnitPrice = product.UnitPrice
    };

After inserting via:

await storeContext
        .SalesOrders               
        .AddAsync(order);
    
await storeContext.SaveChangesAsync();

order.SalesOrderId will be populated with the ID assigned from the database.

Relationship Types

One to One

A one to one relationship can be created with a primary key column that is also a foreign key to a parent entity table.

/// <summary>
/// Receipt for one SalesOrder.
/// </summary>
public class Receipt
{
    public int ReceiptId { get; set; }
    public int SalesOrderId { get; set; }
    public SalesOrder SalesOrder { get; set; }
}

/// <summary>
/// SalesOrder with one Receipt.
/// </summary>
public class SalesOrder
{
    public int SalesOrderId { get; set; }
    ...      
    public int ReceiptId { get; set; }
    public Product Product { get; set; }
    public Receipt Receipt { get; set; }
}

In OnModelCreating of StoreContext:

modelBuilder.Entity<Receipt>().ToTable("Receipt");
modelBuilder.Entity<Receipt>()
    .HasOne(it => it.SalesOrder)
    .WithOne(it => it.Receipt)
    .HasForeignKey<SalesOrder>(it => it.SalesOrderId);

One to Many

A one to many relationship can be created by a descendant table containing a foreign key to its parent entity while still having its own primary key.

/// <summary>
/// Product with many SalesOrders.
/// </summary>
public class Product
{
    public int ProductId { get; set; }
    ...
    public ICollection<SalesOrder> SalesOrders { get; set; }
}

/// <summary>
/// SalesOrder with one product.
/// </summary>
public partial class SalesOrder
{
    public int SalesOrderId { get; set; }
    ...    
    public Product Product { get; set; }
}
In OnModelCreating of StoreContext:
modelBuilder.Entity<SalesOrder>(entity =>
   {
       entity.ToTable("SalesOrder");               

       entity.HasOne(d => d.Product)
           .WithMany(p => p.SalesOrders)
           .HasForeignKey(d => d.ProductId)
           .OnDelete(DeleteBehavior.Cascade)
           .HasConstraintName("FK_SalesOrder_Product");
   });

Many to Many | New to EF Core 5

A many to many relationship between two entities requires an additional table to act as a join table. When two entities contain an ICollection of each other, EF will create this third table.

/// <summary>
/// Skill that is associated with many Employees.
/// </summary>
public abstract class Employee
{
    public int EmployeeId { get; set; }
    ...
    public ICollection<Skill> Skills { get; set; }
}

/// <summary>
/// Employee with many Skills.
/// </summary>
public class Skill
{
    public int SkillId { get; set; }
    ...
    public ICollection<Employee> EmployeeWithSkill { get; set; }
}

In OnModelCreating of StoreContext:

modelBuilder.Entity<Skill>()
            .HasMany(s => s.EmployeeWithSkill)
            .WithMany(s => s.Skills);

Notice that the generated join table EmployeeSkill also contains a composite primary key.

Inheritance & Composition

One of the most appealing benefits of using EF is the support for inherited properties between entities. Nested and/or inherited data can be represented in several ways as shown below.

Composition

A one-to-one relationship created in the database will be scaffolding as a composed entity. This method of creating the structure of your model uses the parent class as a field within the base class. In the example with Employee types this would look like the following:

public class Developer
{        
    public int LinesOfCodeWritten { get;set; }
    public Employee Employee { get;set; }
}

To fetch the Developer with Employee data:

var dev = storeContext
    .Developers
    .Where(it => it.EmployeeId == employeeId)
    .Include(a => a.Employee)
    .FirstOrDefault();

Note that access to this technique of nesting data would be used as developer.Employee.FirstName. This example model is a Developer which has all the properties of a Person. Ideally this can be done with inheritance and skip the nested Employee field. as shown below. This example is included because this is the result the scaffolding tool will create for a one-to-one relationship defined in the database. The scaffolding tools do not have a way to distinguish which resulting class fits the use case for the application.

Table per Type Inheritance (TPT) | New in EF Core 5

This method creates a separate table for the base class and derived classes. The derived classes will map to tables that contain a foreign key back to the base class table. When the entities are fetched, the fields from the parent table will be included via a join to that table.

public class A { 
    public int Id { get; set; }
    public string FieldSharedWithDerivativesOfA { get; set; }
}

public class B : A { 
    public string FieldSpecificToB { get; set; }
}

public class C : A { 
    public string FieldSpecificToC { get; set; }
}

It is worth pointing out that the resulting SQL tables for both a composed or inherited entity with TPT are the same.

Table per Hierarchy (TPH)

This method uses a single table for a set of entity types with a discriminator column (ex: EmployeeType) to determine how which columns make up the entity in a particular row in the table. Creating the model like so will create a single table with a Discriminator column. That discriminator column is actually just the type name which EF will use to determine which type to parse for a particular row.

public abstract class A { 
    public int Id { get; set; }
    public string FieldSharedWithDerivativesOfA { get; set; }

}
public class B : A { 
    public string FieldSpecificToB { get; set; }

}
public class C : A { 
    public string FieldSpecificToC { get; set; }
}

The difference in TPT and TPH in C# is only the abstract vs concrete base class. This is the default behavior for the migration created for these classes. TPT can also be done with an abstract base class if you specify it after migration.

A Guide to EF Core 5 | Cautionary Notes & Conclusions

Index

The important consideration when using EF is ensuring efficient loading of data from the database. This can be hard to catch in certain scenarios. While the tools can handle interactions with the database automatically, it may still be necessary to check for performance issues in the generated queries. The worst case scenario for a simple query in EF would be loading an entire record set to the application server before doing any filtering. IEnumerable represents a set of loaded data whereas IQueryable represents an expression to translated into SQL and executed on the SQL server.

When Reusing Filter Expressions

Say you’d like to reuse a filter expression in multiple methods that access the DbContext. You might be tempted to create a boolean function that takes your entity as a parameter and define your search criteria within that function. As shown in one of the pitfalls below, this will lead to loading an entire result set to the application server and then doing the filtering.

When EF cannot convert an expression to SQL for the database server, it may end up loading all records from that table to do the processing on each one in your application.

The following code uses an arbitrary filter method as part of the query:

public void PrintSalesOrders()
{
    var salesOrders = storeContext
        .SalesOrders
        .Where(isMatch);

    foreach (var s in salesOrders)
        Console.WriteLine(s);
}

private bool isMatch(SalesOrder it)
{
    return it.UnitPrice >= 20 && it.UnitPrice < 21 && it.OrderQuantity < 2;
}

Resulting SQL:

SELECT [s].[SalesOrderID], [s].[DiscountAmount], [s].[OrderDate], [s].[OrderQuantity], [s].[Product
, [s].[UnitCost], [s].[UnitPrice]
FROM [SalesOrder] AS [s]

That selected and returned all the records from the table to the application!

While EF doesn’t convert your boolean method to SQL that can be sent to the database, an Expression< Func< T, bool>> can be converted.

An expression defined as:

Expression<Func<SalesOrder, bool>> filter = it => it.UnitPrice >= 20 && it.UnitPrice <= 21 && it.OrderQuantity < 2;

Can later be used in multiple interactions with the database like so:

var salesOrders = storeContext
    .SalesOrders
    .Where(filter);

This method allows filtering to happen on the database server as it should! This is just one example of an expression fall that can cause more data to be loaded than necessary when working with with EF.

Conclusion

The overhead of having EF generate based on your C# code is low but the queries generated are intended to converge toward the ones that would be written explicitly. When there are doubts about what queries are being created by EF to manipulate you can always check the outputs in the SQL Server Profiler. EF works best when working with short-lived database context instances and avoiding bulk inserting or updating. Should there be a need to choose between EF, Dapper, or other database access techniques, keep in mind that these are built on top of standard ADO .NET and converge toward the same SQL queries in the end result. If benefits of one over the other are found, an abstracted data access layer can allow for multiple implementations to be utilized where most appropriate.

Index

Quick Menu

Overview

Solution Setup

Database Interaction

Change Tracking, Relationships, & Inheritance

Cautionary Notes

Conclusion

Get To Know Us

1 – Overview

Overview
Audience
Why Use Entity Framework
Comparison of Database Access Techniques
Concepts in Entity Framework

2 – Solution Setup

Solution Structure
Scaffolding
Dependencies
CLI Parameters
Code-First Migrations
Model Definition
Naming of Tables vs Entities
IDesignTimeDbContextFactory
Creating and Running Migrations
How does EF know what to change?
Migration Management

3 –Database Interaction

Create
Creating Nested/Inherited Entities
Read
Single
Single with Navigation
Many with Navigation
Pagination
Update
Delete

4 – Change Tracking, Relationships, & Inheritance

Change Tracking
A Note about Working with Identity Columns (Auto Increment)
Relationship Types
One to One
One to Many
Many to Many | New to EF Core 5
Inheritance & Composition
Composition
Table per Type Inheritance (TPT) | New in EF Core 5
Table per Hierarchy Inheritance (TPH)

A Guide to Entity Framework (EF) Core 5

A Guide to EF Core 5 | Overview

Audience

Why Use Entity Framework?

Comparison of Database Access Techniques

Concepts in Entity Framework

A Guide to EF Core 5 | Solution Setup

Solution Structure

Scaffolding

Dependencies

CLI Parameters

Code-First Migrations

Model Definition

Naming of Tables vs Entities

Design Time DbContext Factory

Creating and Running Migrations

How does EF know what to change?

Migration Management

A Guide to EF Core 5 | Database Interaction

About Intertech

Create

Read

Update

Delete

A Guide to EF Core 5 | Change Tracking, Relationships, & Inheritance

Change Tracking

A Note about Working with Identity Columns (Auto Increment)

Relationship Types

One to One

One to Many

Many to Many | New to EF Core 5

Inheritance & Composition

Composition

Table per Type Inheritance (TPT) | New in EF Core 5

Table per Hierarchy (TPH)

A Guide to EF Core 5 | Cautionary Notes & Conclusions

When Reusing Filter Expressions

Conclusion

Index

1 – Overview

2 – Solution Setup

3 –Database Interaction

4 – Change Tracking, Relationships, & Inheritance

5 – Cautionary Notes

6 – Conclusion

Author

Let’s Build Something Great Together!

We’re ready to get started.

We Use Cookies From Time to Time