Entity framework features I wish I knew earlier

Modified November 14, 2023

Tim Deschryver

timdeschryver.dev

We as developers can make a lot of code work. But, does this mean we're doing it the "right way"? Sometimes it will be, other times there's a better way. And when there's a better way we're often not even aware of it. There are many reasons for this. Deadlines, having a bad day, new features, copy-pasting a solution (or generated by AI these days), lack of knowledge, and other factors can lead to not ideal solutions.

This blog post is about me not knowing all the functionality that Entity Framework provides, which I wish I knew earlier. I discovered these while bumping across someone's article or talk, a friend who pointed me to it, or by reading the documentation. Even by writing this blog post, I discovered some additional functions.

Instead of using workarounds, that often introduce some gotcha's, I hope this blog post can also help you to do things in the recommended/official way.

• Starting point
• AutoInclude
• Single or Split Queries
• HasQueryFilter
• Temporal (History) Table
• Shadow properties
• DbFunction
• SqlQuery to unmapped type
• Conclusion

Starting point link

To follow along with the examples, we need a starting point to work with. For this blog post, I'm using the following simple model. We have a Customer entity, which has a collection of Address entities.

 content_paste
public class Customer
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public List<Address> Addresses { get; set; }
}
 
public class Address
{
    public Guid Id { get; set; }
    public string Street { get; set; }
}

AutoInclude link

You probably already know that you can eager load related entities using the Include method when retrieving data. For example, let's say that we have a customer and we want to eager load the addresses while querying a customer. To implement this, you use the Include method to include the customer's addresses.

 content_paste
var customersWithAddresses = await dbContext.Customers
    .Include(c => c.Address)
    .ToListAsync();

The benefit of eager loading is that you can minimize the number of database roundtrips to query the data you need. Instead, all of the data is retrieved in a single query (or a few while using AsSplitQuery()). In many cases, a single query is faster than multiple queries, and it also reduces the load on the database server.

But, this can become repetitive when you need to include the same entity relation(s) in most of your queries. It can also lead to bugs when you forget to include a related entity that's used further down.

AutoInclude is a feature that simplifies the eager loading of related entities. It automates this process by automatically including the related entities when retrieving the data. This means that you don't have to use the Include method anymore within your queries.

I find this useful when I know that I always need access to the related entity. This way I don't have to worry about the entity's state, whether the related entity is retrieved or not.

To enable AutoInclude, navigate to the related entity and invoke the AutoInclude method using the EntityTypeBuilder while configuring your entity relationships. The example can be refactored to the following configuration.

 content_paste
internal class CustomerEntityConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.Navigation(e => e.Addresses)
            .AutoInclude();
    }
}

Now, when you retrieve the data, the related addresses are automatically included.

 content_paste
var customersWithAddresses = await dbContext.Customers
    .ToListAsync();

For those one-off cases where you're not interested in the entity, you can use the IgnoreAutoInclude method to not automatically include the configured included entities. This can be useful when performance is critical and you don't need the related entity.

 content_paste
var customers = await dbContext.Set<Customer>()
    .IgnoreAutoIncludes()
    .ToListAsync();

Documentation

Single or Split Queries link

Including (or AutoIncluding) related entities can lead to performance issues when the related entities contain a lot of relational data. Because the generated SQL query contains many joins, which can lead to a lot of duplicated data being retrieved. This is called the Cartesian explosion.

For example, let's say that a customer has 10 addresses. When you query the customer, the generated SQL query will contain a join to the addresses. The result is that the customer is retrieved 10 times, once for each address.

A solution to this problem is to use AsSplitQuery to split the query into multiple queries. This way, the customer is retrieved once, and the addresses are retrieved in a separate query.

 content_paste
var customersWithAddresses = await dbContext.Set<Customer>()
    .AsSplitQuery()
    .ToListAsync();

Entity Framework also helps you out by logging a warning when it detects that multiple collections are loaded in a singe query.

A single query is the default behavior, but you can also enable this behavior globally by using the UseQuerySplittingBehavior method on the DbContextOptionsBuilder.

 content_paste
optionsBuilder.UseSqlServer(connectionString, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

When a split queries is enabled, you can use AsSingleQuery to force a single query.

 content_paste
var customersWithAddresses = await dbContext.Set<Customer>()
    .AsSingleQuery()
    .ToListAsync();

Documentation

HasQueryFilter link

This tip is similar to AutoInclude because HasQueryFilter also allows you to configure your entity in a centralized location. As the name implies, HasQueryFilter can be used to filter out entities when retrieving data.

HasQueryFilter defines a global filter that is applied to all queries for that entity. I find this useful to for example, filter out entities that are soft deleted, or to filter entities that you're not interested in but cannot be removed. Instead of duplicating the following logic into all your queries to remove deleted customers.

 content_paste
var customers = await dbContext.Set<Customer>()
    .Where(q => q.DeletedOn == null)
    .ToListAsync();

You can refactor this by making use of HasQueryFilter.

 content_paste
internal class CustomerEntityConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.HasQueryFilter(q => q.DeletedOn == null);
    }
}

When there's a global filter, you can also disable it for those one-off queries using IgnoreQueryFilters.

 content_paste
var customers = await dbContext.Set<Customer>()
    .IgnoreQueryFilters()
    .ToListAsync();

Documentation

Temporal (History) Table link

A SQL temporal table is useful because it captures all data-related changes to a SQL table. How this works is that a new table (the default convention is that the table name is suffixed with History) is created with the same structure as the original table. There will also be an additional two columns PeriodStart and PeriodEnd (these are the default names) that are created within the newly created table. When a record in the original table is updated, the old version is inserted into the history table. When a record is deleted, the old version is also inserted into the history table.

