小编典典

LINQ to SQL:在多个列上进行多个联接。这可能吗?

c#

鉴于:

命名的表TABLE_1有以下栏目:

  • ID
  • ColumnA
  • ColumnB
  • ColumnC

我有SQL查询,其中TABLE_1基于关闭的两次自己加入ColumnAColumnBColumnC。查询可能看起来像这样:

Select t1.ID, t2.ID, t3.ID
  From TABLE_1 t1
  Left Join TABLE_1 t2 On
       t1.ColumnA = t2.ColumnA
   And t1.ColumnB = t2.ColumnB
   And t1.ColumnC = t2.ColumnC
  Left Join TABLE_1 t3 On
       t2.ColumnA = t3.ColumnA
   And t2.ColumnB = t3.ColumnB
   And t2.ColumnC = t3.ColumnC
... and query continues on etc.

问题:

我需要在LINQ中重写该查询。我试着刺了一下:

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on t1.ColumnA equals t2.ColumnA
      && t1.ColumnB equals t2.ColumnA
    // ... and at this point intellisense is making it very obvious
    // I am doing something wrong :(

如何在LINQ中编写查询?我究竟做错了什么?


阅读 196

收藏
2020-05-19

共1个答案

小编典典

在Linq to SQL中的多个列上进行连接有点不同。

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { t1.ColumnA, t1.ColumnB } equals new { t2.ColumnA, t2.ColumnB }
    ...

您必须利用匿名类型,并为要比较的多个列组成一个类型。

乍一看这似乎很令人困惑,但是一旦您熟悉了由表达式构成SQL的方式,它将变得更加有意义,在幕后,这将生成您要查找的联接类型。

编辑 添加基于注释的第二个连接的示例。

var query =
    from t1 in myTABLE1List // List<TABLE_1>
    join t2 in myTABLE1List
      on new { A = t1.ColumnA, B = t1.ColumnB } equals new { A = t2.ColumnA, B = t2.ColumnB }
    join t3 in myTABLE1List
      on new { A = t2.ColumnA, B =  t2.ColumnB } equals new { A = t3.ColumnA, B = t3.ColumnB }
    ...
2020-05-19