I am trying to filter rows of data on a specific date:
The current code takes a dictionary of , and applies the filter to queryable like so:
queryable = pageSettings.FiltersDictionary.Aggregate(queryable, (current, keyValuePair) => current.Where(keyValuePair.Key + ".ToString()" + ".Contains(\"" + keyValuePair.Value + "\")"));
The SQL generated from this will apply the following WHERE clause:
WHERE CAST( [Extent1].[TimeStarted] AS nvarchar(max)) LIKE N'%2015-05-05%'}
SELECT CONVERT(nvarchar(max), getdate())
To see how SQL Server structures its converted DateTime we can see that it will never line up, as it is formatted like so:
May 18 2016 3:55PM
I have tried to add a custom where clause to properties of type DateTime, however, "LINQ to Entities" does not seem to like the 'ToString("MMMM-DD-YYY")' call and generates an error.
I want to be able to supply a date, such as 2015-05-05 and have it like up with May 5 2015 - Am I missing something obvious to get this to work?
The following (bare-bones) example solved my issue:
DateTime givenDate = DateTime.Parse(pair.Value); string dateTimeConverted = givenDate.ToString("MMMM d yyyy"); queryable = queryable.Where(pair.Key + ".ToString()" + ".Contains(\"" + dateTimeConverted + "\")");
Note the two spaces between month and day, as SQL Server seems to have that as well, this is needed for the "LIKE" clause to match anything.