New to L2E, found out that I can't use DefaultIfEmpty() until I get L2E 4 (which will hopefully be happening soon). I found an article regarding left joins via inverse navigation, which seems like a clean method for left joins. That said, the article appears to be incorrect, in that the snippet
from lPnpItem in PnpItems
join lPnpItemValue in PnpItemValues on lPnpItem.Id equals lPnpItemValue.PnpItem.Id into lValues
select new
{
PnpItemName = lPnpItem.Name,
PnpItemValue1Planned = lValue.PlannedValue,
PnpItemValue1Actual = lValue.ActualValue
}
does not work because you cannot directly call the table lValue - at least in my case. Perhaps it works for a 1:1 relationship (curious if anyone knows the truth in this), but does not work for my 0..1:many relationship. My code addresses that and looks like so:
from user in db.users
join role in db.roles
on user.RoleID equals role.RoleID
into user_roles
select new
{
id = user.UID,
firstName = user.FirstName,
lastName = user.LastName,
roles = user_roles.ToList(x => x.RoleID == user.RoleID).Role
};
This works for my needs, and will manage to return a valid record where a role is null, as a left join would. As I'm new to L2E though, I'm a bit curious about the performance issues of the calls made to user_roles - in this case, it's just one, but I have more complicated queries with 10+ left joins (hurray horrendous data) on a medium sized database that I'm worried about.
Is this the most efficient method for a inverse navigation left join? Were I to query a 1:1 relationship, would user_roles.FirstOrDefault(lambda).Role be sufficient, or would I actually be able to call user_roles.Role?