How to further filter on a query's .Include(.Where()) and have it reflected outside of the Include()

Question

I understand that my question is kind of confusing because I haven't found a better way to ask it, but I'm sure it isn't a hard problem to solve.

Here's what's happening:

I'm supposed to return a List<Place> inside a method called GetAllPlaces in the entity's repository.

Place entity:

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 entity:

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; }

Each Place have a List<Hour> property. I'm trying to filter on this list of hours to not return places that are closed to the caller of this method. What I have so far is that I'm filtering to only include the Place's today's Hour in the Place's timezone:

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
                    // here I'm filtering to get just today's Hour like explained previously
                    .Where(d => d.DayOfTheWeek.DayName
                        == TimeZoneInfo
                        .ConvertTime(DateTime.Now,
                            TimeZoneInfo
                            .FindSystemTimeZoneById(d.Place.Timezone.Name))
                            .DayOfWeek
                            .ToString()).FirstOrDefault())
                // a second .Where() would filter on the .Include()
                // or on the "places" List but not on its Hours.
                // How to further filter to do something like this:
                // if Place.Hour.Open <= timeNowInPlaceTimezone
                // && Place.Hour.Close >= timeNowInPlaceTimezone ? passToList : dontPassToList

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

            return places;
        }

Do you know how I could filter it to only get the places where the Hour of the place is between the open and close hour of today in its timezone ?

EDIT 1

So, thanks to @TN's answers, here's the SQL I wrote:

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; -- in meters

-- Create geolocation for the user's location and the Place's location
-- as well as the distance between the user and the Place in order
-- to then return the places where the distance between the two
-- is smaller than the visible radius.
SET @UserLocation = geography::Point(@UserLat, @UserLon, @SRID);
SET @PlaceLocation = geography::Point(@PlaceLat, @PlaceLon, @SRID);
SET @PlaceToUserDistance = @UserLocation.STDistance(@PlaceLocation)

-- TO DO
-- create the new table PlacePinOnMap

-- Update the IsOpen property
UPDATE [P]
SET [IsOpen] = [OpenTimeCalc].[IsOpen]

-- when the user runs this stored procedure and updates the IsOpen
-- property, it returns a "new table" with the updated data
-- to be put into a DTO in the code.
OUTPUT [P].[PlaceId],
       [P].[Location],
       [P].[Latitude],
       [P].[Longitude],
       INSERTED.[P].[IsOpen],
       [M].[Name],
       [E].[EC]

