Search

8 Tips and Tricks for Writing the Best Queries in LINQ to Entities

LINQ is a powerful querying tool for .NET applications. There are certain techniques to follow when writing queries to make sure they run quickly and effectively. The following are a few things to consider when aiming to improve the performance of LINQ to Entities:

  • Pull only the needed columns

  • Use of IQueryable and Skip/Take

  • Use of left join and inner join at the right places

  • Use of AsNoTracking()

  • Bulk data insert

  • Use of async operations in entities

  • Look for parameter mismatches

  • Check SQL query submitted to the database


Pull only the needed columns

When working with LINQ, only pull the needed columns in the Select clause instead of loading all the columns in the table.


Consider the following LINQ query.

using (varcontext=newLINQEntities())
{
    var fileCollection = context.FileRepository.Where(a=> a.IsDeleted == false).ToList();
}

This query will be compiled into SQL as shown in the following screenshot.

Query Compiled into SQL


Though we might need only a few columns, we have loaded them all. This consumes more memory than necessary and slows down query execution. We can change the query as follows for better execution.

<