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.

Answer 1

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

Answer 2

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
        };

Answer 3

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