Tips to improve Entity Framework Performance

Smart tips to improve Entity Framework Performance: An Overview

LINQ to Entity is a great ORM for querying and managing databases. It offers a lot of things, so it is mandatory to know about the performance of it. These are right up to a certain point as LINQ comes with its penalties. There are some tips and tricks that we should keep in mind while designing and querying databases using the entity framework ORM.

Stretching the reach of these framework-related concepts, you can go through the Entity Framework tutorial It will help you in your journey of being a quality programmer. Well, here are some smart tips that I would like to share with you for better performance of Entity Framework.

Avoid to put all the DB Objects into One Single Entity Model

Entity Model specifies a single unit of work, not all of our database. If we have many database objects that are not connected or these(log tables, objects used by batch processes, etc.) are not used at all. Hence these objects are consuming space in the memory and cause performance degrades. So try to make separate entity models of related database objects.

Disable change tracking for the entity if not needed

Whenever you retrieve the data only for reading purposes, not for modification then there is no need for object tracking. So disable object tracking by using the Merge Option as below:

NorthwindDataContext context = new NorthwindDataContext() context.tblCities.MergeOption = MergeOption.NoTracking;

This option allows us to turn off the object cache and unnecessary identity management of the objects.

Use Pre-Generating Views to reduce response time for the first request

When the object of ObjectContext is created for the first time in the application, the entity framework creates a set of classes that are required to access the database. This set of classes is called view and if your data model is large then creating the view may delay the web application's response to the first request for a page. We can reduce this response time by creating a view at compile time by using the T4 template or EdmGen.exe command-line tool.

Avoid fetching all the fields if not required

Avoid fetching not-required fields from the database. Suppose I have a table of Customers with 20 fields and I am interested only in three fields - CustomerID, Name, and Address then fetch only these three fields instead of fetching all the fields of the Customer table.

 //Bad Practice
var customer =
 (from cust in dataContext.Customers
 select cust).ToList();
 //Good Practice
var customer =
 (from cust in dataContext.Customers
 select new {
 customer. CustomerID,
 customer.Name,
 customer.Address
 }). ToList (); 

Choose the appropriate Collection for data manipulation

In Linq, we have Var, IEnumerable, IQueryable, and IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collections for data manipulation. For learning differences among all these collections refer to our articles IEnumerable VS IQueryable, IEnumerable VS IList, and Var VS IEnumerable.

Use Compiled Query wherever needed

Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow at first time but boosts the performance significantly. We use the Compile method of the CompiledQuery class for making compiled queries.

Suppose you are required to retrieve customers' details again and again based on the city then make this query compiled query like as

// create the entity object
NorthwindEntities mobjentity = new NorthwindEntities();
 //Simple Query
IQueryable lstCus = from customer in mobjentity.tblCustomers
where customer.City == "Delhi"
select customer;
 //Compiled Query
Func> compiledQuery
= CompiledQuery.Compile>(
(ctx, city) =>from customer in ctx.Customers
where customer.City == city
select customer); 

In the above query, we are passing the string parameter city for filtering the records. For more about anonymous method.

Retrieve only the required number of records

When we are binding data to the grid or doing paging, retrieve only the required no of records to improve performance. This can be achieved by using the Take, While, and Skip methods.

 // create the entity object
NorthwindEntities mobjentity = new NorthwindEntities();
int pageSize=10,startingPageIndex=2;
List lstCus = mobjentity.tblCustomers.Take(pageSize)
 .Skip(startingPageIndex * pageSize)
 .ToList(); 

Avoid using Contains

In LINQ, we use the contains method for checking existence. It is converted to "WHERE IN" in SQL which causes performance degradation.

Avoid using Views

Views degrade the LINQ query performance costly. These are slow in performance and impact the performance greatly. So avoid using views in LINQ to Entities.

Debug and Optimize LINQ Query

If you want to debug and optimize your query then LINQ Pad is a great tool for this purpose. I am a big fan of LINQ Pad. It is very useful for query construction, debugging, and optimization.

IQueryable lstCus = from customer in mobjentity.tblCustomers
where customer.City == "Delhi"
select customer;
lstCus.Dump(); 

The dump method of LINQ Pad gives the result of the above query in the result window.

Summary

I hope you will enjoy these tips and tricks while programming with LINQ to Entity. I would like to have feedback from my blog readers. Your valuable feedback, questions, or comments about this article are always welcome. For extra learning, you can go through our Entity Framework 6 tutorial. Enjoy coding..!