A blog by Devendra Tewari
These are some lessons I learned when improving query performance of an application written in C#, that uses Entity Framework Code First with SQL Server. A LINQ to SQL query is an
IQueryable, and can be reused when two queries are quite similar but differ somewhat in the
Databases cache query plans—the first request may take longer, but subsequent requests are much faster.
select clause to return just the data you need. Returning whole objects, and data you don’t need, translates to a more time-consuming remote procedure call (RPC) to the database.
where clause—at the database. If you use the
Where method of
IQueryable, with a lambda expression that calls C# methods, the query will be performed on the
IEnumerable returned from the database.
Perform sorting after filtering. This can be done using the orderby clause, but also using
OrderBy method of
IQueryable with a lambda expression.
Restrict the number of records returned from the database to some maximum value—1000 works fine—using the
Take method of
Pagination can be used to further reduce the data returned, using
Skip method of
IQueryable to skip records you don’t need, followed by
Take method to pick the records you do need.
If you want to use string value of an
Enum in a query, use
ToString() method on
Enum object. LINQ to SQL sends string values of Enum to the database, hence the query suffers no significant performance issues.
Also see Performance Considerations for additional insights.