c# - 将SQL转换为lambda

嗨，大家好，我很想将此SQL语句转换为Lambda表达式

select Employee.FName,Report.ReportDate,Report.SentDate
from Report
Inner join Employee
on Report.UserId = Employee.UserId
where Employee.UserId in (select UserId from UserInUser where AssignId =3333333) and
Report.Status='Sent'

我得到了LINQ作为

from Report in db.Report
join Employee in db.Employee on Report.UserId equals Employee.UserId
where
    (from UserInUser in db.UserInUser
    where
      UserInUser.AssignId == 3333333
    select new {
      UserInUser.UserId
    }).Contains(new { UserId = ()Employee.UserId }) &&
  Report.Status == "Sent"
select new {
  Employee.FName,
  Report.ReportDate,
  Report.SentDate
}

但是我需要它作为Lambda表达式用于我的实体框架查询...

任何协助表示赞赏！

最佳答案

1）您是否有充分的理由混合使用where in和inner join？

2）EF应该采用查询语法。无论如何，它基本上只是表达语法的糖：

var query1 =
    from report in db.Report
    from employee in db.Employee
    from uiu in db.UserInUser
    where report.UserId == employee.UserId
    where uiu.UserId == employee.UserId
    where uiu.AssignId == 3333333
    where report.Status == "Sent"
    select new
    {
        employee.FName,
        report.ReportDate,
        report.SentDate,
    };

或者，如果您更喜欢显式联接而不是多个from子句：-

var query2 =
    from report in db.Report
    join employee in db.Employee on report.UserId equals employee.UserId
    join uiu in db.UserInUser on report.UserId equals uiu.UserId
    where uiu.AssignId == 3333333
    where report.Status == "Sent"
    select new
    {
        employee.FName,
        report.ReportDate,
        report.SentDate,
    };

或者您真的想使用表达式语法：

var query3 =
    db.Report.SelectMany(report => db.Employee, (report, employee) => new {report, employee})
             .SelectMany(@t => db.UserInUser, (@t, uiu) => new {@t, uiu})
             .Where(@t => @[email protected] == @[email protected])
             .Where(@t => @t.uiu.UserId == @[email protected])
             .Where(@t => @t.uiu.AssignId == 3333333)
             .Where(@t => @[email protected] == "Sent")
             .Select(@t => new
             {
                 @[email protected],
                 @[email protected],
                 @[email protected],
             });

或者，如果您真的想使用表达式语法，并且更喜欢Join而不是SelectMany：-

var query4 =
    db.Report.Join(db.Employee,
                   report => report.UserId,
                   employee => employee.UserId,
                   (report, employee) => new {report, employee})
             .Join(db.UserInUser,
                   @t => @t.report.UserId,
                   uiu => uiu.UserId,
                   (@t, uiu) => new {@t, uiu})
             .Where(@t => @t.uiu.AssignId == 3333333)
             .Where(@t => @[email protected] == "Sent")
             .Select(@t => new
             {
                 @[email protected],
                 @[email protected],
                 @[email protected],
             });

3）如果您确实要使用子选择，则可以执行以下操作：

var query5 =
    from report in db.Report
    join employee in db.Employee on report.UserId equals employee.UserId
    where db.UserInUser.Any(x => x.AssignId == 3333333 && x.UserId == employee.UserId)
    where report.Status == "Sent"
    select new
    {
        employee.FName,
        report.ReportDate,
        report.SentDate
    };

或者再次，如果您更喜欢表达式语法：

var query6 =
    db.Report.Join(db.Employee,
                   report => report.UserId,
                   employee => employee.UserId,
                   (report, employee) => new {report, employee})
             .Where(@t => db.UserInUser.Any(x => x.AssignId == 3333333 && x.UserId == @t.employee.UserId))
             .Where(@t => @t.report.Status == "Sent")
             .Select(@t => new
             {
               @t.employee.FName,
               @t.report.ReportDate,
               @t.report.SentDate
             });

小心点对于数据库中的哪些列是唯一的，哪些不是，我做了一些（合理的）假设。如果我的假设是错误的，那么这些查询可能并不等同，并且可能会给您不正确的结果。

就我个人而言，我倾向于混合并匹配查询/表达式语法，具体取决于我认为在给定查询中最易读的语法（在本例中为query1 imo，但是ymmv！）。但是，您的组织可能有在这种情况下应遵循的特定代码标准。例如。我在atm工作的地方总是喜欢query4的样式。

关于c# - 将SQL转换为lambda，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29961860/