我有主表学生存储一些外国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 --"
};
解决方法
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).