LINQ to Entities formatting date exception

Question

I got this error:

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'System.String ToString(System.String)'

method, and this method cannot be translated into a store expression.'

The code is as below:

var search = filter.getFilterValueFor("StartDate", "eq");                    
query = query.Where(it => it.it.it.ne.newWo.wo.STRT_DT.GetValueOrDefault().ToString("dd/MM/yyyy").Contains(search));
var total = query.Count();

How to solve this error? Thank you.

OK - that is the wrong way to search for dates. You should create a `DateTime` variable and populate it. Then use `query = query.Where(it => it.it.it.ne.newWo.wo.STRT_DT == yourNewDateVariable); var total = query.Count();` — mjwills, Jul 13 '20 at 14:01
Adding to @mjwills's comment, I suggest using [DbFunctions](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbfunctions?view=entity-framework-6.2.0) to manipulate queries if needed. The [TruncateTime](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbfunctions.truncatetime?view=entity-framework-6.2.0#System_Data_Entity_DbFunctions_TruncateTime_System_Nullable_System_DateTime__) is useful for comparing dates without time involved. — jegtugado, Jul 13 '20 at 14:09
I'd argue against doing that personally (i.e. if you want to remove time I'd check it was >= the start of the day you are interested in and < the start of the next date). But yes, otherwise good point @jegtugado. — mjwills, Jul 13 '20 at 14:10

score 0 · Answer 1 · answered Jul 15 '20 at 07:27

An IQueryable<...> does not represent a sequence of similar items, it represents the potential to get a sequence of similar items.

To do this, the IQueryable has an Expression and a Provider. The Expression holds the query that must be executed in some generic format. The Provider knows who must execute the query (usually a database management system) and what language is used to communicate with the DBMS (usually SQL).

When you start enumerating, either explicitly, using IQueryable.GetEnumerator(), or at higher level using foreach / ToList() / FirstOrDefault() / Any(), etc, the Expression is sent to the Provider, who will try to translate it into SQL and execute the query. The fetched data is returned as an IEnumerator<...>.

Back to your problem

Your provider does not know method ToString(string). In fact there are several LINQ methods that are not supported by your Provider. See List of Supported and Unsupported LINQ methods (LINQ to entities).

You also use the method String.Contains, which the Provider also will not know. Even if you would solve the ToString(string) problem you would stumble into this similar problem.

What you could do, is transfer the value in its original DateTime format to your local process, and convert it there to a String, and use the Contain. Transferring the data to your local process is done using AsEnumerable. This is preferred above ToList, because AsEnumerable will not fetch much more data than you will use, ToList will fetch everything.

The problem is that you use this construction to filter the fetched data (Where). So if you would use that solution, you will transfer way more data than you would need.

Alas you forgot to mention what you want to query. I think that you want to get all items where the string version of a date "dd/MM/yyyy" contains "2020", or "11": you want all items of a certain year, or all items from November, or maybe the first day of each month.

If that is the case, consider to translate the value of search from string to the year (or month, or date) that you want to fetch, and use DateTime.Year (or month, or Day), to compare.

One final solution: use SQL Like. See class DbFunctions

Next time: don't give us code and say: "this code doesn't do what I want", give us (also) a description of what you do want.

LINQ to Entities formatting date exception

1 Answers1

Back to your problem