linq to sql：从同一个表中连接多个列

How do I inner join multiple columns from the same tables via Linq?

如何通过Linq从同一个表中连接多个列?

For example: I already have this...

例如:我已经有了......

join c in db.table2 on table2.ID equals table1.ID

I need to add this...

我需要添加这个......

join d in db.table2 on table2.Country equals table1.Country

5 个解决方案

#1

You can put your query inside a Where clause instead of using the join operator.

您可以将查询放在Where子句中,而不是使用join运算符。

The join operator supports multiple clauses in VB.NET, but not C#.

join操作符支持VB.NET中的多个子句,但不支持C#。

Alternatively, you can use the ANSI-82 style of 'SQL' syntax, e.g.:

或者,您可以使用ANSI-82样式的“SQL”语法,例如:

from t1 in table1
from t2 in table1
where t1.x == t2.x
&& t1.y == t2.y

#2

This is the only way I was able to get it to work (in c#).

这是我能够让它工作的唯一方法(在c#中)。

var qry = from t1 in table1
          join t2 in table2
          on new {t1.ID,t1.Country} equals new {t2.ID,t2.Country}
          ...

#3

from http://www.onedotnetway.com/linq-to-sql-join-on-multiple-conditions/

Both these tables have PostCode and CouncilCode as common fields. Lets say that we want to retrieve all records from ShoppingMall where both PostCode and CouncilCode on House match. This requires us to do a join using two columns. In LINQ such a join can be done using anonymous types. Here is an example.

这两个表都有PostCode和CouncilCode作为公共字段。假设我们想从ShoppingMall检索所有记录,其中HouseCode和HouseCode on House匹配。这要求我们使用两列进行连接。在LINQ中,可以使用匿名类型完成连接。这是一个例子。

var query = from s in context.ShoppingMalls
        join h in context.Houses
        on
        new { s.CouncilCode, s.PostCode }
        equals
         new { h.CouncilCode, h.PostCode }
        select s;

#4

var query = from s in context.ShoppingMalls
join h in context.Houses
on
new {CouncilCode=s.CouncilCode, PostCode=s.PostCode }
equals
new {CouncilCode=h.District, PostCode=h.ZipCode }
select s;

This is applicable for any kind of datatype.

这适用于任何类型的数据类型。

#5

In VB:

 dim qry = FROM t1 in table1 _
           JOIN t2 in table2 on t2.ID equals t1.ID _
           AND t2.Country equals t1.Country

#1