CODEWITHSUNDEEP
linq-to-sql
Malcolm
Malcolm

Reputation: 12864

Subquery with Linq to Sql

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

Upvotes: 0

Views: 2397

Answers (3)

zellio
zellio

Reputation: 32484

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;

Upvotes: 1

Malcolm
Malcolm

Reputation: 12864

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();

Upvotes: 1

Nasmi Sabeer
Nasmi Sabeer

Reputation: 1380

    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

Upvotes: 0

Related Questions