CODEWITHSUNDEEP
c#sqlsql-serverlinqjoin
Steven Yates
Steven Yates

Reputation: 2480

How to use AND inside LINQ join

I'm trying to convert an SQL query to linq but do not know how to use the AND clause inside the join statement like i have in this example SQL Query.

I have highlighted the part i cannot figure out below.

DECLARE @GroupId INT = 10

SELECT 
    U.UserName, 
    U.UserID, 
    @GroupId AS "GroupID", 
    CASE WHEN GU.GROUPID IS NULL THEN 0 
         ELSE 1 END 
         AS InGroup 
FROM Users U
LEFT JOIN GroupUsers GU on GU.UserID = U.UserID 
    AND GU.GROUPID = @GroupId -- HERE!!

This is as close as I have got

from user in Users
join gu in GroupUsers on user.UserID equals gu.UserID into subq
from sub in subq.DefaultIfEmpty()
where sub.GroupID == 10 || sub == null
select new
{
    Username = user.UserName,
    UserId = user.UserID,
    GroupId = 10,
    InGroup = sub != null
}

but it using the WHERE removes data.

Upvotes: 2

Views: 181

Answers (2)

Hogan
Hogan

Reputation: 70523

This is how you do a join on multiple columns:

from user in Users
join gu in GroupUsers on new { user.UserID, user.GroupID } equals new { gu.UserID, gu.GroupID } subq
from sub in subq.DefaultIfEmpty()
select new 
{
   Username = user.UserName,
   UserId = user.UserID,
   GroupId = sub == null ? null : sub.GroupID,
   InGroup = sub != null
}

Upvotes: 1

D Stanley
D Stanley

Reputation: 152566

You could embed the GroupId condition into the query:

 from user in Users
 join gu in GroupUsers.Where(gu => gu.GroupID == 10)
    on user.UserID equals gu.UserID into subq
 from sub in subq.DefaultIfEmpty()
 select new 
 {
     Username = user.UserName,
     UserId = user.UserID,
     GroupId = 10,
     InGroup = sub != null
 }

Upvotes: 3

Related Questions