LINQ "not in" does not work

Question

I am trying to exclude elements with a.id that exist in db.AdminAdjusterStatus .

 from u in db.Users join a in db.Adjusters on u.id equals a.userID where u.userType.ToLower() == "adjuster" && !(from x in db.AdminAdjusterStatus select x.adjusterID).Contains(a.id) select new AdjusterProfileStatusItem { user = u, adjuster = a } 

The code above says: "where NOT contains a.id in db.AdminAdjusterStatus.adjusterID .

The problem is that it does not work. I have these two entries in db.AdminAdjusterStatus :

  A9EC05B5-651D-4AA7-8275-1F6BFE212C03
 1BDE55D9-ED0A-4854-9D5F-B89DB17F02D2

And the LINQ query gives me:

  A9EC05B5-651D-4AA7-8275-1F6BFE212C03
 1BDE55D9-ED0A-4854-9D5F-B89DB17F02D2
 e21ff49c-9505-495d-b4a3-c259ee3459d6

While he should only give me:

  e21ff49c-9505-495d-b4a3-c259ee3459d6

Answer 1

Thank you all for your help and sorry to waste time. I realized that this line:

 obj.adjusterID = '@(Url.RequestContext.RouteData.Values["id"])'; 

Elsewhere in my code was sending u.id instead of a.id , as I thought it was. Because of this, I saved an invalid identifier in my database, which led to a problem that I encountered.

Thanks again for your help!

Answer 2

 var query = from u in db.Users from a in db.Adjusters where u.userType.ToLower() == "adjuster" && u.id == a.userID && !db.AdminAdjusterStatus.Any(i => i.adjusterID == a.id) 

...

Answer 3

You can try to do a left join and filter the null entries. Without test data, I can’t say for sure if it will work out of the box, but several times I used the following code to perform this filtering:

 db.Users.Join(db.Abjusters, outer => outer.id, inner => inner.userID, new { User = outer, Adjuster = inner }) .GroupJoin(DBConcurrencyException.AdminAdjusterStatus, outer => outer.Adjuster.id, inner => inner.adjusterID, new { User = outer.User, Adjuster = outer.Adjuster, Admins = inner }) .SelectMany(grp => grp.Admins.DefaultIfEmpty(), (grp, admin) => new { User = grp.User, Adjuster = grp.Adjuster, Admin = admin }) .Where(item => item.User.userType == "adjuster" && item.Admin == null) .Select(item => new AdjusterProfileStatusItem { user = item.User, adjuster = item.Adjuster }); 

The GroupJoin / SelectMany combination performs the union on the left, and then you can filter where the object is null, which should create the same as NOT IN.

Answer 4

Maybe this might work?

  var AdjusterItems = from aa in AdminAdjusterStatus join a in Adjusters on aa.AdjusterId equals a.AdjusterId select a.UserId; var UsersNotAdjustAdmin = from u in Users where !AdjusterItems.Any(x => x == u.Id) && u.UserType.Equals("Adjuster", StringComparison.InvariantCultureIgnoreCase) select u; var result = from u in UsersNotAdjustAdmin join a in Adjusters on u.Id equals a.UserId select new AdjusterProfileStatusItem() { Adjuster = a, User = u };