FROM [Places] [P]
JOIN [Timezones] [TZ] ON [TZ].[TimezoneId] = [P].[TimezoneId]
CROSS APPLY (
    -- Get the Place's timezone's local datetime
    SELECT
        GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE [TZ].[Name] AS [LocalDateTime]
) [LT]
CROSS APPLY (
    -- Get the Place's timezone's local week-day name and local time
    SELECT
        DATENAME(WEEKDAY, [LT].[LocalDateTime]) AS [LocalWeekdayName],
        CAST([LT].[LocalDateTime] AS [TIME]) AS [LocalTime]
) [DT]
CROSS APPLY (
    -- Get the event count of event that are in the future
    SELECT
        COUNT(*)
        FROM [E]
        WHERE [E].[Date] >= [LT].[LocalDateTime]
) [EC]
JOIN [DaysOfTheWeek] [DOW] ON [DOW].[DayName] = [DT].[LocalWeekdayName]
JOIN [Moods] AS [M] ON [P].[MoodId] = [M].[MoodId]
-- Also wanted to get the events related to the place
JOIN [Events] AS [E] ON [P].[PlaceId] = [E].[PlaceId]
CROSS APPLY (
    -- select place where its open and closed hours are within
    -- the ones of the local time, for the day returned from the local datetime
    SELECT CASE WHEN EXISTS (
        SELECT *
        FROM [StoreHours] [SH]
        WHERE [SH].[PlaceId] = [P].[PlaceId]
        AND [SH].[DayOfTheWeekId] = [DOW].[DayOfTheWeekId]
        AND [SH].[OpenHour] <= [DT].[LocalTime]
        -- special case where closing time of '24:00' is stored as '00:00',
        -- since time does not support '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

Answer 1

For efficiency, it may be better to perform all of the filtering on the database side using native SQL to convert the date/times and apply the open/close criteria. This would avoid loading the entire Place table and likely the entire StoreHours table into the C# environment with every execution.

The stored procedure would look something like:

CREATE PROCEDURE GetOpenPlaces
AS
    SELECT P.*
    FROM Place P
    JOIN Timezone TZ ON TZ.TimezoneId = P.TimezoneId
    CROSS APPLY (
        -- Intermediate local time calculations
        SELECT
            GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE TZ.Name AS LocalDateTime
    ) LT
    CROSS APPLY (
        -- More intermediate local time calculations
        SELECT
            DATENAME(weekday, LT.LocalDateTime) AS LocalWeekdayName,
            CAST(LT.LocalDateTime AS TIME) AS LocalTime
    ) DT
    JOIN DayOfTheWeek DOW ON DOW.DayName = DT.LocalWeekdayName
    WHERE EXISTS (
        SELECT *
        FROM StoreHour H
        WHERE H.PlaceId = P.PlaceId
        AND H.DayOfTheWeekId = DOW.DayOfTheWeekId
        AND H.OpenHour <= DT.LocalTime
        AND (H.CloseHour > DT.LocalTime OR H.CloseHour = '00:00')
    )

The above supports a special case where a closing time of '24:00' is stored as '00:00', since time does not support '24:00'

See this db<>fiddle for a demo that includes a variety of generated test data and a slightly modified version of the query above for illustration purposes.

This all assumes that your time zone names match those known to SQL Server. I believe that SQL server time zone information and .Net TimeZoneInfo are both based off if operating system registry data, this should be a valid assumption as long as updates are regularly applied.

Also, I recommend that you define an index on StoreHour(PlaceId) as a minimum, or (better) StoreHour(PlaceId, DayOfTheWeekId, OpenHour, CloseHour).

Answer 2

Not sure about your condition about Open/Close hours, but query should look like this:

public async Task<IReadOnlyList<Place>>  GetAllPlacesAsync()
{    
    var dayOfWeek = TimeZoneInfo.ConvertTime(DateTime.Now, TimeZoneInfo .FindSystemTimeZoneById(d.Place.Timezone.Name))
        .DayOfWeek
        .ToString();

    var places = await context.Places
        .AsNoTracking()
        .Where(p => p.Hours.Any(h =>
                h.DayOfTheWeek.DayName == dayOfWeek 
                && h.Open <= timeNowInPlaceTimezone
                && h.Close >= timeNowInPlaceTimezone
            )
        )
        .ToListAsync();

    return places;
}

Answer 3

First just to clarify my understanding or your data: Each place has a List<Hour> collection where each Hour object has properties DayOfTheWeek, Open, and Close - similar to a line item on a store hours sign. Also, each place has an associated timezone to which the current (UTC) date/time needs to be converted before checking against the place.Hours collection.

I do not believe .Include() is what you want here, since that function is for selecting extra data to be eagerly loaded with the result rather than to filter the result.

My expectation is that you want something like .Where(place => place.Hours.Any(hour => hours-matches-local-time).

However, the hours-matches-local-time part may include complex calculations that may be unnecessarily evaluated inside the .Any() multiple times for the same place. To avoid redundant calculations, the condition in the outer .Where() can be made into a code block where the local day-of-week and local-time are calculated once per place, assigned to variables, and then referenced repeatedly inside the inner .Any().

Try something like:

var places = await context.Places
    .AsNoTracking()
    .Where(place => {
        var localDateTime = ...;
        var dayOfWeekInPlaceTimezone = ...;
        var timeNowInPlaceTimezone  = ...;
        bool isOpen = place.Hours
            .Any(hour =>
                hour.DayOfTheWeek == dayOfWeekInPlaceTimezone
                && hour.Open <= timeNowInPlaceTimezone
                && hour.Close > timeNowInPlaceTimezone
            );
        return isOpen;
    })
    //(not needed) .Distinct() 
    .ToListAsync();

The above uses LINQ Method syntax. An alternative is to use the LINQ Query syntax which can also calculate local variables using the let clause. This post has answers that demonstrate some of those techniques. (Perhaps someone with more experience than I in LINQ query syntax can post a translation.)

There may be other opportunities for improvement, such as grouping places by timezone and calculating local day/time once per group. A .SelectMany() would eventually be used to process each group and flatten out the results. The result might be something like:

var places = await context.Places
    .AsNoTracking()
    .GroupBy(place => place.Timezone)
    .SelectMany(tzGroup => {
        var timezone = tzGroup.Key;
        var localDateTime = ...;
        var dayOfWeekInPlaceTimezone = ...;
        var timeNowInPlaceTimezone = ...;
        return tzGroup.Where(place =>
            place.Hours.Any(hour =>
                hour.DayOfTheWeek == dayOfWeekInPlaceTimezone
                && hour.Open <= timeNowInPlaceTimezone
                && hour.Close > timeNowInPlaceTimezone
            )
        );
    })
    //(not needed) .Distinct() 
    .ToListAsync();