--- title: Entity framework features I wish I knew earlier slug: entity-framework-features-i-wish-i-knew-earlier description: Some useful built-in Entity Framework features that I discovered along the way. date: 2023-07-12 tags: .NET, Entity Framework --- 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](#starting-point) - [AutoInclude](#autoinclude) - [Single or Split Queries](#single-or-split-queries) - [HasQueryFilter](#hasqueryfilter) - [Temporal (History) Table](#temporal-history-table) - [Shadow properties](#shadow-properties) - [DbFunction](#dbfunction) - [SqlQuery to unmapped type](#sqlquery-to-unmapped-type) - [Conclusion](#conclusion) ## Starting point 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. ```csharp public class Customer { public Guid Id { get; set; } public string Name { get; set; } public List
Addresses { get; set; } } public class Address { public Guid Id { get; set; } public string Street { get; set; } } ``` ## AutoInclude 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. ```csharp 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. ```csharp internal class CustomerEntityConfiguration : IEntityTypeConfiguration { public void Configure(EntityTypeBuilder builder) { builder.Navigation(e => e.Addresses) .AutoInclude(); } } ``` Now, when you retrieve the data, the related addresses are automatically included. ```csharp 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. ```csharp var customers = await dbContext.Set() .IgnoreAutoIncludes() .ToListAsync(); ``` [Documentation](https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#model-configuration-for-auto-including-navigations) ## Single or Split Queries `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](https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries#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. ```csharp var customersWithAddresses = await dbContext.Set() .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`. ```csharp optionsBuilder.UseSqlServer(connectionString, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)); ``` When a split queries is enabled, you can use `AsSingleQuery` to force a single query. ```csharp var customersWithAddresses = await dbContext.Set() .AsSingleQuery() .ToListAsync(); ``` [Documentation](https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries#cartesian-explosion) ## HasQueryFilter 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. ```csharp var customers = await dbContext.Set() .Where(q => q.DeletedOn == null) .ToListAsync(); ``` You can refactor this by making use of `HasQueryFilter`. ```csharp internal class CustomerEntityConfiguration : IEntityTypeConfiguration { public void Configure(EntityTypeBuilder 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`. ```csharp var customers = await dbContext.Set() .IgnoreQueryFilters() .ToListAsync(); ``` [Documentation](https://learn.microsoft.com/en-us/ef/core/querying/filters) ## Temporal (History) Table A [SQL temporal table](https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16) 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. ```csharp internal class CustomerEntityConfiguration : IEntityTypeConfiguration { public void Configure(EntityTypeBuilder 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. ```csharp var customerHistory = await dbContext.Set() .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. ```csharp var customerHistory = await dbContext.Set() .TemporalFromTo(startOfYear, endOfYear) .ToListAsync(); ``` :::warning ⚠️ Keep in mind that all the entities that are auto-included are also included in the temporal query, which most often will throw an exception. The same counts for the global query filters. To avoid this, you can use `IgnoreAutoIncludes` and `IgnoreQueryFilters` methods to disable this functionality. ::: [Documentation](https://learn.microsoft.com/en-us/ef/core/providers/sql-server/temporal-tables#querying-historical-data) ## Shadow properties 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: ```csharp 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`. ```csharp 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](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.ef.property?view=efcore-7.0). ```csharp var customers = await dbContext.Set() .TemporalAll() .Where(c => EF.Property(c, "PeriodStart") >= DateTime.Today) .OrderBy(e => EF.Property(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. ```csharp internal class CustomerEntityConfiguration : IEntityTypeConfiguration { public void Configure(EntityTypeBuilder builder) { // builder.HasQueryFilter(q => q.DeletedOn == null); // This is needed to create the column in the database builder.Property("DeletedOn"); builder.HasQueryFilter(e => EF.Property(e, "DeletedOn") == null); } } ``` To set and update the value of a shadow property, get the `EntityEntry` of the entity and use the `Property` method. ```csharp 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`. ```csharp 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](https://learn.microsoft.com/en-us/ef/core/modeling/shadow-properties) ## DbFunction In a previous blog post, [Consuming SQL Functions with Entity Framework](../consuming-sql-functions-with-entity-framework/index.md), 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. ```csharp public class EfDemoDbContext : DbContext { public EfDemoDbContext(DbContextOptions 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. ```csharp var customersViaSoundEx = await dbContext.Set() .Where(c => EfDemoDbContext.SoundEx(c.Name) == EfDemoDbContext.SoundEx("Jhon Do")) .ToListAsync(); ``` ## SqlQuery to unmapped type In another blog post, [You can now return unmapped types from raw SQL select statements with Entity Framework 8](../you-can-now-return-unmapped-types-from-raw-sql-select-statements-with-entity-framework-8/index.md), 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 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. :::info 🧑‍💻 The source code that is used throughout this blogpost is available on [GitHub](https://github.com/timdeschryver/entity-framework-demo). 📔 And, if you want to take a closer look and acquire more information, feel free to use my [Microsoft Learn Collection on Entity Framework](https://learn.microsoft.com/en-us/users/timdeschryver/collections/qnds53g3y0eyj). :::