对你来说最重要的是知道如何执行INNER JOIN
and an OUTER JOIN
.
For the 内部联接你用JOIN
从 LINQ 像这样:
var result =
TableA
.Join(TableB, left => left.Id, right => right.ForeignKeyToTableA,
(left, right) => new { TableAColumns = left, TableBColumns = right });
The 外连接你已经在你的例子中展示了。
现在您需要混合您所知道的内容,以获得所需的结果。
例如执行全外连接在 LINQ 中执行类似以下伪代码的操作:
SELECT TableA.*, TableB.* FROM TableA LEFT OUTER JOIN TableB
UNION
SELECT TableA.*, TableB.* FROM TableB LEFT OUTER JOIN TableA
在 LINQ 中,这将如下所示:
var fullOuterJoin =
(
TableA
.GroupJoin(TableB,
left => left.Id, right => right.ForeignKeyId,
(left, right) => new { TableA = left, TableB = right })
.SelectMany(p => p.TableB.DefaultIfEmpty(), (x, y) =>
new { TableA = x.TableA, TableB = y })
)
.Union
(
TableB
.GroupJoin(TableA,
left => left.Id, right => right.ForeignKeyId,
(left, right) => new { TableA = right, TableB = left })
.SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) =>
new { TableA = y, TableB = x.TableB })
);
您的图像的最后一个示例将是:
var fullOuterJoinOnlyWithNulls =
fullOuterJoin
.Where(p => p.TableA == null || p.TableB == null);
A 右外连接无非是一个左外连接您可以像这样交换结果列:
var rightOuterJoin =
(
TableB
.GroupJoin(TableA,
left => left.Id, right => right.ForeignKeyId,
(left, right) => new { TableA = right, TableB = left })
.SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) =>
new { TableA = y, TableB = x.TableB })
);
像这样,您可以构建所有示例场景。只需检查表格即可null需要的时候。