Linq Acrobatics: How to flatten hierarical data models?

I use SQL like this to flatten hierarchical data. I just create a view and toss it on the EF diagram. However this doesn't fit the "Replace SQL Management Studio with LinqPad" mentality. How would I code these in Linq (and C#)? (Linq to Entities / Entity Framework 4)

Table A holds products and table B holds many kinds of categories. I want to select the category id as a single field in the view:

select A.*, B1.category as color, B2.category as size, B3.category as shape
fro开发者_JAVA技巧m   A left join B B1 on A.key = B1.key and B1.type = 1 -- Selects one B row
         left join B B2 on A.key = B2.key and B2.type = 2
         left join B B3 on A.key = B3.key and B3.type = 3

Better yet, is there a Linq pattern cookbook where you can look-up the SQL and see the Linq equivalent? I have already seen the 101 Linq examples in C#.

Unfortunately, there's neither an outer join in LINQ, nor can you add arbitrary join conditions. The inner join can be worked around using DefaultIfEmpty, but the Bn.type = n part of the join condition would need to be moved to a where condition.

The following produces exactly the SQL you provided, except for the type clauses I mentioned:

from A in products
join B1 in categories on A.key equals B1.key into tmp_color
join B2 in categories on A.key equals B2.key into tmp_size
join B3 in categories on A.key equals B3.key into tmp_shape
from B1 in tmp_color.DefaultIfEmpty()
from B2 in tmp_size.DefaultIfEmpty()
from B3 in tmp_shape.DefaultIfEmpty()
where B1.type == 1 && B2.type == 2 && B3.type == 3
select new { product = A, color = B1.category, size = B2.category, shape = B3.category };

results in

exec sp_executesql N'SELECT [t0].[key], [t1].[category] AS [color], [t2].[category] AS   [size], [t3].[category] AS [shape]
FROM [Product] AS [t0]
LEFT OUTER JOIN [Category] AS [t1] ON [t0].[key] = [t1].[key]
LEFT OUTER JOIN [Category] AS [t2] ON [t0].[key] = [t2].[key]
LEFT OUTER JOIN [Category] AS [t3] ON [t0].[key] = [t3].[key]
WHERE ([t1].[type] = @p0) AND ([t2].[type] = @p1) AND ([t3].[type] = @p2)',N'@p0 int,@p1 int,@p2 int',@p0=1,@p1=2,@p2=3

(Update: that's LINQ to SQL, just assuming that EF would be similar.)

Albin's answer is more readable, but probably produces less optimal SQL. For an exact match with your SQL, you need to replace FirstOrDefault with DefaultIfEmpty though (might make no difference, depending on your data). (Sorry, can't comment yet ;-))

I would go for a subselect approach.

from a in ModelEntities.A
select new
{
    f1 = a.f1,
    f2 = a.f2,
    // ...,
    fn = a.fn,
    color = ModelEntities.B.Where(b => a.key == b.key && b.type == 1)
                           .Select(b => b.category).FirstOrDefault(),
    size = ModelEntities.B.Where(b => a.key == b.key && b.type == 2)
                          .Select(b => b.category).FirstOrDefault(),
    shape = ModelEntities.B.Where(b => a.key == b.key && b.type == 3)
                           .Select(b => b.category).FirstOrDefault(),
}

But following the create a view habit you should probably create some fancy entity in the EF-designer that does something like this.