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