651.288.7000 info@intertech.com

A Guide to EF Core 5

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. 

EF Core solidifies application data access

Audience

Developers familiar with database access via ADO.NET, Dapper, or other methods outside of a full ORM should be able to relate to the workflow involved with 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.

Sections

A Guide To EF Core 5 In Eight Sections

Quick Menu

1 – Why Use Entity Framework Core 5

 

2 – Getting Started

      • Solution Structure
      • Database Context

3 –Scaffolding

      • Solution
      • CLI Parameters
      • Model Definition
      • Creating and Running Migrations
      • How does EF know what to change?
      • Migration Management

4 – Basic Features

      • Create
        1. Creating Nested/Inherited Entities
      • Read
        1. Single
        2. Single with Navigation
        3. Many with Navigation
        4. Pagination
      • Update
      • Delete

5 – More Features

      • Create & Get New ID
      • Change Tracking
      • Naming of Tables vs Entities

6 – Relationship Types

      • One to One
      • One to Many
      • Many to Many

7 – Inheritance & Composition

      • Composition
      • Table per Type (TPT)
      • Table per Hierarchy (TPH)

8 – Pitfalls to Avoid

      • When Reusing Filter Expressions
      • N+1 Problem

9 – Conclusion

 

A Guide To Entity Framework (EF) Core 5

Why Use Entity Framework?

There are two good reasons to use Entity Framework! 

Make data access clear and concise using LINQ expressions and support comparison of model versions to create migrations.

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.

 

Next, we’ll compare database access options.

Database access in plain ADO .NET:

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 visitsTimeline = db.Query("SalesOrder")
    .Select("SalesOrder.*")            
    .Where("SalesOrder.ProductId", productId)        
    .Get<SalesOrder>();

The Entity Framework way of accessing the SalesOrder records from the database looks more like this:

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

Next we’ll move into more features of EF.

Getting Started With Entity Framework

The following 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) Create C# classes and allow the CLI tools to generate a migration to be applied to the database. 

 

2) Create the database tables in an RDBMS and use the scaffolding tool to create an EF model and entity classes from 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.

Solution Structure

For an example based around a ficticious store, the projects in the solution may look like:

 

      • 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.
      • Store Domain – Class Library | Domain objects shared across all projects in the solution.

 

Database Context

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 at runtime 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 implement IDesignTimeDbContextFactory.

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.

Scaffolding & Migrations

An existing database can be scaffolded. After scaffolding, set up an initial migration for the applciation. 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.

Solution

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
    •  

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"

CLI Parameters
    • --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 annoations 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 definition.

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 --startup-project ..StoreAPI
Create the the SQL for that migration via:
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 --startup-project ..StoreAPI

The resulting SQL file will contain:

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 indicated 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.

Basic Features

Next, we’ll go through the SQL generated by EF for basic CRUD operations.

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 this, the order object is

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:
// 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();

More Features

Feature 1: New ID
Feature 2: Tracking Changes
Feature 3: Naming of Tables vs. Entities

Create & Get New ID

When working with an autoincrement ID column:

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.

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());

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.

Relationship Types

Type 1: One to One
Type 2: One to Many
Type 3: Many to Many

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 parant 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
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 scaffodling 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 (TPT)
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 int Id { get; set; }
    public string Field1 { 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.

Pitfalls to Avoid

The key to 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.

 

Inefficient Filtering of Records

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> 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!

The N+1 problem. (Needs a section)

Conclusion

The overhead of having EF generate based on your C# code is low but the queries generated are intended to be very similar to the ones that would be created manually. 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. Should you need to choose between EF, Dapper, or other database methods, keep in mind that these are 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, and abstracted data access layer can allow for multiple implementations to be utilized where most appropriate.

Author

by Matthew Routon – Intertech, Inc. Consultant

Industries That Trust Intertech

Details & Case Studies That Cross Vertical Boundaries

Financial Services

Healthcare & MedTech

Manufacturing

Business Services

High Tech

Government & Legal Services

Energy

Agriculture & Food Processing

Retail

Aerospace & Defense

Gaming & Entertainment

NonProfit

Three Great Reasons To Let Intertech Build Your Software “Right” The First Time!

To understand why so many companies rely on Intertech for their software consulting and software education needs, you must understand the importance we place on staying up-to-date with the most current technologies and methodologies.

“When an outside firm asked over 4000 of our customers these questions, we immediately understood why they trusted Intertech!”

Say “Yes” To Intertech!

  • Would you use Intertech again? 99.55% 99.55%

99.55% of customers answered YES!

  • Are you happy with Intertech? 99.55% 99.55%

99.55% of customers answered YES!

  • Would you refer Intertech to others? 99.70% 99.70%

99.70% of customers answered YES!

Discover All That Intertech Can Do For You!

Clients Testimonials

“We look forward to working with Intertech on future projects to drive even more value from our data.”

 

99.55% Of Our Past Clients Said “Yes, They Would Work With Us Again!”

“Intertech’s SQL expertise was invaluable in improving our database server performance. Thanks to the guidance and work from Intertech consultants, we’ve reduced runtime on common queries, modernized our technology stack, created a platform for real-time sales feedback, and enabled more comprehensive strategic planning for our company. We look forward to working with Intertech on future projects to drive even more value from our data.” — Tom B

 

Software Consulting Services Hand-Shake

“Intertech eliminated hundreds of hours in manual work…”

“Working with Intertech has been a great experience. Converting a manual process of entering data into spreadsheets to a new online system gives us access to real-time results. We have modernized our business like no one else in the market. Eliminating hundreds of hours in manual work, while giving our customers a tool to quickly manage their business and improve profits is a great benefit we can now offer.” — Jay W

“It was a pleasure working with everyone at Intertech.”

“Thanks everyone for all of your hard work on another successful portal project! As always, it has been a pleasure working with everyone at Intertech.” — Sandra K

“A credit to their hard work…”

“It’s great to hear about the positive responses to eCharging and the enthusiasm shown by those involved. These remarks are a credit to the hard work that you and the rest of the eCharging team have put into the project. These comments remind us of the important impact our daily work has on improving the criminal justice system across the state. Good work and congratulations!” — Robert H

Let’s Build Something Great!

Tell us what you need and we’ll get back with you ASAP!