How can you optimize LINQ queries for better performance?

1 Avoid unnecessary iterations

One of the most common mistakes when using LINQ queries is to iterate over the same data source multiple times, either by calling methods like Count, Any, or First, or by using foreach loops. This can cause a significant overhead, especially if the data source is large or remote. To avoid this, you can use the ToList or ToArray methods to materialize the query results into a collection, and then reuse that collection for further operations. For example, instead of writing:

var customers = db.Customers.Where(c => c.Age > 30);
var count = customers.Count();
var first = customers.First();
var last = customers.Last();        

You can write:

var customers = db.Customers.Where(c => c.Age > 30).ToList();
var count = customers.Count;
var first = customers[0];
var last = customers[^1];        

This way, you only query the database once, and then access the cached results in memory.

2 Choose the right operators

Another important aspect of optimizing LINQ queries is to choose the right operators for your scenario. For example, if you only need to check if a sequence contains any elements, you should use the Any method instead of the Count method, as the latter will iterate over the entire sequence, while the former will stop at the first element. Similarly, if you only need to access the first or last element of a sequence, you should use the First or Last method instead of the ElementAt method, as the latter will iterate over the sequence until it reaches the index, while the former will return the element immediately. For example, instead of writing:

var numbers = Enumerable.Range(1, 100);
var hasEven = numbers.Count(n => n % 2 == 0) > 0;
var tenth = numbers.ElementAt(9);        

You can write:

var numbers = Enumerable.Range(1, 100);
var hasEven = numbers.Any(n => n % 2 == 0);
var tenth = numbers.First();        

This way, you reduce the number of iterations and improve the performance of your queries.

3 Defer execution

One of the benefits of LINQ queries is their lazy execution, which allows for deferred evaluation until they are needed and composing with other queries. However, this also means that caution must be taken when and how they are executed, as they may be affected by changes in the data source or by repeated evaluations. To control the execution of queries, one can use methods such as ToList, ToArray, ToDictionary, or ToLookup to execute a query immediately and cache the results. AsEnumerable or AsQueryable can be used to execute a query only once and cache the results. Finally, AsParallel or AsSequential can be used to execute a query every time the results are accessed, allowing for parallel processing or sequential ordering depending on needs. For example, if one wants to speed up a query performing heavy computation on a large sequence of numbers using multiple threads, they can write: var numbers = Enumerable.Range(1, 1000000); var squares = numbers.AsParallel().Select(n => n * n); This way, the query is executed in parallel and results are obtained as they are iterated over.

4 Use compiled queries

The last tip on how to optimize LINQ queries for better performance is to use compiled queries. Compiled queries are a feature of LINQ to SQL and LINQ to Entities that allow you to precompile your query expressions into reusable delegates, and then execute them with different parameters. This can improve the performance of your queries by reducing the overhead of parsing and translating the query expressions into SQL statements. To use compiled queries, you need to use the CompiledQuery class and its static methods, such as Compile or CompileAsync. For example, if you have a query that returns a list of customers by their age, you can write:

var query = CompiledQuery.Compile((DataContext db, int age) => db.Customers.Where(c => c.Age == age));
var customers = query(db, 30);        

This way, you compile the query once, and then execute it with different parameters, without recompiling it every time.

5 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?