Join and Union with Entity Framework

Question

I have two tables (clients and emails), one with customer's data, including a main email address, and another one with additional email addresses.

I need to validate the user from one of their many email addresses, no matter if it is on clients or emails table. I've come up with this SQL sentence which works fine:

set @email = '[email protected]';
select c1.credits > 0 as Allowed, c1.Email as MainEmail from 
customers c1 inner join (select ClientId,  Email FROM customers WHERE 
Email=@email union all select ClientId, Email FROM emails WHERE Email=@email) e1 
on c1.ClientId = e1.ClientId;

How to write this query in LINQ to Entities with method-based syntax?

Answer 1

If i understand correctly,

Customer may or may not have the email (Additional) in emails table. Also, Customer have more than one additional emails entry in emails table. Like below

List<Customer> customers = new List<Customer> 
{ 
    new Customer { ClientId = 1, Email = "[email protected]", Credits = 2 },
    new Customer { ClientId = 2, Email = "[email protected]", Credits = 1 },
    new Customer { ClientId = 3, Email = "[email protected]", Credits = 1 },
};

List<Emails> emails = new List<Emails> 
{ 
    new Emails { ClientId = 1, Email = "[email protected]" },
    new Emails { ClientId = 1, Email = "[email protected]" },
    new Emails { ClientId = 2, Email = "[email protected]" },
};

In that case, Use the below query to get it done,

var result = from c in customers
             let _emails = emails.Where(e => c.ClientId == e.ClientId).Select(t => t.Email)
             where c.Email == "[email protected]" || _emails.Contains("[email protected]")
             select new
             {
                 Allowed = c.Credits > 0,
                 MainEmail = c.Email
             };

I hope it helps you.