Improve SQL queries while using .NET Language-Integrated Query (LINQ)
1. Use IQueryable instead of IEnumerable interface while querying. While using IQueryable, we need to use the ToList() to get the results from DB. Do not use ToList() until you know the final output of your query. With IQueryable, query does not run against DB until we use ToList() function. This will be helpful in case suppose we have a table with 20 columns but we only need two columns in the output but we did not know at the time while writing the LINQ function then we can write it with IQueryable and will select only two columns in our final call and then we will use ToList But if we return the IEnumerable list from the LINQ function that means it already ran the query against the Db and selecting two columns afterwards will not make any difference (It will not improve our DB SQL call).
2. We can write single function which returns the IQueryable type of table and we can use that in our code and make different type of inline queries from that function before using ToList. Use both select and where clause before ToList. In select, specify only necessary columns instead of getting all the columns.
3. Use dbcontext executeQuery methods for updating one or two columns instead of saving entire table object.
4. While using Iqueryable interface, we cannot perform inbuilt functions on the columns of table like ToString() or Math functions against the database. We can only perform such functions if we already executed the query against the db, means after ToList function otherwise exception will be thrown.
5. While using repositories, use them wisely. Always call the repository methods by creating the repository instance with Using statement Instead of directly calling as using disposes off the repository object after use and creates only one such object but everytime you call directly new object gets created.
Using (var repo = new DemoRepository()){
repo.Get();
}
Instead of
new DemoRepository().Get();