Optimize Your .NET EF Application

In today’s modern era, we always want things to be faster, better, and efficient. A user is not going to wait for you and this becomes a challenge to convince user’s needs and satisfy at a very high scale.

As an Engineer, whenever I develop any application, I have to set criteria to meet the standards. Every application has its standards like security, robustness, maintainability .e.t.c but after all of this, there is one, which sits at the top i.e PERFORMANCE.

Performance is a big concern as the data grows in today’s world.
So today, I am going to share a couple of EF performance tips through which I had benefited and now it’s your turn to make your application faster and better.

Entity Framework Performance:

Our community knows very well that the Entity Framework is working on deferred execution or lazy loading. Compare to writing your SQL to access the data, you can become phenomenal more productive by using Entity Framework (EF). Unfortunately, several traps that are easy to fall into have given it a reputation for poor performance.

Greedy About Rows

It is important to understand how EF hit the database for fetching the records. Let’s try to understand with an example :

List<Employee> employees= Db.Employees.ToList();
List<Employee> filterEmployees = employees
 .Where(x=>x.FirsName==“Alex”).ToList();

Let’s see how EF is going to hit it into the database using a profiler.

SQL Server Profiler

It would be far more efficient to let SQL Server do the filtering instead, and transfer fewer data, here you have to use the power of IQueryable and IEnumerable.

Instead of writing this, modify this way.

List<Employee> employees = Db.Employees.Where(x=>x.FirstName== "Alex");

Or this :

IQueryable Employees = Db.Employees;
List<Employee> FilterEmployees = Employees.Where (x=>x.FirstName== "Alex");

SQL Server Profiler

As you can see in the above picture, we filter the data at the Db level and fetches the required records only.

The conclusion is, hit the database with all the filters so that the number of records, transfer of bytes, and computation of data will be less.

Needed Columns Only

Let’s take an example if you want to print Name for selected Employee. We can write it like:

int employeeId=1234;
List<Employeee> employee = Db.Employees.Where 
(x=> x.EmployeeId == employeeId);

Let’s see the profiler:

SQL Server Profiler

As you can see that it selects all the columns of the employee which is not necessary, you have to make entity framework understand that what all columns are required for you.

You can resolve by writing the same thing in this way as

int employeeId=1234; 
List<Employee> employees = Db.Employees.Where(x=>x.EmployeeId == employeeId).Select(x=> new EmployeeDTO()
{
   FirstName = x.Firstname,
   LastName = x.Lastname
}).ToList();

Here is the profiler query :

It reduces the load from the system and fetches only relevant records that you need.

Navigation make you suffers a lot

Initially, engineers were mentioning that EF optimizes the query by itself but it is not true and makes you suffer a lot. As you might hear that lazy loading or deferred execution is good for loading the records but in general, the application is working with navigation property a lot and EF cannot understand by itself, which navigation properties you needed and therefore does not load.

Believe me or not, but 70% of the developer’s community has been facing this problem a lot.

But once you hit the navigation property, it will hit the table and fetches all the records and respectively the loading time of the page will increase. Let’s see in action.

List<Employee> employee = Db.Employee.ToList();
List<EmployeeStatus> EmployeeStatus= employee.Select (x=>x.EmployeeStatus);

As you can see, the line in the red block is fetching the records from the employee table and all other records in green block fetching records from the Employee Status table. As EmployeeStatus table has only 4 records, therefore, it hits 4 times, if it has n records then it will hit n number of times.

Now, this leads to the N+1 Problem of Entity Framework and this is one of the problems of AutoMapper when you are mapping the DB entities into DTO.

How we can resolve this issue?

Here’s your intelligence required at par. This has to decide by the engineers what data they want to present to the user and accordingly, they can load it using an Eager Loading Method.

Include is the option that uses an Eager Loading mechanism to load the data. Let’s see How?

List<Employee> employee = Db.Employee.Include("EmployeeStatus").ToList();
List<EmployeeStatus> EmployeeStatus= employee.Select (x=>x.EmployeeStatus);

As you can see, the profiler has only one query.

Here is the result. Through Include, EF understands that the user wants to make outer join with the resultant set of data and load this navigation property beforehand.

I hope this blog will help you in improving the performance of EF. In my next blog, I am gonna share tips for the Async Partial Views which will give you more insights at the application level.

…. Your claps are the token of Appreciation.