FirstOrDefault() with DynamicSqlDataProvider

Topics: Troubleshooting
Aug 11, 2011 at 2:03 PM

I have a strange error occuring that I thought would be worth mentioning.  I have just converted my project to using the DynamicSqlDataProvider and had the following scenario break.

	         public List<FatalityWithTagInfoModel> GetFatalitiesForYear(int year)
		{
			var fatalities = this.Connection.Get<Fatalities>().Where(w => w.Year == year);
			fatalities = this.ScopeFatalities(fatalities);

			//Works
			IIHS.Lists.Tags tag = Connection.Get<IIHS.Lists.Tags>().FirstOrDefault();

			List<FatalityWithTagInfoModel> result;			
			try
			{
				result = fatalities.Select(s => new FatalityWithTagInfoModel { Fatality = s, Tag = Connection.Get<IIHS.Lists.Tags>().Where(w => w.Id == s.MainTagId).FirstOrDefault() }).ToList();
			}
			catch (Exception ex)
			{
				//Throws 
				//"Could not convert from type 'System.Collections.Generic.List`1[Composite.Data.GeneratedTypes.DynamicSqlDataProvider.TagsIIHS_Lists_Tags_PublishedEntity]' to type 'IIHS.Lists.Tags'."
				LoggingService.LogVerbose("GetFatalitiesForYear", ex.ToString());
			}
			
			//Works
			result = fatalities.Select(s => new FatalityWithTagInfoModel { Fatality = s, Tag = Connection.Get<IIHS.Lists.Tags>().Where(w => w.Id == s.MainTagId).FirstOrDefault() as Tags}).ToList();

			return result;
		}

You can see that there are three example calls to FirstOrDefault(). The first FirstOrDefault() worked fine as expected. The second one failed not as expected, but in the third scenario where I typed it to the Tags interface as a workaround it succeeded (Tag ends up with the proper value not null). The code that now throws an exception functioned fine with the Xml provider. I thought this might be a bug in the Linq implementation of the generated sql entities and therefore worth mentioning.

Thanks, Peter

Aug 11, 2011 at 6:16 PM

Hi Peter


I'll try to debug the sample a little bit later, but for now I can say that likely it works, but not the way it should. What happends is - linq2sql cannot build a query out of your original linq construction:


fatalities.Select(s => new FatalityWithTagInfoModel { Fatality = s, Tag = Connection.Get<IIHS.Lists.Tags>().Where(w => w.Id == s.MainTagId).FirstOrDefault() })


When you added a type cast operator "as Tags", it didn't made the code


Connection.Get<IIHS.Lists.Tags>().Where(w => w.Id == s.MainTagId).FirstOrDefault()


a part of result SQL, but rather produced a separate query. So the SQL would look like:


select * from fatalities where ....


and then for each fataliy it will do a separate query to Tags table


select * from tags where id = ....


So it works, but performance is likely not that good

The optimal way would be to write something like

from fatality in fatalities
from tag in Connection.Get<IIHS.Lists.Tags>()
where w => tag.Id == fatality.MainTagId
select new FatalityWithTagInfoModel { Fatality = fatality, Tag = tag }

 


this way it will produce just one query like

select fatalities.xxx, 

     tags.xxxx

from Fatalities as [fatalities]

from Tags as [tags]

where

  (fatalities.TagId = tags.Id)

 and [fatalities.Year = @Year]

 and ......

 

The query complexity will still be the same ( ~O(n*n)), but since you won't have many round trips to sql server, the performance will be a couple of times better.

Aug 11, 2011 at 6:43 PM

I see.  Thanks for the advice.