从 sql 转换为 linq 查询

Question

我只想将以下查询转换为LINQ表达式。

Select * 

from customer 

where customer_id in

    (select customer_id from cust where username=name)

Answer 1

所以你有两张桌子：一张Cust桌子和一张Customer桌子。两个表都有一个属性CustomerId。该Cust表也有一个属性UserName。现在给定一个字符串变量name，您希望Customers表中具有CustomerId等于CustomerId的所有元素在Cust表中具有UserName等于的所有元素中name。对我来说似乎是一个加入：var result = myDbContext.Cust             // take the cust table    .Where(cust => cust.UserName == name) // keep only custs with Username == name    .Join(myDbContext.Customers,          // Join the result with Customers table    cust => cust.CustomerId,              // From every cust take the CustomerId    customer => customer.CustomerId,      // From every customer take the CustomerId    (cust, customer) =>                   // when they match take the cust and the Customer       customer                           // to select the matching Customer

Answer 2

假设您可以访问 customer 表dbcontext.customer和 cust 表dbcontext.custvar name = "desired username"var result = dbcontext.customer.Where(customer => dbcontext.cust.Where(cust => cust.username == name).Select(cust => cust.customer_id).Any(cust => cust == customer.customer_id)).ToList();