Filtering database with Linq

Question

I'm building an MVC web app with ASP.CORE 3.1 and EF connected to MSSQL.

Today, finally after 3 days, I've somewhat reached a working code for filtering my view.

I've got database of

• Date stamps (15 minute intervals) and
• INT values of cars (3 types (OA, NA, NS))
• going through Border (13 of them).
• Data are for both directions (Plus, Minus).
• I need to filter this by above plus change time interval (60, 30, 15 minutes)
• and by Weekdays (Monday, Friday, Sat, Sun, and Ordinary (Tuesday-Thursday)).

But I'm doing filtering for the first time in my life here. Happy it works but would love to hear how can I improve this. Dataset is not big. Around 17 columns x 300 000 rows.

Visualization

Model

Borders.cs

public class Borders
{
    [Key]
    public int Id { get; set; }

    public int TransitId { get; set; }

    public DateTime Day { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }

    public int OA_Plus { get; set; }
    public int NA_Plus { get; set; }
    public int NS_Plus { get; set; }
    public int ALL_Plus { get; set; }

    public int OA_Minus { get; set; }
    public int NA_Minus { get; set; }
    public int NS_Minus { get; set; }
    public int ALL_Minus { get; set; }

    public int OA_Sum { get; set; }
    public int NA_Sum { get; set; }
    public int NS_Sum { get; set; }
    public int ALL_Sum { get; set; }
}

ViewModel

BordersViewModel.cs

public class BordersViewModel
{
    // DB items
    public IEnumerable<Borders> Borders { get; set; }

    // Other ViewModels
    public IEnumerable<ChartIntensityVM> ChartIntensityVM { get; set; }
    // Contains only String Key, Int Value

    // Filters
    public string FilterTransitNumber { get; set; } = "All";
    public string FilterSeason { get; set; } = "All";
    public string FilterDay { get; set; } = "All";
    public string FilterTimeInterval { get; set; } = "1";
    public string FilterDirection { get; set; } = "All";

    public bool FilterChkboxOA { get; set; } = true;
    public bool FilterChkboxNA { get; set; } = true;
    public bool FilterChkboxNS { get; set; } = true;

    // SelectListItems
    public List<SelectListItem> ListTransitNumbers { get; set; } = new List<SelectListItem>()
    {
        new SelectListItem { Value = "All", Text = "All" },
    };
    public List<SelectListItem> ListSeasons { get; set; } = new List<SelectListItem>()
    {
        new SelectListItem { Value = "All", Text = "All" },
        new SelectListItem { Value = "Q1", Text = "Spring (1-3)" },
        new SelectListItem { Value = "Q2", Text = "Summer (3-6)" },
        new SelectListItem { Value = "Q3", Text = "Fall (6-9)" },
        new SelectListItem { Value = "Q4", Text = "Winter (9-12)" },
    };
    public List<SelectListItem> ListDays { get; set; } = new List<SelectListItem>()
    {
        new SelectListItem { Value = "All", Text = "All" },
        new SelectListItem { Value = "Ordinary", Text = "Ordinary day (Tue, Wed, Thr)" },
        new SelectListItem { Value = "Friday", Text = "Friday" },
        new SelectListItem { Value = "Sunday", Text = "Sunday" },
        new SelectListItem { Value = "Monday", Text = "Monday" },
    };
    public List<SelectListItem> ListTimeIntervals { get; set; } = new List<SelectListItem>()
    {
        new SelectListItem { Value = "60", Text = "1 hr" },
        new SelectListItem { Value = "30", Text = "0.5 hr" },
        new SelectListItem { Value = "15", Text = "0.25 hr" },
    };
    public List<SelectListItem> ListDirections { get; set; } = new List<SelectListItem>()
    {
        new SelectListItem { Value = "All", Text = "All" },
        new SelectListItem { Value = "Plus", Text = "Plus" },
        new SelectListItem { Value = "Minus", Text = "Minus" },
    };
}

View

Index.cshtml

classical view, you can see that in the picture above
SelectLists, Checkboxes, Filterbutton that is doing form POST method

Controller

