LINQ to Entity, joining on NOT IN tables

Question

My brain seems to be mush right now! I am using LINQ to Entity, and I need to get some data from one table that does NOT exist in another table.

For example: I need the groupID, groupname and groupnumber from TABLE A where they do not exist in TABLE B. The groupID will exist in TABLE B, along with other relevant information. The tables do not have any relationship. In SQL it would be quite simply (there is a more elegant and efficient solution, but I want to paint a picture of what I need)

SELECT
   GroupID,
   GroupName,
   GroupNumber,
FROM
   TableA
WHERE
   GroupID NOT IN (SELECT GroupID FROM TableB)

Is there an easy/elegant way to do this using the Entity Framework/LINQ to Entity? Right now I have a bunch of queries hitting the db, then comparing, etc. It's pretty messy.

score 15 · Accepted Answer · answered Apr 06 '10 at 19:05

15

You could use any

  var temp =context.TableA
         .Where(x=>!context.TableB.Any(y=>y.GroupID!=x.GroupID))
         .Select(x=>new { GroupID = x.GroupID, GroupName=x.GroupName, GroupNumber = x.GroupNumber}).ToList();

answered Apr 06 '10 at 19:05

Nix

57,072
29
149
198

Should the inner clause be `y => y.GroupID == x.GroupID`? (return false if there are any items in `TableB` with this `GroupID`) – Hannele Oct 03 '13 at 18:43
@Hannele Technically its the same, and probably more readable. For some reason I was built to do boolean logic backwards. – Nix Oct 04 '13 at 12:07
I agree with Hannele, I think it should be Where(x=> !context.TableB.Any(y=>y.GroupID==x.GroupID)) – Sam Mar 03 '16 at 08:12
this was what exactly i wanted – Bimal Das Apr 24 '17 at 08:54

score 4 · Answer 2 · answered Apr 06 '10 at 18:59

It depends upon how you've met them, which you don't show, but, generally:

var q = from a in Context.TableA
        where !a.Group.TableBs.Any()
        select new
        {
            GroupID = a.GroupID,
            GroupName = a.GroupName,
            GroupNumber = a.GroupNumber
        };

score 1 · Answer 3 · edited Jan 07 '16 at 19:18

@Nix - Your result set should have been either:

 var temp =context.TableA
         .Where(x=>context.TableB.Any(y=>y.GroupID != x.GroupID))
         .Select(x=>new { GroupID = x.GroupID, GroupName=x.GroupName, GroupNumber = x.GroupNumber}).ToList();

Or

var temp =context.TableA
         .Where(x=> ! context.TableB.Any(y=>y.GroupID == x.GroupID))
         .Select(x=>new { GroupID = x.GroupID, GroupName=x.GroupName, GroupNumber = x.GroupNumber}).ToList();

But NOT both, like you wrote it.

LINQ to Entity, joining on NOT IN tables

3 Answers3

Linked