Select from two tables using "NOT IN" in LINQ in Entity Framework

Question

i have the following SQL, how can i implement that in LINQ, i am using Entity framework in my MVC project.

  SELECT 
  * FROM Catalog.ServiceItem 
  WHERE SKU NOT IN (SELECT ServiceItemSKU FROM Catalog.TaggedServiceItems)

Any Ideas ?

EDIT: ANSWER:

var untaggedItems = from item in serviceItems where !taggedServiceItems.Contains(item.SKU) select item;

goric · Accepted Answer · 2012-05-04 14:24:58Z

8

You can do something like this with Contains:

var result = from item in ServiceItem
    where !TaggedServiceItems.Contains(item.SKU)
    select item;

answered May 4, 2012 at 14:24

goric

12k9 gold badges56 silver badges72 bronze badges

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

Comments

Likurg · Accepted Answer · 2012-05-04 14:37:29Z

1

Try this

var Catalog_ServiceItem = ds.Tables["Catalog.ServiceItem"].AsEnumerable();
var Catalog_TaggedServiceItems = ds.Tables["Catalog.TaggedServiceItems"].AsEnumerable();
var _except = from c in Catalog_ServiceItem 
                   join b in Catalog_TaggedServiceItems 
                     on c.Field<string>("ServiceItemSKU ") equals b.Field<string>("ServiceItemSKU ") into j
                   from x in j.DefaultIfEmpty()
                   where x == null
                   select c;

EDIT

You can do this

var _except = Catalog_ServiceItem.Except(Catalog_TaggedServiceItems);

answered May 4, 2012 at 14:37

Likurg

2,76019 silver badges22 bronze badges

1 Comment

Nanu Over a year ago

I like using the EXCEPT, its only one line, leaves the code neat and clean . Thanks !!

Meysam Savameri · Accepted Answer · 2012-05-04 14:38:55Z

0

use lambda.

dim query=Catalog.ServiceItem.where(function(q) not q.Contains(q.SKU))

answered May 4, 2012 at 14:38

Meysam Savameri

5562 gold badges13 silver badges33 bronze badges

Comments

Vince Iovacchini · Accepted Answer · 2012-05-04 14:33:47Z

-1

Try this:

var untaggedItems = 
  from i in Catalog.ServiceItem
  let taggedItems = (
    from ti = Catalog.TaggedServiceItems
    select ti.ServiceItemSKU ).Distinct()
  where ! taggedItems.Contains( i.SKU )
  select i;

answered May 4, 2012 at 14:33

Vince Iovacchini

151 bronze badge

Collectives™ on Stack Overflow

Select from two tables using "NOT IN" in LINQ in Entity Framework

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related