-
Notifications
You must be signed in to change notification settings - Fork 3.2k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Wrong query generation when using Contains inside where predicate. #6687
Comments
@gmartinezsan curious, does MySQL generally not support this usage of parenthesis in concatenation expressions? Or is there something else that makes this SQL not valid? |
Somewhat related to this, there are improvements in 1.1 for the translation of |
@gmartinezsan the fix Diego is referring to is here: 5e1c562 We only did it for SqlServer and Sqlite, so indeed MySql provider would have to add their own MethodCallTranslators (probably quite similar to the Sqlite one - SqliteContainsOptimizedTranslator). Note that the commit I linked also fixes bug in RelationalCompositeMethodCallTranslator which allows using provider specific translators rather than the default ones. Without this change even if MySql provider adds appropriate translators, they won't be used. |
hi @maumar , any idea when these fixes will be published? |
@gmartinezsan those are 1.1 changes. Depending on the answer to my first question on the usage of parenthesis in concatenation we may have a more general issue and the possibility to fix it for 1.0 as well, either on a patch release of EF Core or in the MySQL provider. |
@divega MySQL string concatenation should use the CONCAT function. Unfortunately '+' operator is not supported. |
@gmartinezsan we have some extensibility points for this kinds of scenarios. You can override DefaultQuerySqlGenerator.TryGenerateBinaryOperator and generate a custom SQL for string concatenation. |
We added the MethodCallTranslators implementation based on the approach used in Sqlite, because changing the concat operator was not enough to cover this case. Any idea when 1.1 will be available? |
@gmartinezsan I'll email you about dates |
@gmartinezsan you are unblocked now right? @maumar will investigate why the extension point in default sql generator didn't work (but not immediately). |
@rowanmiller hi, yes we have solved this issue, and tested it with latest EF Core version (1.1) and it works fine, but only with 1.1. |
The problem is that out VisitBinary operator is hard-coded to a form [leftOperand] [operator] [rightOperand], so it doesn't allow modifying the structure, just what the operator would be. We have StringConcatTranslator that would normally handle this (convert Add binary operator to a method call) but because we already ran a method call translator (translating Contains) which produced the string concat, the second translator (StringConcatTranslator) doesn't run. This happens because we stop running the translators on a given expression once the first one successfully processes that expression. So the correct way to address this is to make all the necessary modifications in the Contains translator that is going to run first, i.e. what @gmartinezsan did. |
@shahafal Still hitting this issue? |
@gmartinezsan Hi gabriela,
What can I do without trying a different mysql connector? |
@shahafal Thanks for the feedback. 6.10.0 alpha should be enough for this case. I personally tried it today and it is working with EF Core 1.1 and 6.10.0. Here's the linq expression I'm using: context.Employees.Where(t => t.FirstName.Contains("jo")).ToList(); Could you please share the code you are using? Thanks! |
@gmartinezsan
this is my code:
and the error:
I wonder if perhaps the reason your code passed and mine failed is due to cached nuget packages? (like the issue in upgrading entity framework version from v1.0 to v1.1 where the package "Microsoft.EntityFrameworkCore.Relational" is needed to be added to project json with the new 1.1 version otherwise its v1.0 is being used and an error is thrown) My project.json file has had upgrades for entity framework from v1.0 to v.1.1 and for mysql connector from v7.0.4 to v7.0.6 to v.6.10.0 in the following packages:
Is there a chance that one of them (the entity framework or mysql connector) refers to an older package? Is there a package you can think of that I can try and add to my project.json with the correct version so the app won't use its cached version in hopes that it would solve the error with |
@shahafal just tried using a variable instead of a constant or hard coded value as I was doing and only the last one worked. I added a bug in our community site so you can follow up the fix. |
@gmartinezsan could you provide the entire stack trace of the issue that you were experiencing? This particular scenario seems to be working on Sql provider, but I wonder if I can force the issue here as well if I modify a query somewhat. |
@maumar System.InvalidOperationException was unhandled by user code |
@gmartinezsan in my previous message I was still having this error even after upgrading to mysql v6.10.0-alpha: The fix was to change this line:
to this:
After fixing this problem, I am now having the error you wrote in your last message regarding passing parameter instead of plaintext into I opened a post about it in stackoverflow thinking I might miss something in the way @maumar in case this helps, here is my code:
and my stacktrace:
|
Visual Studio 2015 update 3
efcore : V1.0.0
MySQL database
A query with a where predicate that uses string.contains inside of the where . Example:
The like expression generated in DefaultQuerySqlGenerator.cs creates a misplaced parenthesis:
Which causes an SQL exception:
EF Core version: 1.0
Can you please have a look into this?
The text was updated successfully, but these errors were encountered: