c# – 使用LINQ进行左外连接 – 理解代码

发布时间：2020-12-15 07:56:42 所属栏目：百科来源：网络整理

导读：如果有人能够在使用LINQ时解释该术语的含义,我将不胜感激.一般来说,我试图了解如何在C#中进行INNER JOIN,LEFT OUTER JOIN等. 我有主表学生存储一些外国ID密钥,然后在运行查询时用它们的名称替换.名称从标记表中读取,例如Marks,SoftwareVersions,Departments

如果有人能够在使用LINQ时解释该术语的含义,我将不胜感激.一般来说,我试图了解如何在C#中进行INNER JOIN,LEFT OUTER JOIN等.

我有主表学生存储一些外国ID密钥,然后在运行查询时用它们的名称替换.名称从标记表中读取,例如Marks,SoftwareVersions,Departments等.所有字段都是必需的,但MarkID.我试图在LINQ中构建的查询是这样的：

SELECT * FROM dbo.Students
INNER JOIN dbo.Departments ON dbo.Students.DepartmentID=dbo.Departments.DepartmentID
INNER JOIN dbo.SoftwareVersions ON dbo.Students.SoftwareVersionID=dbo.SoftwareVersions.SoftwareVersionID
INNER JOIN dbo.Statuses ON dbo.Students.StatusID=dbo.Statuses.StatusID
LEFT JOIN dbo.Marks ON dbo.Students.MarkID=dbo.Marks.MarkID
WHERE dbo.Students.DepartmentID=17;

在阅读了大量文章和观看一些视频之后,我设法让下面的代码得到了解决,但我不觉得我对代码有完整的理解.让我感到困惑的是第5行以in结尾,然后是从m开始的下一行….我很困惑什么做了什么以及从m中发生了什么….这就是LINQ中的代码：

var result = from st in dbContext.Students where st.DepartmentID == 17
             join d in dbContext.Departments on st.DepartmentID equals d.DepartmentID
             join sv in dbContext.SoftwareVersions on st.SoftwareVersionID equals sv.SoftwareVersionID
             join stat in dbContext.Statuses on st.StatusID equals stat.StatusID
             join m in dbContext.Marks on st.MarkID equals m.MarkID into marksGroup
             from m in marksGroup.DefaultIfEmpty()
             select new
             {
                 student = st.StudentName,department = p.DepartmentName,software = sv.SoftwareVersionName,status = st.StatusName,marked = m != null ? m.MarkName : "-- Not marked --"
             };

解决方法

我相信 How to: Perform Left Outer Joins MSDN page的示例部分真的很好解释.让我们把它投射到你的例子中.引用页面的第一段

The first step in producing a left outer join of two collections is to
perform an inner join by using a group join. (See How to: Perform
Inner Joins (C# Programming Guide) for an explanation of this
process.) In this example,the list of Person objects is inner-joined
to the list of Pet objects based on a Person object that matches
Pet.Owner.

因此,在您的情况下,第一步是使用基于MarkID对象的Marks对象列表执行Student对象列表的内部联接,对象与Marks对象中的MarkID匹配.从报价中可以看出,内连接正在使用group join执行.如果您在MSDN页面中查看关于如何执行组连接的注释部分,您可以看到

Each element of the first collection appears in the result set of a
group join regardless of whether correlated elements are found in the
second collection. In the case where no correlated elements are found,
the sequence of correlated elements for that element is empty. The
result selector therefore has access to every element of the first
collection.

这意味着在你的例子的上下文中,通过使用你有组连接的结果,你有所有的学生对象,以及Marks对象的相关元素序列(如果没有匹配的Marks对象,序列将会是空的.

现在让我们回到如何：执行左外连接MSDN页面,特别是第二段

The second step is to include each element of the first (left)
collection in the result set even if that element has no matches in
the right collection. This is accomplished by calling DefaultIfEmpty
on each sequence of matching elements from the group join. In this
example,DefaultIfEmpty is called on each sequence of matching Pet
objects. The method returns a collection that contains a single,
default value if the sequence of matching Pet objects is empty for any
Person object,thereby ensuring that each Person object is represented
in the result collection.

再次,为了将此项目投射到您的示例中,将在匹配Marks对象的每个序列上调用DefaultIsEmpty().如上所述,如果匹配Marks对象的序列对于任何Student对象为空,则该方法返回包含单个默认值的集合,这确保每个Student对象将在结果集合中表示.因此,您拥有的元素集包含所有Student对象和匹配的Marks对象,或者如果没有匹配的Marks对象,则Marks的默认值(在本例中为null).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!