CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sql
German Ortiz
German Ortiz

Reputation: 639

Query Linq to Sql Entity Framework c#

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.

Upvotes: 1

Views: 173

Answers (2)

Gusman
Gusman

Reputation: 15151

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).

Upvotes: 2

akekir
akekir

Reputation: 523

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}

Upvotes: 0

Related Questions