Joining tables using more than one column in Linq To Entities

Question

Every single example of joins in Linq to Entities involves only one column in the on clause. What is the syntax if I need 2 or more columns to make the join work? I would need an example for Linq to Entities Query Expressions and Method Based also, if possible. Below is the example of what I need. There isn't a relationship between Table1 and Table2.

CREATE TABLE dbo.Table1 (
  ID1Table1 INT NOT NULL,
  ID2Table1 SMALLDATETIME NOT NULL,
  Value1Table1 VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY (ID1Table1, ID2Table1));
CREATE TABLE dbo.Table2 (
  ID1Table2 INT NOT NULL,
  ID2Table2 SMALLDATETIME NOT NULL,
  ID3Table2 INT NOT NULL,
  Value1Table2 VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Table2 PRIMARY KEY (ID1Table2, ID2Table2, ID3Table2));

SELECT a.ID1Table1, a.ID2Table1, a.Value1Table1, b.ID3Table2, b.Value1Table2
FROM dbo.Table1 a JOIN dbo.Table2 b
  ON a.ID1Table1 = b.ID1Table2
  AND a.ID2Table1 = b.ID2Table2

score 47 · Accepted Answer · edited Dec 01 '19 at 20:57

47

You can write it using two from expressions like below:

from a in Table1s 
from b in Table2s
where a.ID1Table1 == b.ID1Table2 && a.ID2Table1 == b.ID2Table2
select new {a.ID1Table1, a.ID2Table1, a.Value1Table1, b.ID3Table2, b.Value1Table2}

Using join:

from a in Table1s
join b in Table2s on new{PropertyName1 = a.ID1Table1, PropertyName2 = a.ID2Table1} equals new{PropertyName1 = b.ID1Table2, PropertyName2 = b.ID2Table2}
select new {a.ID1Table1, a.ID2Table1, a.Value1Table1, b.ID3Table2, b.Value1Table2}

edited Dec 01 '19 at 20:57

Owen

4,229
5
42
50

answered Jan 19 '11 at 14:28

Chandu

81,493
19
133
134

1

Ok, tks. I will solve my problem, but I would really like to know how to accomplish this is using the join, in Query Expression and Method-Based. – Pascal Jan 19 '11 at 14:36
1

Man, worked like a charm! Tks sooooooooo much... couldn't find this anywhere... The same concept applies to Lambda, right? – Pascal Jan 19 '11 at 14:53
1

@Chandu is it true that the former style can use inequality (!=), but the latter cannot? In my case I want to find the inner join product where a column `Value` is different between tables, like: `where a.ID1Table1 == b.ID1Table2 && a.ID2Table1 == b.ID2Table2 && a.Value != b.Value` – Nate Anderson Sep 02 '15 at 17:59
If any body is interested in the documentation here it is: https://learn.microsoft.com/en-us/dotnet/csharp/linq/join-by-using-composite-keys they call it "Join by using composite keys" – abann sunny Nov 19 '20 at 19:50
Are those single `=`'s correct? – Scott Fraley Jun 03 '22 at 18:01

score 28 · Answer 2 · answered Feb 27 '14 at 07:52

For method based query:

var query = ctx.Table1s.Join(ctx.Table2s,
  a => new { a.ID1Table1, a.ID2Table1 },
  b => new { b.ID1Table2, b.ID2Table2 },
  (t1, t2) => new {
  t1.ID1Table1, t1.ID2Table1, t1.Value1Table1, t2.ID3Table2, t2.Value1Table2
});

if happen to be key column name is different between two tables, then should assign a same propery name in outer and inner selector. eg:

var query = ctx.Table1s.Join(ctx.Table2s,
  a => new { key1 = a.ID1Table1, key2 = a.ID2Table1 },
  b => new { key1 = b.ID1Table2, key2 = b.ID2Table2 },
  (t1, t2) => new {
    t1.ID1Table1, t1.ID2Table1, t1.Value1Table1, t2.ID3Table2, t2.Value1Table2
});

to verify the above query, print the sql statement:

string sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

The comment about naming difference applies to type difference too (e.g. when joining `int` and `int?` is needed) — Dan, Sep 12 '19 at 17:55

Joining tables using more than one column in Linq To Entities

2 Answers2

Linked