Filter Grid (Pivot) with datetimepicker or text box (Using SQL to LINQ, Lamda) Winforms C#.net-CodePudding

I am populating a grid in my application into a pivot style. (Using SQL to LINQ and Lambda)

I am busy introducing filter textboxes which works fine when there is a normal name value to filter.

I am struggling to get a filter to work specific to the date field. I have first tried with a datetimepicker control and could not get it to work and started looking at using a textbox filter to filter the specific date.

The textbox filter works partially. If I type in a partial number i.e 2023 or 18 etc it filters the grid on that value whether in the date or time field.

If I type the date out in a format the grid filters to a blank result. Date Formats Used include yyyy-MM-dd, yyyy/MM/dd, yyyy.MM.dd.

I cannot seem to find an appropriate solution around this.

Grid Example: Grid Example

        private void PopulateGird()
        {

            var productivity = operationsEntities.Cases
                .Where(x => x.Case_Status == 2)
                .Where(b => b.Branches.Branch.Contains(txtBranch.Text))
                .Where(t => t.TechResource.FullName.Contains(txtResource.Text))
                .Where(s => s.StartDate.Value.ToString().Contains(txtStartDate.Text))
                .GroupBy(d => new { d.Branches.Branch, d.TechResource.FullName, d.StartDate })
                .Select(q => new
                {
                    Branch = q.Key.Branch,
                    Resource = q.Key.FullName,
                    Date = q.Key.StartDate,
                    Total = q.Where(p => p.Primary_Call > 0).Count(),
                    Installation = q.Where(p => p.Primary_Call == 1).Count(),
                    DeInstallation = q.Where(p => p.Primary_Call == 2).Count(),
                    ReInstallation = q.Where(p => p.Primary_Call == 3).Count(),
                    Repair = q.Where(p => p.Primary_Call == 4).Count()
                    
                }).ToList() ;
            
            gvProductivity.DataSource = productivity;
            gvProductivity.Columns["DeInstallation"].HeaderText = "De-Installation";
            gvProductivity.Columns["ReInstallation"].HeaderText = "Re-Installation";
            gvProductivity.Columns["Total"].HeaderText = "Total Jobs";

Can someone please point me in the right direction. Maybe I am missing something(obviously) or forgetting to add something

Thank you

CodePudding user response：

So LINQ does not like to work with Dates at all when trying to implement a filter. I had to add additional column to my DB where the date is seen as a string and not as a date. After adding this I was able to reference the string field and the string of the datetimepicker and it works. Probably not the best solution but the solution I needed to make this work.

Other answers are welcome for other users :D