Left Outer Join in Linq-To-Entities

Question

Is there a way to do a left outer join in linq-to-entities WITHOUT having tables mapped with foreign keys?

Also, if our company decides to stick with using linq-to-entities despite all of its current flaws, do you think it's important to get Visual Studio 2010? In other words, what is in Visual Studio 2010 that would help developing with entities that isn't in Visual Studio 2008?

Thanks.

Answer 1

Microsoft has two data teams that both aim toward the same goal: LINQ-to-SQL and ADO.NET entity. There support lies now with ADO.NET for Entities which means that LINQ-to-SQL won't be as high a priority as the ADO.NET offering.

Visual Studio 2010 has support for .NET 4 which in turn supports ADO.NET for entities and LINQ-to-SQL. Aside from the number of productivity features presents in VS2010, I've not seen much in terms of data support that's different from VS2008.

In terms of left outer join:

var dc = new DataContext();

var query = (
    from t1 in dc.MyTable
    join a in MyJoinedTable on p.Id equals a.Id into tempTable
    from t2 in tempTable.DefaultIfEmpty()
    select new { p.Column1, p.Column2, t2.Column1}
    );

Answer 2

This may not satisfy you because the objects are not properly flattened, but you can perform an outer join and return a collection of anonymous types.

A left outer join is the same as the union of the inner join and the except set (the part of the first set which does not join onto the second set).

Here I simply

• calculate the inner join
• calculate the except set (creating an empty B entity to ensure the types are consistent)
• combine the two sets.

The LINQ is not beautiful, but it is cute.

class A
{
    public int Id { get; set; }
    public string PropertyA { get; set; }        
}

class B
{
    public int Id { get; set; }
    public string PropertyB { get; set; }
}   

var aThings = new List<A>();
var bThings = new List<B>();

var innerJoin = aThings.SelectMany(a => 
    bThings.Where(b => a.Id == b.Id).Select(b => new { a, b })).ToList();

var exceptSet = aThings.Where(a => 
    !bThings.Select(b => b.Id).Contains(a.Id)).Select( a =>
    {
        B b = new B();
        return new { a, b };
     });

var outerJoin = innerJoin;
outerJoin.AddRange(exceptSet);

The result is a List of anonymous types {a, b}