Outer left Entity Framework through Linq To Entities using Where Clause

entity-framework left-join linq-to-entities outer-join


I have read all the posts related to implementing the equivalent of a LEFT OUTER JOIN in Linq to Entities (.NET 3.5) when using the Entity Framework, but have yet to find a solution to the following problem.

Given two tables, represented by the objects below:

    public class Foo
        public int FooId;  // primary key
        public string Name;

    public class Bar
        public int BarId;  // primary key
        public int FooId;  // foreign key
        public string Desc;

I need to create a Linq to Entities statement which is the EQUIVALENT of the following SQL statement. Note that the WHERE statement contains two OR'd conditions which span both tables, and the use of the DISTINCT qualifier.

    LEFT OUTER JOIN Bar ON Foo.FooId = Bar.FooId
    (Foo.Name = 'fooname' OR Bar.Desc = 'bardesc')

The Linq query that I am generating is Linq to Entities via the Entity Framework, and will (hopefully) generate a single SQL statement to be executed on the server. Linq to Entities does not support the FirstOrDefault() extension clause, so the standard Linq syntax for LEFT OUTER JOINs will not work.

Here is the solution that I have SO FAR, but I am unable to do either of the following:

1) Generate a result set which contains the set of Foo/Bar combinations that would be returned by the LEFT OUTER JOIN operation.

2) Implement the equivalent of the WHERE clause: WHERE (Foo.Name = 'fooname' OR Bar.Desc = 'bardesc')

    private class JoinSet
        public Foo Foo;
        public IQueryable<Bar> Bars;

    private class FooBar
        public Foo Foo;
        public Bar Bar;

    IEnumerable<Foo> OuterJoinTest()
        IQueryable<Foo> fooBaseQuery = dbContext.FooSet;
        IQueryable<Bar> barBaseQuery = dbDontext.BarSet;

        IQueryable<JoinSet> joinQuery =
            from foo in fooBaseQuery
            select new JoinSet
                        Foo = foo,
                        Bars = barBaseQuery.Where(bar => bar.FooId == foo.FooId)

        // How do I generate a result set containing FooBar objects ?

        // How or where do I insert the equivalent of: ?
        //  WHERE (Foo.Name = 'fooname' OR Bar.Description = 'bardesc')

        IQueryable<Foo> resultQuery =
            from joinSet in joinQuery
            select joinSet.Foo;

        resultQuery = resultQuery.Distinct();

        return resultQuery.ToList();

Any help, ideas or suggestions would be appreciated.


3/22/2011 5:58:23 PM

Popular Answer

.NET 3.5

private class FooBar
   public Foo Foo { get; set; }
   public Bar? Bar { get; set; }

var innerQuery = from foo in context.Foos
                 from bar in context.Bars
                 where foo.Name == 'fooname' || bar.Description == 'bardesc'
                 where foo.FooId == bar.FooId
                 select new FooBar { Foo = foo, Bar = bar };

var outerQuery = from foo in context.Foos
                 where foo.Name == 'fooname' 
                 where !context.Bars.Any(b => b.FooId == foo.FooId)
                 select new FooBar { Foo = foo, Bar = null };

var leftouterjoinQuery = innerQuery.Union(outerQuery).Distinct();

.NET 4.0

var query = (from foo in context.Foo
            join b in context.Bar
            on foo.FooId equals b.FooId into Bar
            from bar in Bar.DefaultIfEmpty()
            where foo.Name = 'fooname' || bar.Description = 'bardesc'
            select new { foo, bar }).Distinct();
6/22/2012 1:53:33 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow