LINQ double left join

Question

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.

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

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 : ""


                };

Answer 1

EDIT:

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.

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.

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