如何在EF查询中基于Hour筛选进一步返回营业的Place实体

我知道我的问题表述有些模糊，因为暂时找不到更清晰的提问方式，但确定这并非难以解决的问题。

具体场景如下：
我需要在实体仓储的GetAllPlaces方法中返回List<Place>集合。

Place实体定义 #

public Guid PlaceId { get; set; }
public string Name { get; set; }
public Point Location { get; set; }
public bool IsOpen { get; set; }
public List<Event> Events { get; set; }
public List<Hour> Hours { get; set; }

Hour实体定义 #

public Guid HourId { get; set; }
public Guid DayOfTheWeekId { get; set; }
public DayOfTheWeek DayOfTheWeek { get; set; }
public DateTime OpenHour { get; set; }
public DateTime CloseHour { get; set; }
public Guid PlaceId { get; set; }
public Place Place { get; set; }

每个Place实体包含List<Hour>属性，我希望通过筛选该列表，不向调用方返回当前处于关闭状态的场所。目前已实现的逻辑是：仅包含该Place所在时区当天对应的Hour数据：

public async Task<IReadOnlyList<Place>>
            GetAllPlacesAsync(
            double lat,
            double lon,
            string userCity,
            double visibleRadius)
        {    
            var geometryFactory =
            NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
        var userLocation = geometryFactory
            .CreatePoint(new Coordinate(lon, lat));

            var places = await context.Places
                .AsNoTracking()

                .Include(s => s.Hours
                    // 此处筛选出前文所述的当天Hour数据
                    .Where(d => d.DayOfTheWeek.DayName
                        == TimeZoneInfo
                        .ConvertTime(DateTime.Now,
                            TimeZoneInfo
                            .FindSystemTimeZoneById(d.Place.Timezone.Name))
                            .DayOfWeek
                            .ToString()).FirstOrDefault())
                // 第二个.Where()会筛选Include的数据或places集合，但无法针对Hours进行过滤
                // 我希望实现的逻辑是：
                // 如果Place.Hour.Open <= 场所时区当前时间
                // && Place.Hour.Close >= 场所时区当前时间，则保留，否则排除

                .Where(
            x => x.Address.City == userCity
            && x.Location.IsWithinDistance(userLocation, visibleRadius))
                .Distinct()
                .ToListAsync();

            return places;
        }

请问如何进一步筛选，仅保留其当天所在时区营业时间内的Place实体？

编辑1 #

感谢@TN的解答，我编写了如下SQL存储过程：

CREATE PROCEDURE spUpdateIsOpenAndSelectForMap
@UserLat DOUBLE PRECISION,
@UserLon DOUBLE PRECISION,
@PlaceLat DOUBLE PRECISION,
@PlaceLon DOUBLE PRECISION,
@UserCity NVARCHAR(100),
@VisibleRadius DOUBLE PRECISION,
@PlaceToUserDistance DOUBLE PRECISION,
@UserLocation GEOGRAPHY,
@PlaceLocation GEOGRAPHY
AS
BEGIN

SET NOCOUNT ON;

DECLARE @SRID INT;
SET @SRID = 4326; -- 单位：米

-- 创建用户位置和场所位置的地理信息，以及两者间的距离
-- 后续返回距离小于可见半径的场所
SET @UserLocation = geography::Point(@UserLat, @UserLon, @SRID);
SET @PlaceLocation = geography::Point(@PlaceLat, @PlaceLon, @SRID);
SET @PlaceToUserDistance = @UserLocation.STDistance(@PlaceLocation)

-- 待办事项
-- 创建新表PlacePinOnMap

-- 更新IsOpen属性
UPDATE [P]
SET [IsOpen] = [OpenTimeCalc].[IsOpen]

-- 当用户执行该存储过程并更新IsOpen属性后，返回包含更新数据的结果集
-- 用于在代码中转换为DTO
OUTPUT [P].[PlaceId],
       [P].[Location],
       [P].[Latitude],
       [P].[Longitude],
       INSERTED.[IsOpen],
       [M].[Name],
       [E].[EC]

FROM [Places] [P]
JOIN [Timezones] [TZ] ON [TZ].[TimezoneId] = [P].[TimezoneId]
CROSS APPLY (
    -- 获取场所时区的本地日期时间
    SELECT
        GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE [TZ].[Name] AS [LocalDateTime]
) [LT]
CROSS APPLY (
    -- 获取场所时区的本地星期名称和本地时间
    SELECT
        DATENAME(WEEKDAY, [LT].[LocalDateTime]) AS [LocalWeekdayName],
        CAST([LT].[LocalDateTime] AS [TIME]) AS [LocalTime]
) [DT]
CROSS APPLY (
    -- 获取未来事件的数量
    SELECT
        COUNT(*)
        FROM [Events] [E]
        WHERE [E].[Date] >= [LT].[LocalDateTime]
) [EC]
JOIN [DaysOfTheWeek] [DOW] ON [DOW].[DayName] = [DT].[LocalWeekdayName]
JOIN [Moods] AS [M] ON [P].[MoodId] = [M].[MoodId]
-- 同时获取与场所关联的事件
JOIN [Events] AS [E] ON [P].[PlaceId] = [E].[PlaceId]
CROSS APPLY (
    -- 筛选出本地时间处于当日营业时间内的场所
    SELECT CASE WHEN EXISTS (
        SELECT *
        FROM [StoreHours] [SH]
        WHERE [SH].[PlaceId] = [P].[PlaceId]
        AND [SH].[DayOfTheWeekId] = [DOW].[DayOfTheWeekId]
        AND [SH].[OpenHour] <= [DT].[LocalTime]
        -- 特殊情况：24:00的关门时间存储为00:00，因为TIME类型不支持24:00
        AND ([SH].[CloseHour] > [DT].[LocalTime] OR [SH].[CloseHour] = '00:00')
    ) THEN 1 ELSE 0 END AS [IsOpen]
) [OpenTimeCalc]
WHERE ([P].[IsOpen] <> [OpenTimeCalc].[IsOpen])
AND ([P].[Address].[City] = @UserCity)
AND (@PlaceToUserDistance <= @VisibleRadius);

END

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