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

Entity Framework is a very powerful tool that can assist you when 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 and I hope this brings all of us 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.

1.   // Create explicit compiled query  

2.   var query = EF.CompileAsyncQuery((AdventureWorksContext context, string id)  

3.    => context.Customers.First(c => c.AccountNumber == id));  


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

1.   using (var db = new AdventureWorksContext())  

2.   {  

3.       foreach (var id in accountNumbers)  

4.       {  

5.           // Invoke the compiled query  

6.           var customer = query(db, id);  

7.       }  

8.   }  


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

1.   public class BloggingContext : DbContext  

2.   {  

3.       [DbFunction]  

4.       public static int PostReadCount(int blogId)  

5.       {  

6.        …  

7.       }  

8.  

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

1.   var query =  

2.    from p in context.Posts  

3.    where BloggingContext.PostReadCount(p.Id) > 5  

4.    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, which is a shame because views can be a powerful method to get better insights from our relational databases. What if I told you there is 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, Query Types cannot be used to insert, update or delete data.

1.   CREATE VIEW View_BlogPostCounts AS  

2.    SELECT NameCount(p.PostId) as PostCount from Blogs b  

3.    JOIN Posts p on p.BlogId = b.BlogId  

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

1.   protected override void OnModelCreating(ModelBuilder modelBuilder)  

2.   {  

3.       modelBuilder  

4.        .Query().ToView("View_BlogPostCounts")  

5.        .Property(v => v.BlogName).HasColumnName("Name");  

6.  

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

1.   using (var db = new BloggingContext())  

2.   {  

3.       var postCounts = db.BlogPostCounts.ToList();  

4.       foreach (var postCount in postCounts)  

5.       {  

6.           Console.WriteLine($"{postCount.BlogName} has {postCount.PostCount} posts.");  

7.           Console.WriteLine();  

8.       }  

9.   }  

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

1.   public void ConfigureServices(IServiceCollection services)  

2.   {  

3.       services.AddDbContextPool(c => c.UseSqlServer(ConnectionString));  

4.  


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

Wrap Up

I hope in the new year, you find these EF Core features 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. 

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