Linq where condition on datetime.ToString()

entity-framework-core linq

Question

I have following Linq code

// query = IQueryable<DataClass>
query = query.Where(m => m.Column1.Contains(model.search.value)
        || m.DateTimeColumn2.ToString("dd.MM.yyyy").StartsWith(model.search.value));

return query.ToList() // here the error is thrown

I get NullReferenceException error

Exception has occurred: CLR/System.NullReferenceException An exception of type 'System.NullReferenceException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code: 'Object reference not set to an instance of an object.' at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor
1.EnumeratorExceptionInterceptor.MoveNext()

if i commented out the line for 2nd column it works

//|| m.DateTimeColumn2.ToString("dd.MM.yyyy").StartsWith(model.search.value)

model.search.value is string value I am trying to filter all columns. The DateTimeColumn2 is in DateTime datatype in the database, but user input string, therefore Iam converting DateTimeColumn2 to string and try to filter to users value. Any idea, what I am doing wrong ?

1
2
8/2/2018 11:17:58 AM

Accepted Answer

What happens here is that the part...

|| m.DateTimeColumn2.ToString("dd.MM.yyyy").StartsWith(model.search.value)

...can't be translated into SQL (ToString("dd.MM.yyyy") isn't supported`), so EF-core auto-switches to client-side evaluation.

However, now the whole Where clause is evaluated client-side, including the first part,

m.Column1.Contains(model.search.value)

Now this first part has become susceptible to null reference exceptions. There are entities that have a null for Column1.

When you remove the DateTimeColumn2 predicate the whole statement can be translated into SQL and evaluated by the database.

2
8/2/2018 12:10:46 PM

Popular Answer

If you think that the exception is thrown because of any of the DateTimeColumn2 values might be null, check for non-nullness:

query = query.Where(m => ...
    || (m.DateTimeColumn2 != null &&
        m.DateTimeColumn2.ToString("dd.MM.yyyy").StartsWith(model.search.value)));


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow