鉴于:
命名的表TABLE_1有以下栏目:
TABLE_1
ID
ColumnA
ColumnB
ColumnC
我有SQL查询,其中TABLE_1基于关闭的两次自己加入ColumnA,ColumnB,ColumnC。查询可能看起来像这样:
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中编写查询?我究竟做错了什么?
在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 } ...