Entity Framework Core Optimization

IT Tips & Insights: A Senior Software Engineer shares tips for improving your EntityFramework Core.

By Nicolas Horenstein, Senior Software Engineer

ORM is a tool that nowadays we can’t live without. For those who don’t know what ORM means, it’s a technique that lets you query and manipulate data from a database using an object-oriented paradigm. When talking about ORM, most people are referring to a library that implements the Object-Relational Mapping technique.

When working with C#, we can find some ORMs, but the most used are EntityFramewok (EF) and Dapper (among others). In another article, we can discuss the differences between those two ORMs, but right now let’s look at how we can optimize our EntityFramework.

Optimization

Most of the time when we talk about optimization we are talking about making things faster, but there are additional factors to consider:

• Pure database performance
• Network data transfer
• Network roundtrips
• EF runtime overhead

In this article, we’re going to focus on database performance and EF runtime overhead. The question is: Why should I be worried about EF performance? For some applications, performance isn’t necessarily critical, but in applications where it is, it’s vital that the developer understands what EF is doing for them, inspect outgoing SQL queries, follow roundtrips, etc.

Database Performance

So, for those who need to have a good performance database interaction, we need to focus on:

• Performance diagnosis
• Efficient querying
• Efficient updating
• Modeling performance
• Advanced performance topics

Performance Diagnosis

We need ways to detect if there are performance issues in your EF application. It is very important to carefully diagnose and investigate any problems before jumping to any conclusion.

One of the methods that’s often used is identifying slow database commands via logging. EF makes it very easy to capture command execution times, via either “simple logging” or “Microsoft.Extensions.Logging”.

For example:

When the logging level is set at LogLevel.Information, EF emits a log message for each command execution with the time taken. This command logging is very important because we can check if a query takes longer than expected. It can also reveal cases where unexpected database roundtrips are being made, which would show up as multiple commands where only one is expected.

As we know, there are various alternatives to EF’s logging feature for capturing command execution times. Databases typically come with their own tracing and performance analysis tools, which usually provide much richer, database-specific information beyond simple execution times. For example, if you are working with SQL Server, you can work with SQL Server Management Studio, which is a powerful client that can give us performance information. We can also use Azure Application Insights that provides powerful monitoring out of the box, integrating database performance and query execution times.

At some point, we need a way to know which way of writing or executing a query is faster than others, so writing a benchmark is strongly recommended — and best done using the “BenchmarDotNet” library.

Efficient Querying

Efficient querying is a vast subject that covers topics as wide-ranging as indexes, related entity loading strategies, and many others. Here are some key takeaways:

Use indexes properly: the main deciding factor of whether a query runs fast or not is whether it will properly utilize indexes where appropriate. A good way to spot indexing issues is to first pinpoint a slow query, and then examine its query plan via your database’s favorite tool.

EF Core makes it very easy to query out entity instances, and then use those instances in code. However querying entity instances can frequently pull back more data than necessary from your database, so consider only the properties you need.

By default, a query returns all rows that match its filter, so try to limit the result size.

Load related entities when possible. When dealing with related entities, we usually know in advance what we need to load. For example, if we need to load a certain set of blogs, along with their posts, it is always better to use eager loading in these scenarios so that EF can fetch all the required data in one roundtrip.

Beware of lazy loading. Sometimes you could generate a huge roundtrip in a simple loop, so choose if you will need eager loading or lazy loading.

EF tracks entity instances by default so that changes on them are detected and persisted when SaveChanges is called. In read-only scenarios where changes aren’t saved back to the database, the above overheads can be avoided by using no-tracking queries.

In some cases, using raw SQL can provide a substantial performance boost, and EF supports several ways to do this.

As a rule, for your application to be scalable, it’s important to always use asynchronous APIs rather than synchronous ones.

Efficient Updating

EF Core helps minimize roundtrips by automatically batching together all updates in a single roundtrip. For example:

We can see that two SQL INSERT statements and one UPDATE statement are sent to the database, rather than sending them one by one. Bulk updates are supported by Relational databases, which perform the entire operation in a single roundtrip. Unfortunately, EF doesn’t provide APIs for performing bulk updates. So, you can use raw SQL to perform the operation where performance is sensitive.

Modeling Performance

In many cases the way you model can have a profound impact on the performance of your application. Remember that it’s quite difficult to change your model once an application is running in production. For this scenario, you can work with denormalization and caching, stored computed columns, update cache columns when inputs change, materialized views, and inheritance mapping.

EF optimization is a vast subject, and sometimes quite difficult, but following the rules above will help you optimize all of your queries and roundtrips as much as possible. Thankfully, there are many web links that can help us with examples, theory, and good practices. The rest is up to you!

About

Hello everyone! I’m Nicolás Horenstein, an Information Systems Engineer from Córdoba, Argentina. Currently, I’m a Senior Software Engineer at Softensity. I’m a Fullstack developer, but my seniority comes from the Backend. I’ve been working with C# for almost 7 years, and I also love mobile development, which is why I work with Ionic, too. I’ve been working with C# and dotNet for the past few years and thought I’d share some tips for improving your EntityFramework Core. I hope it helps you!