使用日期数组时LINQ表达式无法翻译的问题求助

问题背景 #

需要查询指定楼层（FloorId=1404）的活跃座位，同时判断每个座位在selectedDates集合中的任意日期是否被预订/占用，逻辑对应下方的SQL语句，但原LINQ代码运行时抛出EF Core无法翻译的异常。

原LINQ代码 #

List<DateTime?> selectedDates = new List<DateTime?>();

var sql = _dbContext.Seat
    .Where(s => s.IsActive == true && s.FloorId == 1404)
    .Select(s => new
    {
        s.SeatId,
        s.Name,
        s.radius,
        s.XCoordinate,
        s.YCoordinate,
        s.FloorId,
        BookedFlag =
        selectedDates.Any(sd =>
        _dbContext.SeatBooking.Any(sb => sb.BookedDate == sd) ||
        _dbContext.GuestBookings.Any(gb => gb.StartDate <= sd && gb.EndDate >= sd) ||
        _dbContext.SeatReservation.Any(sr => sr.StartDate <= sd && sr.EndDate >= sd))
    });

目标SQL逻辑 #

SELECT S.seatid,
       S.NAME,
       S.radius,
       S.xcoordinate,
       S.ycoordinate,
       S.floorid,
       CASE
         WHEN EXISTS(SELECT bookedid
                     FROM   seatbooking SB
                     WHERE  S.seatid = SB.seatid
                            AND bookeddate IN( '01-18-2024', '01-19-2024' ))
               OR EXISTS(SELECT *
                         FROM   guestbookings GB
                         WHERE  S.seatid = GB.seatid
                                AND EXISTS(SELECT 1
                                           FROM   (VALUES ('01-18-2024'),
                                                          ('01-19-2024')) AS
                                                  DateList(
                                                  datevalue)
                                           WHERE  DateList.datevalue >=
                                                  GB.startdate
                                                  AND DateList.datevalue <=
                        GB.enddate))
               OR EXISTS(SELECT *
                         FROM   seatreservation SR
                         WHERE  S.seatid = SR.seatid
                                AND EXISTS (SELECT 1
                                            FROM   (VALUES ('01-18-2024'),
                                                           ('01-19-2024')) AS
                                                   DateList(
                                                   datevalue)
                                            WHERE  DateList.datevalue >=
                                                   SR.startdate
                                                   AND DateList.datevalue <=
                                           SR.enddate))THEN 1
         ELSE 0
       END BookedFlag
FROM   seat S
WHERE  S.isactive = 1
       AND S.floorid = 1404 

报错信息 #

The LINQ expression 'sd => DbSet()
.Any(s0 => s0.BookedDate == sd) || DbSet()
.Any(g => g.StartDate <= sd && g.EndDate >= sd) || DbSet()
.Any(s1 => s1.StartDate <= sd && s1.EndDate >= sd)' could not be translated. Either rewrite the query in a form that can be translated,
or switch to client evaluation explicitly by inserting a call to
'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See
https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

解决方案 #

原代码的问题在于在内存集合selectedDates的Any方法中嵌套了数据库表的Any查询，EF Core无法将这种嵌套逻辑正确翻译成SQL。需要调整逻辑顺序，针对每个座位直接检查三个预订表中是否存在关联且日期匹配的记录：

List<DateTime?> selectedDates = new List<DateTime?>();

// 过滤掉null的日期，避免后续判断出错
var validSelectedDates = selectedDates.Where(sd => sd.HasValue).Select(sd => sd.Value).ToList();

var query = _dbContext.Seat
    .Where(s => s.IsActive && s.FloorId == 1404)
    .Select(s => new
    {
        s.SeatId,
        s.Name,
        s.radius,
        s.XCoordinate,
        s.YCoordinate,
        s.FloorId,
        BookedFlag =
        // 检查SeatBooking中是否有当前座位的预订日期在selectedDates中
        _dbContext.SeatBooking.Any(sb => sb.SeatId == s.SeatId && validSelectedDates.Contains(sb.BookedDate))
        // 检查GuestBookings中是否有当前座位的时间段包含selectedDates中的任意日期
        || _dbContext.GuestBookings.Any(gb => gb.SeatId == s.SeatId && validSelectedDates.Any(sd => sd >= gb.StartDate && sd <= gb.EndDate))
        // 检查SeatReservation中是否有当前座位的时间段包含selectedDates中的任意日期
        || _dbContext.SeatReservation.Any(sr => sr.SeatId == s.SeatId && validSelectedDates.Any(sd => sd >= sr.StartDate && sd <= sr.EndDate))
    });

调整说明 #

1. 先过滤selectedDates中的null值，避免后续日期比较出现异常，同时让EF Core更容易翻译；
2. 将原逻辑中“遍历每个日期检查是否有预订”，改为“针对每个座位，检查三个预订表中是否存在匹配当前座位且日期符合条件的记录”；
3. 调整后的逻辑与目标SQL完全对齐，EF Core可以正确翻译成对应的EXISTS子查询，避免客户端评估带来的性能问题。

内容的提问来源于stack exchange，提问作者Vishnu Babu