LINQ双左联接 [英] LINQ double left join

问题描述

我有一个带名称的学生班，一个带学生名称和条目标题的blogEntries班，以及带作业名称的blogAssignments班.

I've got a students class with a name, a blogEntries class with a student name and entry title, and a blogAssignments class with an assignment name.

我想向所有学生展示，并向他们展示他们创建的BlogEntries，其标题与blogAssignments.assignmentName相匹配.

I'd like to show ALL students and alongside them the blogEntries they created whose title matches the blogAssignments.assignmentName.

我有以下LINQ查询，但我不知道在哪里加入blogAssignments:

I've got the following LINQ query but I can't figure out where to join in the blogAssignments:

var results =
                from person in HACCStudents
                join bEntry in blogEntries on person.FullName equals bEntry.AuthorFullName
                into studentBlogEntries
                from studentBlogEntry in studentBlogEntries.DefaultIfEmpty()
                orderby person.FullName
                select new { PersonName = person.FullName, 
                    BlogTitle=studentBlogEntry != null ?studentBlogEntry.Title : ""


                };

推荐答案

这是我编写的一个测试用例，我认为使用LINQ to Objects可以满足您的要求:

Here's a test case I wrote which I think meets your requirements using LINQ to Objects:

public class Person
{
    public string FullName;
}

public class BlogEntry
{
    public string AuthorFullName;
    public string Title;
}

public class BlogAssignment
{
    public string AssignmentName;
}

public void GetStudentsWithAssignments()
{
    var students = new List
    {
        new Person { FullName = "Bob Doe" },
        new Person { FullName = "Steve Carrell" },
        new Person { FullName = "John West" },
        new Person { FullName = "Alice Croft" },
        new Person { FullName = "Al Bean" }
    };

    var entries = new List
    {
        new BlogEntry { AuthorFullName = "Steve Carrell", Title = "100 ways to skin a cat" },
        new BlogEntry { AuthorFullName = "Steve Carrell", Title = "LINQ sux arze" },
        new BlogEntry { AuthorFullName = "John West", Title = "Salmon bake 2007" },
        new BlogEntry { AuthorFullName = "John West", Title = "Make my day" },
        new BlogEntry { AuthorFullName = "Al Bean", Title = "An inconvenient tooth" }
    };

    var assignments = new List
    {
        new BlogAssignment { AssignmentName = "Salmon bake 2007" },
        new BlogAssignment { AssignmentName = "LINQ sux arze" },
        new BlogAssignment { AssignmentName = "Make my day" }
    };

    var subquery =
        from e in entries
        join a in assignments on e.Title equals a.AssignmentName
        select e;

    var results =
        from person in students
        join entry in subquery on person.FullName equals entry.AuthorFullName into personEntries
        from personEntry in personEntries.DefaultIfEmpty()
        orderby person.FullName
        select new
        {
            PersonName = person.FullName,
            BlogTitle = personEntry == null ? "" : personEntry.Title
        };
}

之所以棘手，是因为您想向所有学生展示，但只允许对与博客分配匹配的博客条目进行左联接，这是内部联接.

What made this trickier is that you want to show all students, but only left join to blog entries that match with blog assignments, which is an inner join.

这就是为什么我添加了执行内部联接的子查询，然后使用join..into语法在主查询中将联接保留到该子查询的原因，从而获得了外部联接的语义.

This is why I've added the subquery which does the inner join, then you left join to that subquery in your main query using the join..into syntax, to get left outer join semantics.

您可以一次完成所有操作，但是我发现通过将子查询分解为单独的声明来读取LINQ查询要容易得多.

You could do it all in one go, but I find it much easier to read LINQ queries by factoring subqueries into separate declarations.

这篇关于LINQ双左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！