Introduction
LINQ to Entity is a great ORM for querying and managing databases. There are some points that we should consider while designing and querying databases using the Entity Framework ORM.
Disable change tracking for entity if not needed
In the Entity Framework, the context is responsible for tracking changes in entity objects. Whenever we are only reading data, there is no need to track the entity object. We can disable entity tracking using the MergeOption, as in:
AdventureWorksEntities e = new AdventureWorksEntities();
e.EmployeeMasters.MergeOption = System.Data.Objects.MergeOption.NoTracking;
Use Pre-Generating Views to reduce response time for the first request
While working with Entity Framework, view generation may take longer for a large and complicated model. We can use a pre-generated view to save run time cost to generate a view for the first request of a page. A Model Generated view helps us to improve start-up performance at run time.
Avoid fetching all the fields if not required
Avoid fetching all fields from the database that are not really required. For example we have an EmployeeMaster table and for some operation I require only the EmployeeCode field so the query must be:
AdventureWorksEntities e = new AdventureWorksEntities();
string code = e.EmployeeMasters.Where(g => g.EmployeeId == 1000)
.Select(s => s.EmployeeCode).FirstOrDefault();
Retrieve only required number of records (rows)
Do not collect all data while binding data to a data grid. For example if we have a data grid on a page and we only show 10 records on the screen, so do not fetch all records from the database, as in:
AdventureWorksEntities e = new AdventureWorksEntities();
int pageSize = 30, startingPageIndex = 3;
List<EmployeeMaster> lstemp = e.EmployeeMasters
.Take(pageSize)
.Skip(startingPageIndex * pageSize).ToList();
Avoid using Contains
Avoid use of the contain keyword with a LINQ - Entity Query. While Entity Framework generates equivalent SQL, the contain is converted in the "WHERE IN" clause. The "IN" clause has performance issues.
Avoid using Views
Views degrade the LINQ query performance. So avoid using views in LINQ to Entities.
Use Compiled Query
If you are using Entity Framework 4.1 and below, you must use a compiled query. The Compiled Query class provides compilation and caching of queries for reuse. The Entity Framework 5.0 supports a new feature called Auto-Compiled LINQ Queries. With EF 5.0, LINQ to Entity queries are compiled automatically and placed in EF's query cache, when executed.
static Func<AdventureWorksEntities, IQueryable<EmployeeDetail>> getEmpList = (
CompiledQuery.Compile((AdventureWorksEntities db) =>
db.EmployeeDetails
));
AdventureWorksEntities e = new AdventureWorksEntities1();
List<EmployeeDetail> empList = getEmpList(e).ToList();
Must examine Queries before being sent to the Database
When we are investigating performance issues, sometimes it's helpful to know the exact SQL command text that the Entity Framework is sending to the database. I would suggest that we must examine each and every LINQ to Entity queries to avoiding performance issues.
Efficiently Loading Related Data
Entity Framework supports the loading of related data. The Include method helps us load relevant data. Use of the Include method ensures it is really required for the page i.e. do not include a navigation property whenever it is not really required.
Select appropriate Loading type while defining model
Entity Framework supports three ways to load related data: eager loading, lazy loading and explicit loading. Eager loading is the process in which a query of one type of entity also loads related entities as part of the query. Eager loading is done by use of the Include method. Lazy loading is the process in which an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed. When lazy loading disabled, it is possible to lazily load related entities with use of the Load method on the related entity's entry. It is called explicit loading. So depending upon on the type of application and requirement we can select appropriate Loading type for the entity model.
Conclusion
Using the above definition tips, we can definitely improve the performance of LINQ to Entity Queries.