BordersController.cs

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Index(BordersViewModel vm)
{
    if (ModelState.IsValid)
    {
        vm.Borders = await _db.Borders.OrderBy(x => x.Start).ToListAsync();
        populateListTransitNumbers(vm);
    }

    // FILTER - TransitNumber (1 - 16)
    //================================
    if (vm.FilterTransitNumber != "All")
    {
        vm.Borders = vm.Borders.Where(x => x.TransitId == Convert.ToInt32(vm.FilterTransitNumber));
    }

    // // FILTER - Day (Monday, Friday, Sunday, Ordinary [Tuesday, Wednesday, Thursday])
    //==================================================================================
    if (vm.FilterDay != "All")
    {
        List<string> ordinaryDays = new List<string>() { "Tuesday", "Wednesday", "Thursday" };

        switch (vm.FilterDay)
        {
            case "Monday":
            case "Friday":
            case "Sunday":
                vm.Borders = vm.Borders.Where(x => x.Start.DayOfWeek.ToString() == vm.FilterDay);
                break;
            default:
                vm.Borders = vm.Borders.Where(x => ordinaryDays.Contains(x.Start.DayOfWeek.ToString()));
                break;
        }
    }

    // FILTER - Season (Q1 - Q4)
    //==========================
    if (vm.FilterSeason != "All")
    {
        switch (vm.FilterSeason)
        {
            case "Q1":
                vm.Borders = vm.Borders.Where(x => x.Start.Month >= 1 && x.Start.Month <= 3);
                break;
            case "Q2":
                vm.Borders = vm.Borders.Where(x => x.Start.Month >= 3 && x.Start.Month <= 6);
                break;
            case "Q3":
                vm.Borders = vm.Borders.Where(x => x.Start.Month >= 6 && x.Start.Month <= 9);
                break;
            case "Q4":
                vm.Borders = vm.Borders.Where(x => x.Start.Month >= 9 && x.Start.Month <= 12);
                break;
        }
    }

    // FILTER - TimeInterval (60, 30, 15)
    //===================================
    var filteredBordersInterval = vm.Borders
        .GroupBy(x =>
        {
            var stamp = x.Start;
            stamp = stamp.AddMinutes(-(stamp.Minute % Convert.ToInt32(vm.FilterTimeInterval)));
            stamp = stamp.AddMilliseconds(-stamp.Millisecond - 1000 * stamp.Second);
            return stamp;
        })
        .Select(g => new
        {
            TransitId = g.First().TransitId,
            Start = g.Key,
            OA_Plus = g.Sum(gi => gi.OA_Plus),
            NA_Plus = g.Sum(gi => gi.NA_Plus),
            NS_Plus = g.Sum(gi => gi.NS_Plus),
            OA_Minus = g.Sum(gi => gi.OA_Minus),
            NA_Minus = g.Sum(gi => gi.NA_Minus),
            NS_Minus = g.Sum(gi => gi.NS_Minus),
        });

    // ORDER BY TIME INTERVAL
    // Ignore the whole DateTime day, just group by HH:mm and SUM column rows
    //=======================
    var filteredBordersGrouped = filteredBordersInterval
        .GroupBy(x => x.Start.ToString("HH:mm"))
        .Select(g => new
        {
            DayTime = g.Key,
            OA_Plus = g.Sum(gi => gi.OA_Plus),
            NA_Plus = g.Sum(gi => gi.NA_Plus),
            NS_Plus = g.Sum(gi => gi.NS_Plus),
            OA_Minus = g.Sum(gi => gi.OA_Minus),
            NA_Minus = g.Sum(gi => gi.NA_Minus),
            NS_Minus = g.Sum(gi => gi.NS_Minus),
        });

    // FINAL FILTER into X/Y values for the Chart
    // Sum only those cars that are checked and both or individual directions
    //===========================================
    vm.ChartIntensityVM = filteredBordersGrouped
        .GroupBy(x => x.DayTime)
        .Select(g =>
        {
            int PlusSum = g.Sum(gi => vm.FilterChkboxOA == true ? gi.OA_Plus : 0)
                        + g.Sum(gi => vm.FilterChkboxNA == true ? gi.NA_Plus : 0)
                        + g.Sum(gi => vm.FilterChkboxNS == true ? gi.NS_Plus : 0);

            int MinusSum = g.Sum(gi => vm.FilterChkboxOA == true ? gi.OA_Minus : 0)
                        + g.Sum(gi => vm.FilterChkboxNA == true ? gi.NA_Minus : 0)
                        + g.Sum(gi => vm.FilterChkboxNS == true ? gi.NS_Minus : 0);

            int AllSum = vm.FilterDirection == "All" ? PlusSum + MinusSum : (vm.FilterDirection == "Plus" ? PlusSum : MinusSum);

            return new ChartIntensityVM
            {
                Key = g.Key,
                Value = AllSum,
            };
        });

    return View(vm);
}

Answer 1

Some quick remarks

• Follow the naming standards. Property names etc. should not contain anything but alphanumeric characters. No underscores etc.

• Use meaningful names. "OA" is meaningless, "OA_Plus" is even more confusing.

• A class name shouldn't be a plural (some exceptions apply): Borders.

• So many magic strings. "All" appears numerous times, for instance. Consider moving these to static classes as public const string properties.

• Do not pointlessly abbreviate: naming it Chkbox isn't making your code run faster.

• Do not call something a "ListXXXX", e.g. ListDays. If it is a list of days, call it "Days".

• ListDays seems to omit Saturday, and "Thr" isn't the correct abbreviation for "Thursday".

• Your seasons/quarters seem to overlap: "Spring (1-3)" vs. "Summer (3-6)". IMHO it should be "1-3", "4-6", etc. However, this is also implemented this way in your business logic -- vm.Borders.Where(x => x.Start.Month >= 3 && x.Start.Month <= 6); -- so I guess that is the "correct" logic? Still, it doesn't make sense to me that Q1 shows data for three months, whereas the others show data for four months. IMHO this look like a major bug.

• public async Task<IActionResult> Index(BordersViewModel vm) is more than 100 lines long. I'd move almost all of that code to a separate class.

---

The title of your question says "Filtering database with Linq", yet your question works completely with the view model. I actually wonder if some of the filtering you do could be done more efficiently in a query, instead of (what you seem to be doing) dumping all of the available data in Borders objects and then applying a lot of filtering logic.