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 where, group, orderby, or select clauses.
Databases cache query plans—the first request may take longer, but subsequent requests are much faster.
Use the 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.
Perform filtering—using 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 IQueryable.
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.