EF6合并多个LINQ查询报错求助

报错原因 #

• 多余的Include调用：子查询中Include(x => x.Room)完全没必要——你只需要获取Room.Id来过滤，Include是用于将关联实体加载到内存的操作，EF6无法将其转换为SQL存储表达式，这是直接触发NotSupportedException的原因。
• 错误使用AsEnumerable()：AsEnumerable()会将Reservations的查询结果提前拉到内存中，导致外层Rooms的数据库查询无法与内存集合整合，EF无法生成有效的SQL语句。

正确的LINQ写法（模拟SQL IN子查询） #

写法1：使用NOT IN逻辑 #

去掉多余的Include和AsEnumerable()，让整个子查询在数据库端执行：

roomRows = hbContext.Rooms
    .Where(z => !hbContext.Reservations
        .Where(x => 
            (x.FromDate < fromDate && fromDate < x.ToDate) || 
            (x.FromDate < toDate && toDate < x.ToDate) ||
            (fromDate < x.FromDate && x.FromDate < toDate) || 
            (fromDate < x.ToDate && x.ToDate < toDate)
        )
        .Select(x => x.Room.Id)
        .Contains(z.Id)
        && z.MaxNumOfPeople >= numOfPeople)
    .Select(z => new RoomRow { From = fromDate, To = toDate, Room = z })
    .ToList();

写法2：使用NOT EXISTS逻辑（更高效） #

SQL中NOT EXISTS比NOT IN性能更优，尤其是当子查询可能返回空值时，用EF的Any方法实现：

roomRows = hbContext.Rooms
    .Where(z => z.MaxNumOfPeople >= numOfPeople
        && !hbContext.Reservations.Any(x => 
            x.Room.Id == z.Id
            && (
                (x.FromDate < fromDate && fromDate < x.ToDate) || 
                (x.FromDate < toDate && toDate < x.ToDate) ||
                (fromDate < x.FromDate && x.FromDate < toDate) || 
                (fromDate < x.ToDate && x.ToDate < toDate)
            )
        ))
    .Select(z => new RoomRow { From = fromDate, To = toDate, Room = z })
    .ToList();

优化建议：直接使用外键字段 #

如果你的Reservation实体定义了RoomId外键属性（而非仅依赖导航属性Room），直接用x.RoomId代替x.Room.Id，可以避免SQL中不必要的表关联，提升查询性能：

roomRows = hbContext.Rooms
    .Where(z => z.MaxNumOfPeople >= numOfPeople
        && !hbContext.Reservations.Any(x => 
            x.RoomId == z.Id
            && (
                (x.FromDate < fromDate && fromDate < x.ToDate) || 
                (x.FromDate < toDate && toDate < x.ToDate) ||
                (fromDate < x.FromDate && x.FromDate < toDate) || 
                (fromDate < x.ToDate && x.ToDate < toDate)
            )
        ))
    .Select(z => new RoomRow { From = fromDate, To = toDate, Room = z })
    .ToList();

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