Discussion on: Using C# LINQ API to perform SQL IN logic at WHERE clause

[Dian Fay]

I stopped writing C# right as LINQ got popular so I don't know it too well but I believe this does store the list of emails in memory.

In SQL you could do this:

SELECT c.*
FROM customers AS c
JOIN users AS u ON u.email = c.email
WHERE u.customer_id IS NULL

and achieve the same result without a subquery. I don't know how you'd go about translating that to LINQ though.

[Alex Ruzenhack]

It's true! But the tables are in different contexts, which means they are in different databases. So, it's not possible to make a JOIN between them.

[Dian Fay]

My condolences :)

[Rafal Pienkowski]

In my opinion, this is a better approach, which doesn't require an additional call to the database and doesn't require to store entities in the memory (which could have terrible results in case of a huge collection).
And of course, it is available in LINQ.