Entity framework features I wish I knew earlier

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

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.

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.

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

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.

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.

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.

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

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.

You can refactor this by making use of HasQueryFilter.

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

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.

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

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.

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

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:

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.

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.

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

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

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.

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.

The SoundEx method can now be consumed in a query.

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.

Note

🧑‍💻 The source code that is used throughout this blogpost is available on GitHub.

📔 And, if you want to take a closer look and acquire more information, feel free to use my Microsoft Learn Collection on Entity Framework.

Outgoing links

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

Join My Newsletter (WIP)

Join my weekly newsletter to receive my latest blog posts and bits, directly in your inbox.

Support me

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

Buy Me a Coffee at ko-fi.com PayPal logo

Share this post

Twitter LinkedIn