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