MySQL Bugs: #103436: SqlNullabilityProcessor error when using EF and filter by Date/Time

Bug #103436	SqlNullabilityProcessor error when using EF and filter by Date/Time
Submitted:	23 Apr 2021 2:44	Modified:	25 Jan 2022 23:10
Reporter:	Guillermo Perez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.3+MySQL8.0.24	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	.net, core, date, EF, SqlNullabilityProcessor, time

Description:
Dear friends, thank you for reading. I'm here to report something I consider an error, humbly asking for your advice if I'm doing wrong.

This is my first MySQL application using .Net Core 5, so I installed the package MySql.EntityFrameworkCore 5.0.3, created my DbContext and my models (as this database was already created).

The problem occurs when I try to filter by date-time. Take for example the following code: (for clarity, please check a screenshot of my code viewing the image I stored temporarily at https://ibb.co/Zg4qrGn)

var Ads = mySqlDbContext.MyTable.Where(
                        w => w.PostType == "mytext" 
                        && w.PostStatus == "activeText"
                    )

it works fine and show the records using a loop, but if I add another condition, for example:                         

&& w.PostCreated >= DateTime.Today.AddDays(-15)

It immediately throws the following error (and to be sincere, there's nothing on the internet that speak how to fix that):

System.InvalidOperationException: 'Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFragmentExpression] [Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlUnaryExpression] [Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlFragmentExpression]' of type 'MySql.EntityFrameworkCore.Query.Expressions.Internal.MySQLComplexFunctionArgumentExpression' encountered in 'SqlNullabilityProcessor'.'

Of course, I spent some hours trying to see if I was doing something wrong, checking the fields, my model, etc and everything was ok... I even check the resulting SQL, did run using SQL bench and it was ok also... 

Now I installed Pomelo (I did not know about that library until now, as I usually prefer to work with the Oracle product and not third parties), and the problem seems to go away.

I would like you to check into this problem as this seems to be a very simple task and a total need, as I can't imagine a program that can't calculate dates and/or filter by them, this is very basic stuff...

Thanks for your help!

How to repeat:
I don't know what to write here, as I explained in the first box... :-( 

Please try to filter by data using EF Core and the error will pop-up.  Thanks for your help!

Suggested fix:
To be sincere I don't know what is the "SqlNullabilityProcessor" error or how this did get there... I would like to help, if you need to inspect my code, logs, remote, etc I can help.  Thank you for your help in this situation!

My program screenshot

Attachment: screenshot.png (image/png, text), 68.43 KiB.

Hello Guillermo Perez,

Thank you for the bug report.
Could you please provide repeatable test case (sample project, etc. - please make it as private if you prefer) to confirm this issue at our end?

Regards,
Ashwini Patil

I seem to have the same problem.

The problem seems to occur when trying to compare dates when there is a posibility of there being no date to compare to (ie. table being empty, other criteria not resulting in a match etc.)

I have tried both "t.LastUpdated < DateTime.Now.AddDays(-7)" and "t.LastUpdated.AddDays(7) < DateTime.Now"

I have also tried making the field "LastUpdated" nullable and checking if the value is null before trying to compare the dates but that did not help.

Hi OP, hopefully you see this reply.  I had exactly the same error as you and found a remarkably simple way to get around it.  Instead of defining the datetime in the where clause, create a separate variable:

var dt = datetime.now.addDays(-15);

var Ads = mySqlDbContext.MyTable.Where(
                        w => w.PostType == "mytext" 
                        && w.PostStatus == "activeText"
                        && w.PostCreated >= dt
                    );

This worked fine for me with no errors reported.  Hope this helps.

Hello Guillermo Perez,

Thank you for the bug report and feedback.
Discussed this issue internally with the developer and confirmed that this is a bug and the workaround suggested by Chris Mason seems to work.
Hence, in the meantime I suggest you to use the workaround until issue is fixed. Thank you!

Regards,
Ashwini Patil

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.29 release, and here's the proposed changelog entry from the documentation team:

When comparing a date-time LINQ expression against a database date field,
Connector/NET returned an invalid operation exception due to unavailable
classes that are required for an application to filter using EF Core.

Thank you for the bug report.

Bug #106520 marked as duplicate of this one.