Using 'NOT IN' Clause in LINQ Query

Question

I have tested the following LINQ query in LINQPad. But it gives me this error:

Error Compiling Expression: Error Compiling Expression: The best overloaded method match for 'string.Contains(string)' has some invalid arguments Argument '1': cannot convert from 'System.Linq.IQueryable' to 'string'

(from t in db.ASN_ITEM
join t0 in db.ASN_MASTER on t.AWB_NO equals t0.AWB_NO
join t1 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t1.ITEM_CODE
where
  (t.TOT_QTY - t.SCND_QTY) != 0 &&
  !t.AWB_NO.Contains((from t2 in db.ASN_ITEM
                    where
                     t2.SCAN_STAT != 2
                        select new {
                         t2.AWB_NO
                        }).Distinct()) &&
  t.AWB_NO == "E1000001"
select new {
  t.AWB_NO,
  ASN_DATE = t.REC_DATE,
  t.PALLET,
  t.CARTON,
  t.TOT_QTY,
  t.SCND_QTY,
  VAR_QTY = (System.Int32?)(t.SCND_QTY - t.TOT_QTY),
  REMARKS = 
  (t.TOT_QTY - t.SCND_QTY) == 0 ? "No Variance" : 
  (t.TOT_QTY - t.SCND_QTY) > 0 ? "Less Qty" : 
  (t.TOT_QTY - t.SCND_QTY) < 0 &&
  t.TOT_QTY != 0 ? "Excess Qty" : 
  t.TOT_QTY == 0 &&
  t.SCND_QTY != 0 ? "Excess Item" : null,
  t1.PART_NO
}).Distinct()

I got this error, when I give below condition in where clause:

!t.AWB_NO.Contains((from t2 in db.ASN_ITEM
                where
                 t2.SCAN_STAT != 2
                    select new {
                     t2.AWB_NO
                    }).Distinct())

Actually in SQL query, this is what I needed (below):

WHERE ASN_ITEM.AWB_NO NOT IN (SELECT DISTINCT AWB_NO FROM ASN_ITEM WHERE SCAN_STAT !=2 )

I think it's because your `select new { ... }` inside `Contains(...)` creates a new anonymous type. You'll have to parse it to string or create `new string {...}`. — diiN__________, Feb 05 '16 at 07:01
Assuming `t2.AWB_NO` is a string you can just use `select t2.AWB_NO` instead of using projection with the `new` keyword — Alexander Derck, Feb 05 '16 at 07:35

har07 · Accepted Answer · 2016-02-05T07:48:25.620

You should've done it the other way around. Call Contains() on the subquery, the one that has multiple items in it :

!(from t2 in db.ASN_ITEM
  where t2.SCAN_STAT != 2
  select t2.AWB_NO
).Distinct()
 .Contains(t.AWB_NO)

Also, you have to select AWB_NO directly, as shown above, instead of projecting to anonymous type. Doing the latter will prevent usage of Contains(), since item type in collection will be different from object type passed as parameter of Contains().

score 0 · Answer 2 · answered Feb 05 '16 at 07:36

From your code, I gather that AWB_NO is a string. If so, then what you're doing here:

!t.AWB_NO.Contains((from t2 in db.ASN_ITEM
                where
                 t2.SCAN_STAT != 2
                    select new {
                     t2.AWB_NO
                    }).Distinct())

translates to this: "It's not true that a string ABW_NO contains (and it's this Contains, not this) a table of some distinct elements of anonymous type with one string property". Which makes no sense. It isn't a "not in" query. You're checking if a single String contains a set of anonymous type objects.

if you want to use a String.Contains and check if a string contains another string, then this will make more sense (may be not what you want though):

!t.AWB_NO.Contains((from t2 in db.ASN_ITEM
                where
                 t2.SCAN_STAT != 2
                    select t2.AWB_NO).FirstOrDefault())

You may find this helpful:

How would you do a "not in" query with LINQ?

"NOT IN" clause in LINQ to Entities

Using 'NOT IN' Clause in LINQ Query

2 Answers2