从 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.cust

var 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();