CODEWITHSUNDEEP
mysqlentity-frameworkentity-framework-coreasp.net-core-webapi
Code Man
Code Man

Reputation: 1568

EF Core MySQL Contains(variable) like operator

I am using ASP.NET Web API Core with EF Core to create a method, which returns JSON format records.
When I use contains for like operator (%varible%), an error occured with message below:

An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ '%'

ORDER BY p.StoreId, p.CategoryId' at line 4

Seems one of % symbol is not at right position.
I checked my codes and raw sql output but didn't see any problem.
Hope someone can help me!

var query = from p in db.Products
            join c in db.ProductCategory
                   on new { p.StoreId, p.CategoryId } equals new { c.StoreId, c.CategoryId } into pc
            from c in pc.DefaultIfEmpty()
            where (p.ProductName.Contains("a"))
            select p;  

query.ToList()

Raw SQL

SELECT `p`.`StoreId`, `p`.`ProductId`, `p`.`CategoryId`, `p`.`Description`, `p`.`ImagePath`, `p`.`ProductName`, `p`.`SalesPeriodFrom`, `p`.`SalesPeriodNeverEnd`, `p`.`SalesPeriodTo`, `p`.`SalesUnit`, `p`.`UnitPrice`, `c`.`StoreId`, `c`.`CategoryId`, `c`.`CategoryName`
FROM `Products` AS `p`
LEFT JOIN `ProductCategory` AS `c` ON (`p`.`StoreId` = `c`.`StoreId`) AND (`p`.`CategoryId` = `c`.`CategoryId`)
WHERE `p`.`ProductName` LIKE ('%' + 'a') + '%'
ORDER BY `p`.`StoreId`, `p`.`CategoryId`

Windows 7 Professional x64
Visual Studio 2015 Professional
MySQL 5.6.17

Microsoft .NET Core 1.0.1
with dependencies

   "Microsoft.ApplicationInsights.AspNetCore": "1.0.2",
   "Microsoft.AspNetCore.Mvc": "1.0.1",
   "Microsoft.AspNetCore.Routing": "1.0.1",
   "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
   "Microsoft.AspNetCore.Server.Kestrel": "1.0.1",
   "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
   "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
   "Microsoft.Extensions.Configuration.Json": "1.0.0",
   "Microsoft.Extensions.Logging": "1.0.0",
   "Microsoft.Extensions.Logging.Console": "1.0.0",
   "Microsoft.Extensions.Logging.Debug": "1.0.0",
   "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
   "MySql.Data.EntityFrameworkCore": "7.0.5-IR21",
   "System.Linq.Dynamic.Core": "1.0.6.6"

Upvotes: 3

Views: 2187

Answers (1)

じょい
じょい

Reputation: 36

I found that changing MySql.Data.EntityFrameworkCore from "7.0.6-IR31" to "6.10.0-alpha" will result in valid SQL syntax.

6.10.0-alpha is more new.

However, another problem occurs. Please see here.

use parameter from method argument inside linq contains

I think that there is no choice but to wait for the bug to be fixed by the update.

Upvotes: 2

Related Questions