Click here to Skip to main content
15,867,994 members
Articles / Programming Languages / C#
Tip/Trick

LINQ To Entity Outer Join

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
21 Apr 2010CPOL1 min read 28.7K   2  
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Sigma IT & Management
Sweden Sweden
Working with web application development since 1999.
Developer of the CMS product Publech (www.publech.com) and a large range of other Publech modules.

Involved in developing the largest non commercial website in Sweden the Swedish Public Employment Service (www.ams.se)

Comments and Discussions

 
-- There are no messages in this forum --