“Data! Data! Data! I can’t make bricks without clay!” Sir Arthur Conan Doyle

“Not everything that can be counted counts, and not everything that counts can be counted.” Albert Einstein

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 or as Albert Einstein stated to find those things that count to be counted. 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!

All the demos I show in this blog post can be found in the following GitHub repository:

https://github.com/cwoodruff/EFCore21Demos

I hope this brings all of us this to this quote I love:

“An investment in knowledge pays the best interest.” Benjamin Franklin

EF Core: Explicitly Compiled Queries

All of our applications that use EF Core to query data perform these queries more than once during the lifetime of the application. There is a simple way to improve the performance of the application when using these common queries. When we run our Windows or ASP.NET Core applications developed in .NET Core, we use Roslyn to compile the code we wrote so that the computer can efficiently execute the application. So why are we running our data queries with that same logic?

EF Core automatically compiles, and caches queries based on a hashed representation of the query expressions. We can gain some performance improvements by allowing EF Core to bypass the computation of the query hash and the cache lookup that happens each time we normally run queries in EF Core. What we get is a compiled query that can be invoked through a delegate on the DBContext from EF Core.

If we use the AdventureWorks database for this example, we can have a compiled query that will return the first Customer for a given AccountNumber.

// Create explicit compiled query var query = EF.CompileAsyncQuery((AdventureWorksContext context, string id) => context.Customers.First(c => c.AccountNumber == id));

We now can use the compiled query with a given DBContext (AdventureWorksContext) to get the data needed.

using (var db = new AdventureWorksContext()) { foreach (var id in accountNumbers) { // Invoke the compiled query var customer = query(db, id); } }

When I run this foreach loop I see around a 53% performance gain using the compiled query over a normal EF Core query. That is a huge improvement. The improvements we get in our applications will vary but why not use this great feature of EF Core at least in queries that we have identified as our most used?

EF Core: Database Scalar Function Mapping

Another useful way to improve the use of EF Core and also make some queries better is to map scalar functions for your database in the DBContext we develop. An example of this is in the following code for BloggingContext.

public class BloggingContext : DbContext { [DbFunction] public static int PostReadCount(int blogId) { … } }

The C# function that has been annotated with the [DbFunction] attribute now can be called in our applications as followed.

var query = from p in context.Posts where BloggingContext.PostReadCount(p.Id) > 5 select p;

Please note that this EF Core feature can only be used to return scalar values so we cannot return complex values at this time.

EF Core: Query Types

Currently, EF Core does not have a method to model views that exist in our databases. That is a shame because views can be a powerful method to get better insights from our relational databases. What if I told we now we do have a way to handle database views? Interested? Let’s look at how we can do it.

We will be using the following view from a small database in one of my demos. This view calculates the number of blog posts for each blog in the database and returns the name of the blog and an integer value for the post count. Just a reminder that Query Types cannot be used to insert, update or delete data.

CREATE VIEW View_BlogPostCounts AS SELECT Name, Count(p.PostId) as PostCount from Blogs b JOIN Posts p on p.BlogId = b.BlogId GROUP BY b.Name

When we develop our EF Core DbContext, we can now add a query that can be mapped to a view. This is done in the OnModelCreating() method as shown below.

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder .Query<BlogPostsCount>().ToView("View_BlogPostCounts") .Property(v => v.BlogName).HasColumnName("Name"); }

We can now use the DBQuery in our BloggingContext to get the collection of blogs and post counts.

using (var db = new BloggingContext()) { var postCounts = db.BlogPostCounts.ToList(); foreach (var postCount in postCounts) { Console.WriteLine($"{postCount.BlogName} has {postCount.PostCount} posts."); Console.WriteLine(); } }

We can also use Query Types for other scenarios like mapping to tables that do not have primary keys, queries defined in the model or can serve as the return type for FromSql() queries.

EF Core: DbContext Pooling

If you are an ASP.NET Core developer, the following feature for EF Core will sure to be a powerful tool in your toolbelt in the future. By default, our ASP.NET Core applications will register the DBContext into the dependency injection (DI) container so that we can obtain instances of the type for use. What we get with this method is a new instance of our DBContext for each request. What if we could have a better way of handling our DBContext?

We now have a way to create a pool of DBContext types at the start of our web application. These pooled types will still be placed in the DI container for use in our application, but we will no longer be creating a new instance of the DBContext for each call. The new DBContext pooling just needs AddDBContextPool instead of AddDBContext at the time of registering the service.

You have two ways to register the DBContext. The most common way is through the Startup ConfigureServices() method. You can also set up through the OnConfiguring() method in the DBContext.

public void ConfigureServices(IServiceCollection services) { services.AddDbContextPool<BloggingContext>(c => c.UseSqlServer(ConnectionString)); }

Doing tests involve DBContext Pooling, I have found that just changing to use pooling can increase the number of calls using a DBContext by around 25%. That is a huge impact just for a simple change in your ASP.NET Core solution.

Wrap Up

I hope in the new year; you find these EF Core features as a way to improve the performance and efficiency of your .NET Core applications. I also hope that learning and digging deeper into the frameworks and tools that we use every day also makes you a better developer.

This blog post is part of The Second Annual C# Advent.

“We are drowning in information but starved for knowledge.” John Naisbitt