使用 let 变量和子查询重构 LINQ to Entities 查询

本文关键字：查询 重构 LINQ to Entities let 变量 使用 | 更新日期: 2023-09-27 17:56:02

我希望能够以某种方式分解以下代码：

return from e in _context.Employees
       let HasWatchedAllVideos = 
       (
           from ev in _context.EmployeeVideos
           where ev.EmployeeId == e.Id && ev.EndTime.HasValue
           select ev.Id
       ).Count() == _context.Videos.Count()
       let EndTime = HasWatchedAllVideos ?
       (
           from ev in _context.EmployeeVideos
           where ev.EmployeeId == e.Id
           select ev.EndTime
       ).Max() : null
       let StartTime =
       (
           from ev in _context.EmployeeVideos
           where ev.EmployeeId == e.Id
           select ev.StartTime
       ).Min()
       select new EmployeeListItem
       {
           Id = e.Id,
           FirstName = e.FirstName,
           LastName = e.LastName,
           Company = e.Company,
           HasWatchedAllVideos = HasWatchedAllVideos,
           StartTime = StartTime,
           EndTime = EndTime
       };

例如，我正在寻找一种方法来分解：

let HasWatchedAllVideos = 
(
    from ev in _context.EmployeeVideos
    where ev.EmployeeId == e.Id && ev.EndTime.HasValue
    select ev.Id
).Count() == _context.Videos.Count()

出于可重用性目的，进入一个单独的方法，但我无法确切地弄清楚如何做到这一点。我试过：

private bool HasWatchedAllVideos(int employeeId)
{
    return (from ev in _context.EmployeeVideos
            where ev.EmployeeId == employeeId && ev.EndTime.HasValue
            select ev.Id
            ).Count() == _context.Videos.Count();
}

这给了我最喜欢的"LINQ to Entities无法识别该方法"异常。

使用 let 变量和子查询重构 LINQ to Entities 查询

// don't count every time
var totalCount = _context.Videos.Count();
from e in _context.Employees
let HasWatchedAllVideos =
    totalCount ==
    _context.EmployeeVideos.Count(ev => ev.EmployeeId == e.Id && ev.EndTime.HasValue)
// count just once per employee
let employeeVideos =  _context.EmployeeVideos.Count(ev => ev.EmployeeId == e.Id)
let EndTime = HasWatchedAllVideos ? employeeVideos.Max() : null
let StartTime =  HasWatchedAllVideos ? employeeVideos.Min() : null
select new EmployeeListItem
{
    Id = e.Id,
    FirstName = e.FirstName,
    LastName = e.LastName,
    Company = e.Company,
    HasWatchedAllVideos = HasWatchedAllVideos,
    StartTime = StartTime,
    EndTime = EndTime
};

这个问题可能不会得到很多行动，所以我发布了一个相关的问题，帮助我尝试找到更好的解决方案：

重构 LINQ IQueryable 表达式以删除查询的重复部分

以下是我特定解决方案变体的代码：

public class AdaTrainingService : ADATraining.Web.Models.IAdaTrainingService, IDisposable
{
    private ADATrainingEntities _context = new ADATrainingEntities();
    public IQueryable<EmployeeListItem> GetEmployeeListing()
    {
        return from e in _context.Employees
               join evsws in EmployeeVideoAggregatesView() on e.Id equals evsws.EmployeeId
               select new EmployeeListItem
               {
                   Id = e.Id,
                   FirstName = e.FirstName,
                   LastName = e.LastName,
                   Company = e.Company,
                   HasWatchedAllVideos = evsws.HasWatchedAllVideos,
                   StartTime = evsws.StartTime,
                   EndTime = evsws.EndTime
               };
    }
    private class EmployeeVideoSeriesWatchingStats
    {
        public int EmployeeId { get; set; }
        public DateTime? StartTime { get; set; }
        public DateTime? EndTime { get; set; }
        public bool HasWatchedAllVideos { get; set; }
    }
    private IQueryable<EmployeeVideoSeriesWatchingStats> EmployeeVideoAggregatesView()
    {
        return from ev in _context.EmployeeVideos
               group ev by ev.EmployeeId into myGroup
               select new EmployeeVideoSeriesWatchingStats
               {
                   EmployeeId = myGroup.Key,
                   StartTime = myGroup.Min( x => x.StartTime),
                   EndTime = myGroup.Max( x => x.EndTime),
                   HasWatchedAllVideos = myGroup.Count() ==  _context.Videos.Count()
               };
    }   
    public void Dispose()
    {
        _context.Dispose();
    }
}
--

更新 5/13/2011 --

上面的示例执行内部联接，不适用于您希望包含所有员工的实例，即使 EmployeeVideoAggregatesView（） 未返回任何结果，因此为了允许左外部联接，我不得不稍微调整代码：

public IQueryable<EmployeeDetails> GetEmployeeListing()
{
    return from e in _context.Employees
           join evsws in EmployeeVideoAggregatesView() on e.Id equals evsws.EmployeeId into myJoin
           from mj in myJoin.DefaultIfEmpty()
           select new EmployeeDetails
           {
               Id = e.Id,
               FirstName = e.FirstName,
               LastName = e.LastName,
               Company = e.Company,
               BadgeNumber = e.BadgeNumber,
               Title = e.Title,
               HasWatchedAllVideos = (mj.HasWatchedAllVideos == null) ? false : mj.HasWatchedAllVideos,
               StartTime = mj.StartTime,
               EndTime = mj.EndTime
           };
}
相关文章：