Entity Framework IQueryable - specific date

Question

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%'}

Using

 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?

score 0 · Accepted Answer · answered May 18 '16 at 16:43

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.

Entity Framework IQueryable - specific date

1 Answers1