Left outer join through Linq To Entities through Entity Framework with Where clause

Question

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

For the two tables 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 next SQL statement. Note that the WHERE clause contains two OR'd clauses that 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 generate is Linq for Entities through the Entity Framework and will (hopefully) generate a single SQL statement that will be executed on the server. Linq to Entities does not support the FirstOrDefault () extension clause, so the standard Linq syntax for the LEFT OUTER JOIN will not work.

Here is the solution that I have SO FAR, but I can not do one of the following:

1) Create a result set containing a set of Foo / Bar combinations that will 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();