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 

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

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