Linq to SQL - Right Join

Question

I'm trying to convert this query (Already working)

select [User].ID AS ID_USER
from [User]
right join Building on Building.ID = 4 AND
                       Building.ID_USER_RESPONSIBLE <> [User].ID
where [User].ID_MANAGER = 1

To Linq to SQL, but I don't know what I'm doing wrong. Look at my trying

IList<User> lstUser = (from building in db.GetTable<Building>()
                       join user in db.GetTable<User>()
                       on new
                       {
                           ID_BUILDING = building.ID,
                           ID_USER = building.ID_USER_RESPONSIBLE
                       }
                       equals new
                       {
                           ID_BUILDING = 4,
                           ID_USER != user.ID
                       } into grpUser
                       from grp in grpUser.DefaultIfEmpty()
                       where building.ID_MANAGER = 1
                       select new 
                       {
                           ID_USER = 
                        });

I need to return all Users plus the Users which aren't responsible to any Building;

EDIT

Any of solution posted here worked. I've resolved just doing this:

 var lstUser = (from building in db.GetTable<Building>()
                join user in db.GetTable<User>()
                on building.ID equals 4
                where user.ID_MANAGER == 1 &&
                      building.ID_USER_RESPONSIBLE != user.ID
                select new 
                {
                       ID_USER = user.ID,
                       NAME = user.NAME
                }).ToList();

http://stackoverflow.com/questions/4497086/linq-left-join-and-right-join — It'sNotALie., Jul 22 '13 at 19:10
@newStackExchangeInstance it didn't help me. I did search before posting here. — gandarez, Jul 22 '13 at 19:11
Also, you have a `!=` (in the `equals new` bit) which seems like it needs to be an `=`. — It'sNotALie., Jul 22 '13 at 19:13
What kind of errors are you getting? Off the bat, `ID_USER != user.ID` doesn't make a whole lot of sense. that `=` means assignment, not equality. — Pat Lillis, Jul 22 '13 at 19:15
I totally disagree about duplicating. My case is related to Right Join however I've a doubt about how to make a linq query when I've this `Building.ID_USER_RESPONSIBLE <> [User].ID` — gandarez, Jul 22 '13 at 19:26
Yes I did @Magnus I've no idea why your query does not work. — gandarez, Jul 26 '13 at 00:15

score 0 · Answer 1 · answered Jul 22 '13 at 19:18

See if the following works:

IList<User> lstUser = (from building in db.GetTable<Building>()
                       join user in db.GetTable<User>()
                       on new
                       {
                           ID_BUILDING = building.ID,
                           ID_USER = building.ID_USER_RESPONSIBLE,
                           building.ID_MANAGER
                       }
                       equals new
                       {
                           ID_BUILDING = 4,
                           ID_USER = user.ID,
                           1
                       } into grpUser
                       from grp in grpUser.DefaultIfEmpty()
                       select new 
                       {
                           ID_USER = grp.ID
                        });

That being said, there seems to be a gap in your requirement, "I need to return all Users plus the Users which aren't responsible to any Building;" I may be mis-reading your statement but isn't the list of users that aren't responsible to a building a subset of the larger "All Users" set. As a result, you would always just get All Users regardless of their building membership.

Linq to SQL - Right Join

1 Answers1