Left Outer Join via Linq To Entities via Entity Framework with Where clause

Question

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.

SELECT DISTINCT
    Foo.*
FROM
    Foo
    LEFT OUTER JOIN Bar ON Foo.FooId = Bar.FooId
WHERE
    (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.

EulerOperator

Answer 1

.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();