LINQ Performance Optimization Tips & Tricks

Apr 15 2024


Many thanks to the sponsors who make it possible for this newsletter to be free for readers.


• POST/CON, the largest API conference by Postman, is scheduled for April 30 to May 1. It's designed for anyone involved with APIs or businesses dependent on APIs.


Check it out here.




LINQ (Language Integrated Query) is a powerful feature in C# that lets developers perform complex queries easily across various data sources like databases, collections, and XML.


However, while LINQ makes coding simpler and more intuitive, it can also slow down your application if not used carefully.


In this article, we'll look at practical tips and best practices to optimize LINQ queries, helping you enhance performance and efficiency in your C# applications.


Let's dive into how to get the most out of LINQ without compromising on speed or resource usage.



How LINQ Execution works?


There are two main execution modes for LINQ queries: deferred (or lazy) execution and immediate execution. Knowing the difference between these is key to making your LINQ queries more efficient.


Deferred Execution


This mode doesn't execute the query when it's initially defined. Instead, the execution is delayed until the data is actually accessed for the first time.


Here’s how it works:

var fruits = new List<string> { "apple", "banana", "cherry", "date", "elderberry" };

var longFruits = fruits.Where(fruit => fruit.Length > 5); // No execution here
foreach (var fruit in longFruits) // Execution happens here


Immediate Execution


In this mode, the query is executed as soon as it's created. Methods such as ToList(), ToArray(), Count(), and First() trigger immediate execution in LINQ.


Here’s how it works:

var fruits = new List<string> { "apple", "banana", "cherry", "date", "elderberry" };

var firstFruit = fruits.Where(fruit => fruit.Length > 5).First(); // Execution happens here


Common Performance Mistakes in LINQ


1. Using Count() method improperly


Using LINQ methods incorrectly can lead to slow performance, especially when checking if a sequence has elements.


You might think of using the Count() method to do this, but that's not efficient because it counts all elements in the sequence, which can take a long time if the sequence is large.

var numbers = new List<int> { 1, 2, 3, 4, 5 };
if (numbers.Count() > 0)
    // Do something


Instead, you should use the Any() method. Any() is faster because it stops looking as soon as it finds the first element.

var numbers = new List<int> { 1, 2, 3, 4, 5 };
if (numbers.Any())
    // do something


The examples shown are collections of type List. If a materialized list is used, there is an option to use the .Count property as well.


2. 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.


3. Select projection on all fields


Select is the most frequently used method in LINQ. And what I often see people doing is extracting the complete list of fields, more precisely the entire object even though they only need 2.3 fields.

var validUsers = users.Where(x => x.IsValid).Select(x => x);


Instead, it is necessary to design only the fields that are necessary:

var validUsers = users.Where(x => x.IsValid).Select(x => new { x.Id, x.Name });


4. Ignoring IQueryable vs IEnumerable


Confusing IQueryable and IEnumerable can lead to inefficient queries.


IQueryable is intended for querying data sources like databases, allowing for query translations and optimizations (like SQL generation).


IEnumerable is used for querying in-memory collections. Using IEnumerable when IQueryable is appropriate can pull more data into memory than necessary.

// Make sure to use IQueryable for database queries to leverage SQL optimizations
IQueryable<Product> query = dbContext.Products.Where(p => p.Price > 100);


5. Not using 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.


Wrapping up


LINQ is a critical component of the .NET framework, offering a robust, readable, and flexible method for querying and managing data.


Like any potent tool, its effectiveness depends significantly on how it's used.To fully benefit from LINQ, it's crucial to grasp its execution modes, steer clear of typical performance issues, adhere to established best practices, and make use of profiling tools to refine your queries.


This will help enhance the performance of your .NET applications significantly.
Optimization should be seen as an ongoing effort rather than a one-off task.


Keep exploring ways to fine-tune your code, remain engaged in learning new techniques, and enjoy the process of coding!


That's all from me today.

Join 10,950+ subscribers to improve your .NET Knowledge.

There are 3 ways I can help you:

Design Patterns Simplified ebook

Go-to resource for understanding the core concepts of design patterns without the overwhelming complexity. In this concise and affordable ebook, I've distilled the essence of design patterns into an easy-to-digest format. It is a Beginner level. Check out it here.


Promote yourself to 10,950+ subscribers by sponsoring this newsletter.

Join .NET Pro Weekly Newsletter

Every Monday morning, I share 1 actionable tip on C#, .NET & Arcitecture topic, that you can use right away.

Subscribe to
.NET Pro Weekly

Subscribe to the .NET Pro Weekly and be among the 10,950+ subscribers gaining practical tips and resources to enhance your .NET expertise.