How to convet mutliple SQL left joins to Linq-To-SQL

Question

I have a user table, a user_items table, a user_to_group table and a group table.

How would I convert the following SQL query to the correct Linq-to-sql query?

select user.username, user.email, user_items.item_number, item_types.desc, [group].name from user
left join user_items on user.user_id = user_items.user_id
left join item_types on user_items.item_type_id = item_types.item_type_id
left join user_to_group on user.user_id = user_to_group.user_id
left join [group] on user_to_group.group_id = [group].group_id

I have tried using group joins and joining with DefaultIfEmpty, but I'm unable to return the exact same results as my SQL query.

Thank you.

@ChrisF,

Sure, I could break it down into:

select user.username, user.email, user_items.item_number from user 
left join user_items on user.user_id = user_items.user_id 

Which I have in linq2sql as:

var users = (from u in db.users 
join ui in db.user_items on u.user_id equals ui.user_id into useritems from ui in useritems.DefaultIfEmpty() 
select new { user = u, item_number = useritems == null ? string.Empty : useritems.FirstOrDefault().item_number}); 

However, it still doesn't return all the results it should, and I can't see where I'm going wrong.

Answer 1

You will need to look at the join ... into ... construct.

Here is an example on MSDN

... Something else to consider ... LINQ will allow you to turn your simple table structure into a complex hierarchy. Another option beyond the left joins would be nested queries. Maybe something like this...

var selected = from user in users
               select new
               {
                   user,
                   items = from item in user_items
                           where item.user_id == user.user_id
                           join it in item_types
                               on item.item_type_id equals it.item_type_id
                           select it,
                   groups = from ug in user_to_groups
                            where ug.user_id == user.user_id
                            join @group in groups
                                on ug.group_id equals @group.group_id
                            select @group.name,
               };

Answer 2

I found my problem, I was selecting

useritems.FirstOrDefault().item_number

Which is what I selected into (and FirstOrDefault!), instead of what I used for my join:

ui.item_number

So my complete linq query is

var users = (from u in db.users
            join ug in db.user_to_groups on p.user_id equals pg.user_id into pgrp
            from ug in pgrp.DefaultIfEmpty()
            join g in db.groups on pg.group_id equals g.group_id into groups
            from g in groups.DefaultIfEmpty()
            join ui in db.user_items on u.user_id equals ui.user_id into useritems 
            from ui in useritems.DefaultIfEmpty()                          

        select new { user = u, item_number = ui == null ? string.Empty : ui.item_number, usergroup = g == null ? string.Empty : g.name});

Thanks for the help!

Answer 3

You query could be written more tersely if you define some associations between your objects:

from u in db.Users
from ug in u.User_To_Groups.DefaultIfEmpty()
let g = ug.Group
from ui in u.UserItems.DefaultIfEmpty()
select new
{
  user = u,
  item_number = ui == null ? string.Empty : ui.item_number,
  usergroup = g == null ? string.Empty : g.name
});