CODEWITHSUNDEEP
c#linqentity-framework
M.Azad
M.Azad

Reputation: 3763

Get Items in an array that not existed in my database table?

I have a table Products in my database. I have an array that has some Id like

var ids = new[] { 10, 12, 1000, 1100 };

In my table product 10 and 20 do exist, while 1000, 1100 do not exist. How can I write an optimized linq query to get items that do not exist in my table? (1000, 1100)

I think the optimize query is like 

select
    a.y
from
    (select 10 as y
     union all
     select 12 as y
     union all
     select 800 as y
     union all
     select 8000 as y) as a
where
    a.y not in (select p.Id from Products as p);

How can I generate a query like this with linq?

Upvotes: 1

Views: 168

Answers (2)

Uriil
Uriil

Reputation: 12628

This should work:

var ids = new[] { 10, 12, 1000, 1100 };
var result = ids.Except(dbContext.Products.Where(p => ids.Contains(p.ID)).Select(p => p.ID)).ToList();

Upvotes: 2

PrinceT
PrinceT

Reputation: 459

Answer

var ids = new[] { 10, 12, 1000, 1100 };

var result = (from p in dbContext.Products
                      where ids.Contains(p.ID)
                      select p).ToList();

Upvotes: 0

Related Questions