Help With LINQ: Mixed Joins and Specifying Default Values

Question

I am trying to figure out how to do a mixed-join in LINQ with specific access to 2 LINQ objects. Here is an example of how the actual TSQL query might look:

SELECT
  *
FROM
  [User] AS [a]
INNER JOIN
  [GroupUser] AS [b]
ON
  [a].[UserID] = [b].[UserID]
INNER JOIN
  [Group] AS [c]
ON
  [b].[GroupID] = [c].[GroupID]
LEFT JOIN
  [GroupEntries] AS [d]
ON
  [a].[GroupID] = [d].[GroupID]
WHERE [a].[UserID] = @UserID

At the end, basically what I would like is an enumerable object full of GroupEntry objects. What am interested is the last two tables/objects in this query. I will be displaying Groups as a group header, and all of the Entries underneath their group heading. If there are no entries for a group, I still want to see that group as a header without any entries. Here's what I have so far:

So from that I'd like to make a function:

public void DisplayEntriesByUser(int user_id)
{
    MyDataContext db = new MyDataContext();

    IEnumberable<GroupEntries> entries =
    (
        from user in db.Users
        where user.UserID == user_id
        join group_user in db.GroupUsers
          on user.UserID = group_user.UserID
        into a

        from join1 in a
        join group in db.Groups
          on join1.GroupID equals group.GroupID
        into b

        from join2 in b
        join entry in db.Entries.DefaultIfEmpty()
          on join2.GroupID equals entry.GroupID
        select entry
    );


    Group last_group_id = 0;
    foreach(GroupEntry entry in entries)
    {
        if (last_group_id == 0 || entry.GroupID != last_group_id)
        {
            last_group_id = entry.GroupID;
            System.Console.WriteLine("---{0}---", entry.Group.GroupName.ToString().ToUpper());

        }
        if (entry.EntryID)
        {
            System.Console.WriteLine("    {0}: {1}", entry.Title, entry.Text);
        }
    }
}

The example above does not work quite as expected. There are 2 problems that I have not been able to solve:

1. I still seem to be getting an INNER JOIN instead of a LEFT JOIN on the last join. I am not getting any empty results, so groups without entries do not appear.

2. I need to figure out a way so that I can fill in the default values for blank sets of entries. That is, if there is a group without an entry, I would like to have a mostly blank entry returned, except that I'd want the EntryID to be null or 0, the GroupID to be that of of the empty group that it represents, and I'd need a handle on the entry.Group object (i.e. it's parent, empty Group object).

Any help on this would be greatly appreciated.

Note: Table names and real-world representation were derived purely for this example, but their relations simplify what I'm trying to do.

Answer 1

This is untested, but I think it's pretty close:

var groupEntries =   
    from
        u in db.Users
    where
        user.Id == user_id
    join
        gu in db.GroupUsers
        on u.UserId equals gu.UserId
    join
        g in db.Groups
        on gu.GroupId equals g.GroupId
    join
        ge in db.GroupEntries
        on u.GroupdId equals ge.GroupId
        into ges
    from
        ge in ges.DefaultIfEmpty(new GroupEntry { EntryId = 0, GroupId = g.GroupId })
    select
        ge;

I don't think you need to use into unless you're planning to do some further processing, such as DefaultIfEmpty(). And notice that the second overload of DefaultIfEmpty() allows you to enter a custom default value. So, you can create a new GroupEntry object and assign the values you want for each property (or leave properties blank).

Answer 2

  //set this to see all queries issued.
myDC.Log = Console.Out;

  //setup to load the GroupEntries property of each group
DataLoadOptions o = new DataLoadOptions();
o.LoadWith<Group>(g => g.GroupEntries);
myDC.LoadOptions = o;

  //query to get the groups
IQueryable<Group> groupQuery =
  from g in myDC.Groups
  where g.GroupUsers.Any(gu => gu.User.UserID == user_id)
  select g;