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

I was working on a project with User and Costumer identificação. The first is generated by the web app, and the second by the business department.

So I had to develop a logic to make those match between identifications.

The User entity needs to know the Costumer identification and, in order to make the match, first I selected the users without a costumer and extracted a list of emails. Then, I selected the customers based on this email list. This statement can be transcribed to SQL syntax, like this:

SELECT *
FROM Customers
WHERE email IN (SELECT email FROM Users WHERE CustomerId IS NULL)

But how to translate the SQL to C# LINQ API?

First, make the selection from Users, extracting the list of emails
Select the Customers with those emails

This way:

var emails = _userManager.Users
    .Where(user => user.CustomerId == null)
    .Select(user => user.Email) // extract the emails from users
    .ToList();

var customers = _applicationRepository.GetCustomers()
    .Where(customer => emails.Contains(customer.Email)) // the Contains method carry the IN logic when translated to SQL script
    .ToList();

Now we have the match programmatically.

That's it!

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

Reference