Tracking changes this way lets you capture the whole history of a particular table. This is useful to keep an audit log of changes.

Of course, that data adds little to no value when you can't query it. That's where temporal tables come in, as it allows you to query the history table to gain the full (or bigger) picture.

To flag an entity as a temporal table, use IsTemporal while configuring the model. When you're generating a new database schema, you'll notice that the history table is included in the new script.

 content_paste
internal class CustomerEntityConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers", o => o.IsTemporal());
    }
}

Once the table is created, you can query and retrieve the historical data of the table using various built-in methods.

 content_paste
var customerHistory = await dbContext.Set<Customer>()
    .TemporalAll()
    .ToListAsync();

In the example above, all the historic data is retrieved, but it's also possible to retrieve the historic data within a specific timeframe. I find this useful to query data based on a year.

 content_paste
var customerHistory = await dbContext.Set<Customer>()
    .TemporalFromTo(startOfYear, endOfYear)
    .ToListAsync();

Documentation

Shadow properties link

I've seen many models that are bloated with properties that shouldn't have been included. But instead, we can use shadow properties to keep the model clean and only include the properties that are relevant to the domain.

A use case for shadow properties is the audit columns, e.g. CreatedOn, CreatedBy, ... Or, the period columns from the history table. These columns are useful to keep track of who created or updated a record, but they don't add any value to the domain.

Another use case are the properties that hold the reference to a foreign key while the navigation property is also included. I find this very confusing because it's not clear which property to use. For example:

 content_paste
class Customer
{
    // foreign key
    public Guid AddressId { get; set; }
 
    // navigation property
    public Address Address { get; set; }
}

Instead, I prefer the following model. Entity Framework will automatically create the needed shadow properties and add the needed relationship constraints. In this example, the shadow property will be AddressId.

 content_paste
class Customer
{
    public Address Address { get; set; }
}

When we take a look at the database schema, we'll see that the column AddressId is created and that it's configured as a foreign key to the Addresses table.

If we take the original example, where a customer holds a collection of addresses (instead of a single address), we see that the CustomerId column is created in the Addresses table because the relationship is a one-to-many relationship.

Shadow properties can also be used to query data, for example, to query the history table we created previously. To access the shadow properties, use the EF.Property method.

 content_paste
var customers = await dbContext.Set<Customer>()
    .TemporalAll()
    .Where(c => EF.Property<DateTime>(c, "PeriodStart") >= DateTime.Today)
    .OrderBy(e => EF.Property<DateTime>(e, "PeriodEnd"))
    .ToListAsync();

With this knowledge, we can refactor the previous snippet using the DeletedOn property to use a shadow property in the global filter.

 content_paste
internal class CustomerEntityConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        // builder.HasQueryFilter(q => q.DeletedOn == null);
 
        // This is needed to create the column in the database
        builder.Property<DateTime?>("DeletedOn");
        builder.HasQueryFilter(e => EF.Property<bool>(e, "DeletedOn") == null);
    }
}

To set and update the value of a shadow property, get the EntityEntry of the entity and use the Property method.

 content_paste
dbContext.Entry(customer).Property("DeletedOn").CurrentValue = DateTime.UtcNow;
dbContext.SaveChanges();

Where this comes in handy is when you don't need to think about these properties, but that they are automatically set. This can be achieved by overriding the SaveChanges method of the DbContext.

 content_paste
public class EfDemoDbContext : DbContext
{
    public override int SaveChanges()
    {
        ChangeTracker.DetectChanges();
        foreach (var entry in ChangeTracker.Entries())
        {
            if (entry.State == EntityState.Added)
            {
                entry.Property("CreatedOn").CurrentValue = DateTime.UtcNow;
            }
            if (entry.State == EntityState.Modified)
            {
                entry.Property("UpdatedOn").CurrentValue = DateTime.UtcNow;
            }
        }
        return base.SaveChanges();
    }
}

Documentation

DbFunction link

In a previous blog post, Consuming SQL Functions with Entity Framework, I explained how to consume SQL functions with Entity Framework. The TLDR version is that you can use the DbFunction attribute to map a C# method to a SQL function.

In the example below we map the SoundEx SQL function to a C# method.

 content_paste
public class EfDemoDbContext : DbContext
{
    public EfDemoDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }
 
    [DbFunction(Name = "SoundEx", IsBuiltIn = true, IsNullable = false)]
    public static string SoundEx(string input)
    {
        throw new NotImplementedException();
    }
}

The SoundEx method can now be consumed in a query.

 content_paste
var customersViaSoundEx = await dbContext.Set<Customer>()
    .Where(c => EfDemoDbContext.SoundEx(c.Name) == EfDemoDbContext.SoundEx("Jhon Do"))
    .ToListAsync();

SqlQuery to unmapped type link

In another blog post, You can now return unmapped types from raw SQL select statements with Entity Framework 8, we've seen that it's possible to return unmapped types from raw SQL select statements. This is useful to use an optimized query or to return a subset or aggregation of the data.

Conclusion link

To recap this post, all I can say is to take advantage of the features that Entity Framework provides.

The examples in this post show that Entity Framework has powerful features built-in for enhancing the performance and maintainability of your application. The trick is to know that they exist.

Outgoing links

• You can now return unmapped types from raw SQL select statements with Entity Framework 8
• Consuming SQL Functions with Entity Framework

Feel free to update this blog post on GitHub, thanks in advance!

Support me

I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.

Share this post

Twitter LinkedIn