Entity Framework tips

Tips

This article will be updated ongoing bases.


Have problem with getting information when exception has been thrown in your query result?

use following line:

when you have
var query = context.MyTable.Where(x => x.Id == RequestId)

var errorMessage = string.Format("Message:{0}", ((System.Data.Objects.ObjectQuery)query).ToTraceString()));

Normalisation of query using EF

 We all like IQueryable as it does not fetch data until we need to access data or starting to loop through records. I have been trying to make EF to create as few requests to the database as possible due the cost of getting data. Take example here: 
 
 var distinctRecords = sites.Select(y => y.Name).Distinct().ToList();

var loadedFullRecords = context.tblMasters.Where(x => distinctRecords.Contains(x.Name));

// loop and assign values
foreach (var site in sites.Distinct())
{
 var item  = loadedFullRecords.FirstOrDefault(x => x.siteName == site.Name);
 // rest of code
}


Getting the distinct records and loading all items about the record based on name. Great I got the list and it cost me one trip to the db. Next I itterate through distinct sites and getting details from loaded fill records and using FirstOrDefault as in case that database has been updated since, I got at least null object.
Now item should be loaded and I would expect to get all records be down in my loadedFullRecords colection, so I fire up SQL Server Profiler and run the query. To my suprise I have about 50 queries agains database.

So lets dig deeper.

I have read my code again, and realised that my object loadedFullRecords is still IQueryable untill I loop  and get data. And solution to the query is below.

Simple fix is : Materialise the collection using ToList()!!

 var distinctRecords = sites.Select(y => y.Name).Distinct().ToList();

var loadedFullRecords = context.tblMasters.Where(x => distinctRecords.Contains(x.Name)).ToList();

// loop and assign values
foreach (var site in sites.Distinct())
{
 var item  = loadedFullRecords.FirstOrDefault(x => x.siteName == site.Name);
 // rest of code
}