linq to sql: join multiple columns from the same table

Question

How do I inner join multiple columns from the same tables via 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 

Answer 1

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

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

Answer 2

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

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

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

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

Answer 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.

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;

Answer 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.

Answer 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