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 = 'client@domain.com';
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?

I would prefer method syntax but I could use expression syntax as an alternative. — JPG, Nov 14 '12 at 15:57

Prasad Kanaparthi · Accepted Answer · 2012-11-15T16:08:44.863

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 = "client1@domain.com", Credits = 2 },
    new Customer { ClientId = 2, Email = "client2@domain.com", Credits = 1 },
    new Customer { ClientId = 3, Email = "client3@domain.com", Credits = 1 },
};

List<Emails> emails = new List<Emails> 
{ 
    new Emails { ClientId = 1, Email = "client1-2@domain.com" },
    new Emails { ClientId = 1, Email = "client1-3@domain.com" },
    new Emails { ClientId = 2, Email = "client2-1@domain.com" },
};

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 == "client3@domain.com" || _emails.Contains("client3@domain.com")
             select new
             {
                 Allowed = c.Credits > 0,
                 MainEmail = c.Email
             };

I hope it helps you.

edited Nov 15 '12 at 16:08

answered Nov 14 '12 at 15:57

Prasad Kanaparthi

6,423
4
35
62

I will work based on your sample as my real table is more complex. Hopefully tomorrow I will give you an update. Thanks! – JPG Nov 14 '12 at 16:48
I receive this error: LINQ to Entities does not recognize the method 'System.String Join(System.String, System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression. – JPG Nov 14 '12 at 17:08
@JPG, Good catch. i edited my post also see this.. http://stackoverflow.com/questions/4095658/how-do-i-concatenate-strings-in-entity-framework-query – Prasad Kanaparthi Nov 14 '12 at 17:34
Now it says something similar but related to List: LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.String] ToList[String](System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression. – JPG Nov 15 '12 at 11:51
as I only need to validate the user, I've managed to get it working by removing the AliasEmail column in your original code (thus removing the conflicting string.Join). I've updated my question for consistence as well. Please update your answer accordingly to grant you the accepted answer. Thanks! – JPG Nov 15 '12 at 12:45
@JPG, I have Updated the Post (Removed the 'AliasEmail'). Thanks – Prasad Kanaparthi Nov 15 '12 at 12:59
Hi, the .ToList() doesn't work for me. May be you can double check and remove it. I think it is no needed anymore. – JPG Nov 15 '12 at 15:09
@JPG, .ToList() is not required. Good catch. Thanks – Prasad Kanaparthi Nov 15 '12 at 16:09

Join and Union with Entity Framework

1 Answers1