使用lambda表达式的多个左外连接

I have an SQL Query to do with Lambda Expressions like this, generally with more joins than in this example.

我有一个SQL查询与这样的Lambda表达式有关,通常有比这个例子更多的连接。

select Table2.a,
          Table2.b,
          Table2.c,
          Table2.d
   from Table1
     LEFT OUTER JOIN Table2
     ON Table2.a = Table1.a and
        Table2.b = Table1.b and
        Table2.c = Table1.c 
     LEFT OUTER JOIN Table3
     ON Table3.b = Table1.b AND
        Table3.c = Table1.c AND
        Table3.d = Table1.d 
   where ( Table1.a = ValueA )
   order by Table3.f

I'm doing this with Join() Lambda Expression, but i see in SQL Server profiler that this generate an INNER JOIN and i need a LEFT OUTER JOIN.

我正在使用Join()Lambda Expression执行此操作,但我在SQL Server探查器中看到这会生成一个INNER JOIN,我需要一个LEFT OUTER JOIN。

This is how i'm doing it with Join()

这是我用Join()做的

var RS = DBContext.Table1.Join(DBContext.Table2,
  Table1 => new {Table1.a, Table1.b, Table1.c},
  Table2 => new {Table1.a, Table1.b, Table1.c},
  (Table1, Table2) => new {Table1})
.Join(DBContext.Table3,
  LastJoin => new {LastJoin.Table1.b, LastJoin.Table1.c, LastJoin.Table1.d},
  Table3 => new {Table3.b, Table3.c, Table3.d},
  (LastJoin,Table3) => new {LastJoin.Table1, Table3})
.Where (LastTable => LastTable.Table1.a == ValueA)
.OrderBy(LastTable => LastTable.Table3.f)
.Select (LastTable => new {LastTable.Table1, LastTable.Table3});

I have been reading that it can be done with DefaultIfEmpty() or GroupJoin() but i haven't find any complex example with more than one LEFT OUTER JOIN.

我一直在读它可以使用DefaultIfEmpty()或GroupJoin()完成,但我没有找到任何具有多个LEFT OUTER JOIN的复杂示例。

1 个解决方案

#1

Why don't you try using linq query, it is also much easier to write and understand both as compared to lambda expressions. I have on such implementation like:

为什么不尝试使用linq查询,与lambda表达式相比,编写和理解它们要容易得多。我有这样的实现,如:

var products = 
        from p in this.Products
        from cat in this.ProductCategoryProducts
        .Where(c => c.ProductID == p.ProductID).DefaultIfEmpty()

        from pc in this.ProductCategories 
        .Where(pc => ac.ProductCategoryID == cat.ProductCategoryID).DefaultIfEmpty()

        where p.ProductID == productID
        select new
        {
            ProductID = p.ProductID,
            Heading = p.Heading,                
            Category = pc.ProductCategory
        };
    return products ;