I've been dabbling with LINQ recently, specifically within the ADO.NET Entity framework. And I'm not ashamed to admit, it's been kicking my butt up and down the proverbial technology block. Here's a sampling of what I've picked up in the hopes of saving some poor other programming soul some time and headache:
-
When trying to achieve a left outer join, disregard the numerous postings instructing you to utilize the 'DefaultIfEmpty()' method, since that will not work for LINQ to Entity queries (in .NET 3.5 SP1). Upon compilation, you'll see something to the effect of:
System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[AllStar.DataV2.DataModel.Object] DefaultIfEmpty[Object](System.Collections.Generic.IEnumerable`1[AllStar.DataV2.DataModel.Object])' method, and this method cannot be translated into a store expression..
All LINQ to Entity group joins (the ones with the 'in group' clause at the end) perform a left outer join by default: http://msdn.microsoft.com/en-us/library/bb399367.aspx. (In C#, the syntax for group join is simply 'join').
Here's an example query that should generate a left outer join if you're using LINQ to Entity:
var query = from s in db.Studies
join sa in db.StudyAccess on s.Id equals sa.StudyId into temp
where (s.Studyname.ToLower() == studyname.ToLower())
select new { s.Id, a.Access };
*NOTE: Make sure you include the 'into [group]' portion or it will NOT complete a left outer join*
-
When trying to perform MULTIPLE left outer joins using LINQ to entity, you'll have to nest the query-groups in order to maintain context (thank-you David Buchanan). Here's what I mean:
var query = from po in allStarNetEntities.PurchaseOrder
join pl2 in
(from pl in allStarNetEntities.POProductLink
join pd in allStarNetEntities.POProducts on pl.POProducts equals pd
into POGroup
select pl)
on po equals pl2.PurchaseOrder into POGroup
where po.Requestor == criteria.SearchText
select po;