65.9K
CodeProject is changing. Read more.
Home

LINQ To Entity Outer Join

starIconstarIconstarIconstarIconstarIcon

5.00/5 (4 votes)

Apr 22, 2010

CPOL

1 min read

viewsIcon

29060

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
Select PT.Syscode,PT.Name,P.Value
From ImagePropertyType PT
Left outer Join ImageProperty P ON PT.Syscode = P.Syscode AND P.ImageId = 123456
I 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.