I have this query in SQL, but I do not know how can I do it in Linq
This is the query
SELECT
GroupName, GroupsId
FROM
Groups
WHERE
GroupsId NOT IN (SELECT Groups.GroupsId
FROM AssignGroups
JOIN Groups ON AssignGroups.GroupsId = Groups.GroupsID
AND AssignGroups.UsersId = 1)
ORDER BY
GroupName
I'm using EF.
var result = from group in Groups
let validGroups = from aGroup in AssignGroups
from vGroup in Groups
where aGroup.GroupsId == vGroup.GroupsID &&
vGroup.UserId == 1
select vGroup.GroupsID
where validGroups.Contains(group.GroupID)
orderby group.GroupName
select group;
The key here is the "let" clausule which creates a subquery, it's an IQueryable< T >, so it can be used with a "Contains" clausule (which in the end will be translated to an "IN()" clausule).
1) You can optimize your query:
SELECT
g.GroupName
, g.GroupsId
FROM Groups g
LEFT JOIN AssignGroups ag
ON g.GroupsId = ag.GroupsId
AND ag.UsersId = 1
WHERE ag.GroupsId IS NULL
ORDER BY g.GroupName
2) Your linq query:
from g in context.Groups
join ag in context.AssignGroups
on new {g.GroupsId, UsersId = 1} equals new {ag.GroupsId, ag.UsersId} into ags
from ag in ags.DefaultIfEmpty()
where ag.GroupsId == null
orderby g.GroupName
select new {g.GroupName, g.GroupsId}
