如何将Entity Framework查询改写为SQL Server的T-SQL查询

以下是将你提供的EF Core逻辑转换后的T-SQL查询，我们使用表值参数传递儿童年龄集合（也可替换为临时表），完整实现筛选、统计及价格计算逻辑：

1. 创建表值参数类型（只需执行一次） #

CREATE TYPE dbo.AgeList AS TABLE (Age INT);

2. 主查询代码 #

-- 定义查询参数
DECLARE @FromDate DATETIME = '2024-01-01', -- 替换为实际起始日期
        @ToDate DATETIME = '2024-01-31',   -- 替换为实际结束日期
        @CityId UNIQUEIDENTIFIER = 'xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx', -- 替换为实际城市ID
        @AdultCount INT = 2; -- 替换为实际成人数量

-- 传入儿童年龄集合（示例数据，替换为实际年龄列表）
DECLARE @ChildAges dbo.AgeList;
INSERT INTO @ChildAges (Age)
VALUES (1), (5), (10);

-- CTE统计每个Search记录的乘客分类数量
WITH SearchPassengerStats AS (
    SELECT
        s.Id, -- 若Search表无主键Id，可改用VendorHotelRoomId等唯一标识
        s.CityId,
        s.CountryId,
        s.HotelId,
        s.ProvinceId,
        s.VendorHotelRoomId,
        s.BasePrice,
        s.ExtraAdultPrice,
        s.ExtraInfantPrice,
        s.ExtraChild1Price,
        s.ExtraChild2Price,
        s.MaxInfantAge,
        s.MaxChild1Age,
        s.MaxChild2Age,
        -- 统计婴儿数量
        SUM(CASE 
                WHEN ca.Age <= s.MaxInfantAge 
                     AND ca.Age < s.MaxChild1Age 
                     AND ca.Age < s.MaxChild2Age THEN 1 
                ELSE 0 
            END) AS InfantCount,
        -- 统计Child1数量
        SUM(CASE 
                WHEN ca.Age > s.MaxInfantAge 
                     AND ca.Age <= s.MaxChild1Age THEN 1 
                ELSE 0 
            END) AS Child1Count,
        -- 统计Child2数量
        SUM(CASE 
                WHEN ca.Age > s.MaxChild1Age 
                     AND ca.Age <= s.MaxChild2Age THEN 1 
                ELSE 0 
            END) AS Child2Count,
        -- 按原EF逻辑，ExtraAdult数量与Child2Count一致
        SUM(CASE 
                WHEN ca.Age > s.MaxChild1Age 
                     AND ca.Age <= s.MaxChild2Age THEN 1 
                ELSE 0 
            END) AS ExtraAdultCount
    FROM dbo.Search s
    CROSS JOIN @ChildAges ca
    WHERE s.Date BETWEEN @FromDate AND @ToDate
      AND s.CityId = @CityId
      AND s.ExtraCapacity >= @AdultCount
    GROUP BY s.Id, s.CityId, s.CountryId, s.HotelId, s.ProvinceId, s.VendorHotelRoomId,
             s.BasePrice, s.ExtraAdultPrice, s.ExtraInfantPrice, s.ExtraChild1Price, s.ExtraChild2Price,
             s.MaxInfantAge, s.MaxChild1Age, s.MaxChild2Age
    
    -- 处理无儿童的场景（若无此需求可删除该UNION ALL分支）
    UNION ALL
    SELECT
        s.Id,
        s.CityId,
        s.CountryId,
        s.HotelId,
        s.ProvinceId,
        s.VendorHotelRoomId,
        s.BasePrice,
        s.ExtraAdultPrice,
        s.ExtraInfantPrice,
        s.ExtraChild1Price,
        s.ExtraChild2Price,
        s.MaxInfantAge,
        s.MaxChild1Age,
        s.MaxChild2Age,
        0 AS InfantCount,
        0 AS Child1Count,
        0 AS Child2Count,
        0 AS ExtraAdultCount
    FROM dbo.Search s
    WHERE s.Date BETWEEN @FromDate AND @ToDate
      AND s.CityId = @CityId
      AND s.ExtraCapacity >= @AdultCount
      AND NOT EXISTS (SELECT 1 FROM @ChildAges)
)
-- 筛选符合容量限制的记录并计算最终价格
SELECT
    CityId,
    CountryId,
    HotelId,
    ProvinceId,
    VendorHotelRoomId,
    (ExtraAdultCount * ExtraAdultPrice) +
    (InfantCount * ExtraInfantPrice) +
    (Child1Count * ExtraChild1Price) +
    (Child2Count * ExtraChild2Price) + -- 原EF代码此处误写为child1Count，已修正为Child2Count，若原逻辑确实如此可改回
    BasePrice AS Price
FROM SearchPassengerStats
WHERE MaxInfantAge >= InfantCount
  AND MaxChild1Age >= Child1Count
  AND MaxChild2Age >= Child2Count;

关键说明 #

• 原EF代码中计算Child2价格时错误使用了child1Count，T-SQL中已修正为Child2Count，若原业务逻辑确实如此可手动改回。
• 若Search表没有主键Id，可改用VendorHotelRoomId等唯一字段进行分组。
• 可将表值参数替换为临时表（如#ChildAges），适合一次性查询场景。

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