Linq有多个联接(Linq with Multiple Joins)

Linq有多个联接(Linq with Multiple Joins)

我正在尝试将以下SQL语句转换为Linq并且遇到多个连接时出现问题 - 我似乎错过了一些东西。

SELECT DISTINCT Test1 = Table1.Column1, Test2 = 1, Test3 = Table1.Column2, Test4 = Table1.Column5, Test5 = Table1.Column6 FROM Table1 LEFT JOIN Table2 ON Table1.Column1 = Table2.Column1 INNER JOIN Table3 ON Table1.Column3 = Table3.Column3 WHERE Table3.Column4 IN (1,2,6)

到目前为止，这是Linq：

var TestQuery = Table1_Collection.Select(x => new { Test1 = Table1.Column1, Test2 = 1, Test3 = Table1.Column2, Test4 = Table1.Column5, Test5 = Table1.Column6 }) [joins go here] .Where("where stuff goes here");

有任何想法吗？ 我不是那么寻求帮助.Where我在哪里加入。 我不确定方法语法的格式。

I'm attempting to translate the following SQL statement to Linq and am having trouble with the multiple joins- I seem to be missing something.

SELECT DISTINCT Test1 = Table1.Column1, Test2 = 1, Test3 = Table1.Column2, Test4 = Table1.Column5, Test5 = Table1.Column6 FROM Table1 LEFT JOIN Table2 ON Table1.Column1 = Table2.Column1 INNER JOIN Table3 ON Table1.Column3 = Table3.Column3 WHERE Table3.Column4 IN (1,2,6)

Here's the Linq so far:

var TestQuery = Table1_Collection.Select(x => new { Test1 = Table1.Column1, Test2 = 1, Test3 = Table1.Column2, Test4 = Table1.Column5, Test5 = Table1.Column6 }) [joins go here] .Where("where stuff goes here");

Any ideas? I'm not so much seeking assistance with the .Where as I am the joins. I'm not sure about the formatting with the method syntax.

最满意答案

干得好：

var results = Table3_Collection .Where(i => column4s.Contains(i.Column4)) .Join(Table1_Collection, i => i.Column3, i => i.Column3, (i, j) => j) .Join(Table2_Collection, i => i.Column1, i => i.Column1, (i, j) => i) .Distinct(comparer);

在原始SQL查询中，您没有使用从Table2中选择任何列，因此您可以省略该连接。 我把它包括在上面，但请随意删除它。

此外，您的C＃示例没有Distinct，但我将其包含在原始SQL查询中，并且很可能是您的意图。 并且，请不要忘记实现自己的IEqualityComparer。 这是一个例子：

class Table1Comparer : IEqualityComparer<Table1> { public bool Equals(Table1 x, Table1 y) { return x.Column1 == y.Column1 && x.Column2 == y.Column2 && x.Column3 == y.Column3 && x.Column4 == y.Column4 && x.Column5 == y.Column5 && x.Column6 == y.Column6; } public int GetHashCode(Table1 obj) { return obj.GetHashCode(); } }

Here you go:

var results = Table3_Collection .Where(i => column4s.Contains(i.Column4)) .Join(Table1_Collection, i => i.Column3, i => i.Column3, (i, j) => j) .Join(Table2_Collection, i => i.Column1, i => i.Column1, (i, j) => i) .Distinct(comparer);

In your original SQL query you weren't using selecting any columns from Table2, so you could omit that join. I included it above, but please feel free to remove it.

Also, your C# example didn't have Distinct, but I included it for you as it was in your original SQL query, and is most likely your intent. And, please, don't forget to implement your own IEqualityComparer. Here is an example of one:

class Table1Comparer : IEqualityComparer<Table1> { public bool Equals(Table1 x, Table1 y) { return x.Column1 == y.Column1 && x.Column2 == y.Column2 && x.Column3 == y.Column3 && x.Column4 == y.Column4 && x.Column5 == y.Column5 && x.Column6 == y.Column6; } public int GetHashCode(Table1 obj) { return obj.GetHashCode(); } }