Compare DateTime to string in LINQ query

Question

I have a LINQ query which filters some data by comparing it to a given string. My entity has a DateTime field, which I also need to compare to said string.

However, I need the DateTime to be formatted as dd/MM/yyyy but DateTime.ToString() gives m/d/yyyy.

When I try parsing the DateTime with my formatting, I get the following exceptions:

An exception of type 'System.NotSupportedException' occurred in Project1.dll but was not handled in user code LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

Here's my code:

auditLogs = auditLogs
                .Where(x => x.Username.Contains(searchValue) ||
                    x.Description.Contains(searchValue) ||
                    x.EntityType.Contains(searchValue) ||
                    x.EntityState.Contains(searchValue) ||
                    x.ChangeTime.ToString("dd/MM/yyyy").Contains(searchValue));

Is there any workaround to this? Or a "proper" way? I know I could just bruteforce it in a loop, but I want to do it elegantly.

EDIT: I cannot parse the string to DateTime, because the string may be just 03/2019 or 22/02 and not a full date.

@SonerGönül it does not answer my question at all. I need to parse DateTime, not cast an int to string. — Ivan Kukic, Mar 07 '19 at 11:00
@IvanKukic, so whats the value in `searchValue` for date part in your query? — er-sho, Mar 07 '19 at 11:08
I added an edit. `searchValue` may be just `dd/MM` or `MM/yyyy`. — Ivan Kukic, Mar 07 '19 at 11:09
You can use sql functions to build a string from the date. [SqlFunctions Class][(https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.sqlclient.sqlfunctions?view=netframework-4.7.2) . For example SqlFunctions.DatePart method. Another approach is to write plain sql query. — Fabio, Mar 07 '19 at 11:22
What is the db-column or C# type of `ChangeTime` ? The error says it is a string. — H H, Mar 07 '19 at 11:50
@HenkHolterman it's `DateTime`, I think that `System.String ToString` refers to the method return value and name, not the type of the operand. — Ivan Kukic, Mar 07 '19 at 11:52
The namespace is `System.Data.Entity.SqlServer`, so: yes, as long as it's ms-sql. — H H, Mar 07 '19 at 12:46

score 0 · Answer 1 · answered Mar 07 '19 at 11:29

0

Have you tried creating string from date parts? See here. It seems similar problem.

answered Mar 07 '19 at 11:29

Pablo notPicasso

3,031
3
17
22

score 0 · Answer 2 · answered Mar 07 '19 at 13:16

Apparently your sequence of AuditLogs is an IQueryable, as opposed to an IEnumerable.

The cause of the problem

You have to be aware about the differences between IEnumerable and IQueryable. An object that implements IEnumerable contains everything to enumerate the sequence: you can try to get the first element, and once you've got an element, you can try to get the next element. The object might need to call some functions to give your the next requested element.

Enumerating at its lowest level is done using GetEnumerator, MoveNext and Current. At a higher level you'll see foreach, or LINQ functions like ToList, ToDictionary, Count, Any, Max: all LINQ functions that don't return IEnumerable<...>

Although an IQueryable seems similar to an IEnumerable, internally it works differently. An object that implements IQueryable holds an Expression and a Provider. The Expression is a generic representation of what must be queried. The Provider knows who must execute the query (usually a database management system) and which language this DBMS uses (usually something like SQL).

When you start enumerating (GetEnumerator), the Expression is sent to the Provider, who will translate it into the language that the executor understands (SQL) and executed the query. The returned data is converted to an IEnumerator which is returned to you to MoveNext

The executor does not know your local functions, and thus can't use any of them. In fact, although the creators of entity framework did a marvelous job to translate expressions into SQL, there are a lot of LINQ functions that cannot be used in a query. See Supported and Unsupported LINQ Methods (linq to entities)

By now you should be able to understand why you can't use ToString().

Back To Your question

There are several problems though with your searchstring. If it equals 11, does that mean Month 11? or does it mean the 11th day of a Month, or do you also want to find the dates in year 2011? And do you also want to support "2010/11/30" as well as "30/11/2010"?

Consider converting your ChangeTime value into portions for Year / Month / Day, using SqlFunctions.DateName. With this function you can convert the string representation of year, month, day, etc from a DateTime:

var result = auditLogs.Where(auditLog => ...
    || SqlFunctions.DateName("year",  auditLog.ChangeTime).Contains(searchParameter) 
    || SqlFunctions.DateName("month", auditLog.ChangeTime).Contains(searchParameter)
    || SqlFunctions.DateName("day",   auditLog.ChangeTime).Contains(searchParameter));

This would give you the following result

searchString     yields ChangeTime:
"2010/11/30"     all ChangeTimes on day 20101130
"30/11/2020"     all ChangeTimes on day 20101130
"11/30/2020"     all ChangeTimes on day 20101130

This seems exactly what you want, whether the data is Japanese, European or American. But what about the following cases:

"11/2010". Do you only want the dates of November 2010? or also 11 January 2010?
"11". Every day of November? Every 11th day of every month? Every day of 2011?

This can only be answered if you specify the requirements unambiguously.

If you want a literal search, you could concatenate the extracted year / month / day strings into the string that you want to use to find in your searchstring. However, this will really limit the search possibilities.

Proper operator interface solves these problems

My advise would be to change the operator interface. Use something like combo boxes. This makes sure that you can't specify invalid dates. Using a zero (or empty) value means that you don't want to filter on the zero year / month / day.

An input separated into year / month / day would make it way more easy for operators to understand the syntax of the searchstring. For you the query will be way easier:

static IQueryable<AuditLog> Where(this IQueryable<AuditLog> auditLogs,
       int year, int month, int day)
{
     // zero value parameters: do not use. so
     // 2011 11 00: all days of November 2011
     // 2011 00 14: every 14th day of every month of 2011

     if (year != 0)
     {
         if (month != 0)
         {
             if (day != 0)
             {  // use parameters year, month, day
                return auditLogs.Where(auditLog =>
                       auditLog.ChangeTime.Year == year &&
                       auditLog.ChangeTime.Month == month &&
                       auditLog.ChangeTime.Day == day);
             }
             else
             {  // use parameters year, month
                return auditLogs.Where(auditLog =>
                       auditLog.ChangeTime.Year == year &&
                       auditLog.ChangeTime.Month == month);
             }
        }
        else
        {   // don't use Month
            if (day != 0)
            {   // use parameters year, day
                return auditLogs.Where(auditLog =>
                       auditLog.ChangeTime.Year == year &&
                       auditLog.ChangeTime.Day == day);
             }
             else
             {  // use parameter year
                return auditLogs.Where(auditLog => auditLog.ChangeTime.Year;
             }
        }
    }
    else
        ... etc, don't use parameter day        
}

Usage:

int year = comboBox.SelectedItem;     // or whatever input device you use
int month = comboBox.SelectedItem     // make sure you can't select invalid months
int day = comboBox.SelectedItem       // make sure you can't select invalid days
// zero value means: ingnore the value

var result = auditLogs
    .Where(...)
    .Where(year, month, day);

Compare DateTime to string in LINQ query

2 Answers2

The cause of the problem

Back To Your question

Proper operator interface solves these problems