Entity Framework: SELECT From Where ID NOT IN

Question

I know this could be easy question but I spent hours trying to figure it out with no luck!

I want to achieve the following SQL Command in Entity Framework:

SELECT * FROM Table1
WHERE RowID NOT IN (
SELECT SomeID FROM Table2 Where SomeID is not null)

I tried the following (Asp.Net C#):

var SomeIDs = db.Table2.Where(n => n.SomeID != null).Select(x => x.SomeID);
var query = (from a in db.Table1
             where !(SomeIDs.Contains(a.RowID))
             select a;

It works fine in small database, but in production db it takes forever then time out!

Appreciate any help!

Let me guess, in production `SomeIDs` contains many more items. — Gert Arnold, Mar 11 '18 at 16:03
The LINQ query should generate exactly the same `NOT IN` or equivalent `NOT EXISTS` SQL query. Can you show the output of `query.ToString()`? — Ivan Stoev, Mar 11 '18 at 16:11

score 4 · Accepted Answer · answered Mar 11 '18 at 19:43

4

Try using AsNoTracking, it might help you if you are not using the returned objects to update data in database

Table1.where(x => !someIds.conatins(x.id)).AsNoTracking();

answered Mar 11 '18 at 19:43

Mohammad Ali

551
7
17

1

I think your answer is my solution because after I added .AsNoTracking(); It really became much faster even in the live database! Thank you :) – AKO Mar 12 '18 at 05:39
@AKO nice to hear that :) – Mohammad Ali Mar 12 '18 at 06:31

score 1 · Answer 2 · answered Mar 11 '18 at 15:09

Have you tried using .DefaultIfEmpty(), as per this question.

var query = from a in db.Table1
            join b in SomeIDs
                on a.RowID equals b.SomeID into c
            from b in c.DefaultIfEmpty()
            where b == null
            select a;

Entity Framework: SELECT From Where ID NOT IN

2 Answers2