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

Solution 1

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}

Solution 2

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();

Comments

[Pascal]

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]

Man, worked like a charm! Tks sooooooooo much... couldn't find this anywhere... The same concept applies to Lambda, right?

[The Red Pea]

@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

[Dan]

The comment about naming difference applies to type difference too (e.g. when joining int and int? is needed)

[abann sunny]

If any body is interested in the documentation here it is: docs.microsoft.com/en-us/dotnet/csharp/linq/… they call it "Join by using composite keys"

[Scott Fraley]

Are those single ='s correct?