LINQ To Entity Outer Join
Introduction...
Introduction
I was looking for a way to do a Outer Join between two tables with LINQ to Entity. But most of what I found was that DefaultIfEmpty() is not supported on LINQ to Entity. I'll show here a short example of how to get it to work. Using the code
In my Entity Model i have Image for Images, ImageProperty for images property values and ImagePropertyTypes for types of image properties. I want to create a list of all Property Types available and the property values for one image. I must therefore make an outer join otherwise I get only the Property Types image has and not all the Property Types. In SQL it looks like this
I was looking for a way to do a Outer Join between two tables with LINQ to Entity. But most of what I found was that DefaultIfEmpty() is not supported on LINQ to Entity. I'll show here a short example of how to get it to work. Using the code
In my Entity Model i have Image for Images, ImageProperty for images property values and ImagePropertyTypes for types of image properties. I want to create a list of all Property Types available and the property values for one image. I must therefore make an outer join otherwise I get only the Property Types image has and not all the Property Types. In SQL it looks like this
Select PT.Syscode,PT.Name,P.Value From ImagePropertyType PT Left outer Join ImageProperty P ON PT.Syscode = P.Syscode AND P.ImageId = 123456I would like to do something like this in LINQ but it does not work.
ImageEntities ent = new ImageEntities (); var properties = (from ImagePropertyType pt in ent.ImagePropertyType join ImageProperty p in ent.ImageProperty on pt.Syscode equals p.Syscode into temp from p in temp.DefaultIfEmpty() where p.ImageId == 123456 select new { pt.Syscode, pt.Name, p.Value }).ToList();I finally found a way to do this by creating an outer join with almost the same SQL code
ImageEntities ent = new ImageEntities (); var properties = (from ImagePropertyType pt in ent.ImagePropertyType select new { pt.Syscode, pt.Name, pt.ImageProperty.FirstOrDefault(q => q.ImageId == 123456).Value}).ToList();If I need more "columns" from ImageProperty and not get an overhead in SQL I need to create and return a Strong Type like this
public class PropertyList{ public PropertyList() public String Syscode { get; set; } public string Name { get; set; } public ImageProperty ImageProperty { get; set; } } And use it like this ImageEntities ent = new ImageEntities (); List<PropertyList> properties = (from ImagePropertyType pt in ent.ImagePropertyType select new PropertyList(){Syscode= pt.Syscode, Name= pt.Name, ImageProperty = pt.ImageProperty.FirstOrDefault(q => q.ImageId == 123456) });Conclusion What I do is that I use the relationship between objects in the Entity Model in the select part. pt.ImageProperty.FirstOrDefault(q => q.ImageId == 123456) The Select part can use FirstOrDefault() which creates my outer join and I also have the opportunity to filter my outer join.