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