I have following LINQ statement. How do I change this so I get GroupIDs only in the subquery. My syntax here is not working.
And also only distinct Users.
from u in Users
join ug in UserGroups on u.UserID equals ug.UserID
where ug.GroupID == (from igr in UserGroups where igr.UserID == 1 select igr.GroupID)
select u
This is how a did it in then end.
var query = (from u in _dbctx.Users
join ug in _dbctx.UserGroups on u.UserID equals ug.UserID
where _dbctx.UserGroups.Any(igr => igr.GroupID == ug.GroupID && igr.UserID == 1)
select GetUser(u)).Distinct();
[code][/code] don't work here.I would break it into two separate queries for readability but here goes
var group = from igr in UserGroup
where irg.UserID == 1
select igr.GroupID;
var result = from u in Users
join ug in UserGroups on u.UserID equals ug.UersID
into x
where group.Contains( x.GroupID )
select x;
As one query I believe it would work like this
var result = from u in Users
join ug in UserGroups on u.UserID equals ug.UersID
into x
where
(from igr in UserGroup
where irg.UserID == 1
select igr.GroupID).Contains( x.GroupID )
select x;
from u in Users
join ug in UserGroups on u.UserID equals ug.UserID
where ug.GroupID == (from igr in UserGroups where igr.UserID == 1 select igr.GroupID).FirstOrDefault()
select u
inner query must return exactly one value
