Linq to Entities Optimizing Part of a Date

Question

I am trying to create a scan based on part of a date field for the admin site control panel (from the interval analysis diagram).

var drilldownQuery = DataManager.DataSessions
            .Include("Location")
            .Include("Quote.Carriers")
            .Include("Drivers")
            .Include("Vehicles")
            .Where(session =>
                session.Timestamp >= Model.FromDate &&
                session.Timestamp < through
            );

        if (!String.IsNullOrWhiteSpace(Model.DrillDown))
        {
            drilldownQuery = drilldownQuery.ToList()
                .Where(session => 
                   IntervalSelector(session) == Model.DrillDown);
        }

    public string IntervalSelector(DataSession session)
    {
        switch (Model.SelectedInterval)
        {
            case TimeInterval.Hourly:
                return session.Timestamp.Hour.ToString("D2");
            case TimeInterval.Weekday:
                return ((int)session.Timestamp.DayOfWeek).ToString();
            case TimeInterval.Weekly:
                return session.Timestamp.Date.AddDays(-(int)session.Timestamp.DayOfWeek).ToString("yyyy/MM/dd");
            case TimeInterval.Monthly:
                return session.Timestamp.Date.ToString("yyyy/MM");
            case TimeInterval.Annual:
                return session.Timestamp.Year.ToString("D4");
            default:
                return session.Timestamp.Date.ToString("yyyy/MM/dd");
        }
    }

Of course, this is very bad with large date ranges. I hope to avoid calling "ToList ()" so that the drilling criteria runs in the database, not in memory. And this is the place where I got stuck, especially when drilling by hourly and everyday criteria.

IntervalSelector is also used to group graph requests, as well as for drilling. I am open to using a separate selector for granularity, which will improve its performance. In the graph request, a ToList is also executed, but the performance is excellent because it does not need to be included.

var graphQuery = DataManager.DataSessions
            .Where(session =>
                session.Timestamp >= Model.FromDate &&
                session.Timestamp < through);

I assume the best route would be to move the switch statement outside of Linq, for example:

switch (Model.SelectedInterval)
{
    case TimeInterval.Hourly:
        int selectedHour = int.Parse(Model.DrillDown);
        drilldownQuery = drilldownQuery
            .Where(session => session.Timestamp.Hour == selectedHour);
        // thoughts on this? any better way?
        break;
    case TimeInterval.Weekday:
        var selectedWeekday = int.Parse(Model.DrillDown) + 1; 
             // convert to sql dayofweek (Sun = 1)
        drilldownQuery =
            drilldownQuery.Where(
                 session => SqlFunctions
                      .DatePart("weekday", session.Timestamp) == 
                            selectedWeekday);
         // get a NotSupportedException here.
         // This function can only be invoked from Linq to Entities
         break;
    case TimeInterval.Weekly:
    case TimeInterval.Monthly:
    case TimeInterval.Annual:
    default:
        // handle these by adjusting the from & through dates?
}

Answer 1

. , :

        if (!String.IsNullOrWhiteSpace(model.DrillDown))
        {
            switch (model.SelectedInterval)
            {
                case TimeInterval.Weekly:
                    model.FromDate = DateTime.ParseExact(model.DrillDown,"yyyy/MM/dd",CultureInfo.InvariantCulture);
                    model.ThroughDate = model.FromDate.AddDays(6);
                    break;
                case TimeInterval.Monthly:
                    model.FromDate = DateTime.ParseExact(model.DrillDown+"/01", "yyyy/MM/dd", CultureInfo.InvariantCulture);
                    model.ThroughDate = model.FromDate.AddMonths(1).AddDays(-1);
                    break;
                case TimeInterval.Annual:
                    model.FromDate = DateTime.ParseExact(model.DrillDown + "/01/01", "yyyy/MM/dd", CultureInfo.InvariantCulture);
                    model.ThroughDate = model.FromDate.AddYears(1).AddDays(-1);
                    break;
                case TimeInterval.Daily:
                    model.FromDate = DateTime.ParseExact(model.DrillDown, "yyyy/MM/dd", CultureInfo.InvariantCulture);
                    model.ThroughDate = model.FromDate;
                    break;
            }

        }

        var through = model.ThroughDate.AddDays(1);

        var drilldownQuery = DataManager.DataSessions
            .Include("Location")
            .Include("Quote.Carriers")
            .Include("Drivers")
            .Include("Vehicles")
            .Where(session =>
                session.Timestamp >= model.FromDate &&
                session.Timestamp < through
            );

        if (!String.IsNullOrWhiteSpace(model.DrillDown))
        {
            switch (model.SelectedInterval)
            {
                case TimeInterval.Hourly:
                    int selectedHour = int.Parse(model.DrillDown);
                    drilldownQuery = drilldownQuery.Where(session => session.Timestamp.Hour == selectedHour);
                    break;
                case TimeInterval.Weekday:
                    var selectedWeekday = (DayOfWeek) int.Parse(model.DrillDown);
                    drilldownQuery =
                        drilldownQuery.Where(
                            session => session.Timestamp.DayOfWeek == selectedWeekday);
                    break;
            }
        }
        return drilldownQuery;

    }