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.
selectclause 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.
whereclause—at the database. If you use the
IQueryable, with a lambda expression that calls C# methods, the query will be performed on the
IEnumerablereturned from the database.
Perform sorting after filtering. This can be done using the orderby clause, but also using
IQueryablewith a lambda expression.
Restrict the number of records returned from the database to some maximum value—1000 works fine—using the
Pagination can be used to further reduce the data returned, using
IQueryableto skip records you don’t need, followed by
Takemethod to pick the records you do need.
If you want to use string value of an
Enumin a query, use
Enumobject. 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.