Linq where condition on datetime.ToString()

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.ExceptionInterceptor1.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 ?

Answer 1

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.