Reputation: 3045
Query 1
var resulty = db.QIS
.Where(w=>w.QSA.Any(a => a.QSID != w.QSID))
.Select(s => s.QSID).ToList();
Query 2
var resultz = db.QIS
.Where(w=>w.QSA.Where(h => h.QSID == w.QSID).Select(s => s.QSID).FirstOrDefault() != w.QSID)
.Select(s => s.QSID).ToList();
Table QIS
and QSA
are related Many:Many
. The idea here is to find how many QIS.ID's
are not Found in QSA
where QIS.QID == QSA.QID
.
Query 1
returns 0
Query 2
on the other hand gives me what I expected to see ( the list off all non matching QIS records.)
Why will the any not work? - i found myself running into the same situation a couple of times now in seperate scenarios... thanks for any help / thoughts.
PS: Prefer method syntax.
Upvotes: 1
Views: 74
Reputation: 69310
In the filtering in your second version, will only be true if the inner Where
clause returns no elements, so that FirstOrDefault()
returns null
or 0
(depending on if the type is nullable or not).
w=>w.QSA.Where(h => h.QSID == w.QSID)
.Select(s => s.QSID).FirstOrDefault() != w.QSID
Which is equivalent to (now assuming QSID
is a non nullable numeric type, if it is nullable, use null
instead of zero):
w=>w.QSA.Where(h => h.QSID == w.QSID)
.Select(s => s.QSID).FirstOrDefault() == 0
which can be rewritten to:
w=>w.QSA.Where(h => h.QSID == w.QSID).FirstOrDefault() == null
which can be rewritten to:
w=>!w.QSA.Any(h => h.QSID == w.QSID)
which is nearly the same as your initial version, but not exactly. You still want to check for equivalence inside the Any()
filter, but then negate the result.
Upvotes: 4