Ridge

Given the following table schema:

Code Snippet

Users UserItems Items

------ ---------- -----

Id UserId Id

Name ItemId Name

SomeField

I'm trying to produce a SQL statement like:

Code Snippet

SELECT
Items.Id,
Items.Name,
UserItems.SomeField
FROM
Items
LEFT OUTER JOIN
UserItems
ON
UserItems.ItemId = Items.Id

AND

UserItems.UserId = @userId

For the life of me I can't get Linq to produce both of the join conditions... It would appear I'd need some flavor of a group join and a DefaultOrEmpty call, but I can't wrangle the syntax to work for me. Could someone offer a clue here




Re: LINQ Project General Linq to Sql left join is baffling me

Ridge

Of course after spending a depressing amount of time trying to get this to work, I solve it after I post a call for help...

This is what I came up with:

Code Snippet
var query =
from i in db.Items
join ui in db.UserItems on i.Id equals ui.ItemId into uio
from o in uio.Where(o => o.UserId == 2).DefaultIfEmpty()
select new { i.Id, i.Name, o.SomeField };

This still seems odd to me, mixing in the lambda syntax to get my second constraint... Is there an alternative






Re: LINQ Project General Linq to Sql left join is baffling me

Matt Warren - MSFT

What you've done is correct. You could have also constrained the UserItems collection before you joined it. It's rare to use anything but an exact join condition in an ON clause so I don't think many people will need to do this.