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!

Answer 1

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();