Getting the Most Out of Entity Framework Core – Part 3

“We’re entering a new world in which data may be more important than software.” Tim O’Reilly

“It’s difficult to imagine the power that you’re going to have when so many different sorts of data are available.” Tim Berners-Lee

“Our ability to do great things with data will make a real difference in every aspect of our lives.” Jennifer Pahlka

This is the third post in the series. To read the first two episodes at Getting the Most Out of Entity Framework Core - Part 1 and Getting the Most Out of Entity Framework Core with Query Tags.

Entity Framework is a very powerful framework that can assist you with working with data in the many .NET Core applications that developers create to solve problems. Like any framework we use, we need to make sure we first understand it and then push it to get the insights that we seek.

This blog post is my attempt to bring a few nuggets of knowledge about Entity Framework Core (EF Core) to developers to make sure that their data queries are efficient and optimized. We will first travel across some of the new features of EF Core since the 2.0 release to make more powerful solutions for our companies, clients or customers. More posts around how to get better with Entity Framework Core are coming!

Global Query Filters

Sometimes we need to have a way to not allow certain data into our applications. I find that for customer-facing applications the need to show “deleted” or deprecated data is a problem. Adding a Where operation to every EF Core LINQ query to not bring back the deprecated data is not only time consuming but if forgotten can cause bugs in our code. I find that using the Global Query Filter feature for EF Core to be a valuable addition if done correctly and with forethought.

Let’s look at the following Entity Model for a Blog Post. It has the IsDeleted boolean property. I don’t like to really delete data so I just flip this bit to signal when a post is not to be shown. I also don’t want to show Posts that have their IsDeleted properties set to True in my application. What to do? I could just add the check to every EF Core Query I write that involves Posts. That smells of bad coding to me (with a caveat we will discuss later).

public class Post
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public bool IsDeleted { get; set; }

public int BlogId { get; set; }
public Blog Blog { get; set; }

I don’t want to forget to include that condition in my queries but do I need to? Not with a Global Query Filter, I don’t! Let’s look at one for not allowing any Posts to be included in queries. We just need to add a HasQueryFilter() method to the modelBuilder’s Post entity and give the lambda expression to not include those undesired Posts.

protected override void OnModelCreating(ModelBuilder modelBuilder)

// Configure entity filters
modelBuilder.Entity().HasQueryFilter(p => !p.IsDeleted);

Now let’s look at the code for the EF Core LINQ query. A simple query to get the Blog entities and include the associated Posts for each.

var blogs = db.Blogs
.Include(b => b.Posts)

The best part of this is that EF Core will actually modify the SQL that is sent over to SQL Server so that the filter is done on the server side. If we look at the SQL that was sent over we have the following.

SELECT [p].[PostId], [p].[BlogId], [p].[Content], [p].[IsDeleted], [p].[Title]
FROM [Posts] AS [p]
SELECT [b0].[BlogId]
FROM [Blogs] AS [b0]
) AS [t] ON [p].[BlogId] = [t].[BlogId]
WHERE [p].[IsDeleted] = 0
ORDER BY [t].[BlogId]

Using Global Query Filters does have one drawback. I did say we would have a caveat. Once the filter is created it cannot be overridden. So think smart about how you want this feature used in your applications. I actually recommend creating two DBContext: one for the consumer-facing part of your application (that includes the filters like above) and another dbContext for administration purposes that does not include any Global Query Filters.

String Interpolation in Raw SQL Methods

The second EF Core feature I want to give love to is one that comes in handy when you have a SQL Query that cannot be written in LINQ. I know LINQ can do most anything but I have come across some needs where LINQ just cannot handle the need or maybe does not give the optimal SQL. For this, we can use a C# 6 feature for String Interpolation. A great feature of C# and the EF team leverages it to allow us, developers, to have raw SQL queries. Don’t worry these are SQL-injection proof!

Let’s look at the following code for a query that will get it’s where conditional from a string variable.

var city = “Redmond”;

using (var context = CreateContext())
FROM Customers
WHERE City = {city}”);

The nice part of this feature is that FromSql() takes a FormattableString type. This allows EF Core to inspect the string that was passed and find the interpolated expressions or { } blocks inside the FormattableString. This allows the framework to replace those { } blocks with a placeholder parameter (@p0) then create a new parameter using something like SqlParameter(“@p0”, formatableString.GetArgument(0)). So the SQL and the parameter value “Redmond” get sent to SQL Server as the following:

FROM Customers
WHERE City = @p0=N’Redmond’

SQL Server will then perform the following query and return the result set back to EF Core.

FROM Customers
WHERE City = ‘Redmond’

There are a few things to remember about using this feature of EF Core:

Wrap Up

I hope this third post about Entity Framework Core has given you even more knowledge to go and build amazing applications! Good luck and have fun!

“If you’re a scientist, and you have to have an answer, even in the absence of data, you’re not going to be a good scientist.” Neil deGrasse Tyson


How to set up Logging in ASP.NET Core