mysql - 加入和结合 Entity Framework

我有两个表（客户和电子邮件），一个包含客户数据，包括主电子邮件地址，另一个包含其他电子邮件地址。
我需要从他们众多电子邮件地址中的一个验证用户，不管它是在客户机上还是在电子邮件表上。我想出了一个很好的SQL语句：

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;

如何用基于方法的语法在LINQ中将此查询写入实体？

最佳答案

如果我理解正确，
客户可能在emails表中有或没有电子邮件（附加）。
此外，客户在emails表中还有多个电子邮件条目。如下所示

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]" },
};

在这种情况下，使用下面的查询来完成它，

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
             };

希望对你有帮助。

关于mysql - 加入和结合 Entity Framework ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13377997/