How to write the "not in" construct of T-Sql in Linq to Entities as a SubQuery?

Question

The query:-

    select * from Product.Barcodes where Barcode='00A000000O' and  
 Barcode not in(select Barcode from Sales.SalePointItem)

I have tried the following, but these does not work:-

db.Barcodes.SingleOrDefault(b => b.Barcode.Equals(ItemBarcode) &&  
  !db.SalePointItems.Select(m=>m.Barcode).Contains(b.Barcode));

db.Barcodes.SingleOrDefault(b => b.Barcode.Equals(ItemBarcode) &&  
  !db.SalePointItems.Any(m=>m.Barcode.Equals(b.Barcode)));

I get the following exception on running both of above:-

Unable to create a constant value of type 'SalePointItemModel'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

What do you mean does not work? Are you getting the wrong result? Or is the generated SQL not what you expect? What is the generated SQL? — Aducci
– Aducci, Commented May 12, 2012 at 15:48

Aducci · Accepted Answer · 2012-05-12 20:12:15Z

1

Try this one:

var item = (from b in db.Barcodes
            where b.Barcode == ItemBarcode
            where !db.SalePointItems.Any(m => m.Barcode == b.Barcode)
            select b).SingleOrDefault();

Method Syntax -

var item = db.Barcodes.Where(b => b.Barcode == ItemBarcode)
                      .Where(b => !db.SalePointItems.Any(m => m.Barcode == b.Barcode))
                      .SingleOrDefault();

edited May 12, 2012 at 20:12

answered May 12, 2012 at 15:57

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

teenup Over a year ago

I know this method, but I don't want a separate query, I wanted to run it as a subquery. See the title of the question as I have updated it.

teenup Over a year ago

The intent is to avoid fetching the list of barcodes into memory which the origninal t-sql does.

teenup Over a year ago

Actually, this works, but I was looking for extension method chains, because I usually write in that form, all my queries.

Aducci Over a year ago

@puretechy - I added extension method way.

Collectives™ on Stack Overflow

How to write the "not in" construct of T-Sql in Linq to Entities as a SubQuery?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related