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

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 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()
        {    
            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

                .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 ?

CodePudding user response：

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();

CodePudding user response：

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