Description:
When having a table containing a VARCHAR or text data type, trying to use the field's Contains method with a string variable will produce an WHERE LIKE clause containing the parameter name but not using it.
How to repeat:
Table:
CREATE TABLE `tblbug` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mytext` varchar(250) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Creating a model using the latest entity framework for that table.
C# code:
using (var db = new testEntities())
{
var x = db.tblbug.Where(b => b.mytext.Contains("hello"));
var param = "hello";
var y = db.tblbug.Where(b => b.mytext.Contains(param));
}
Result queries:
x:
SELECT
`Extent1`.`id`,
`Extent1`.`mytext`,
FROM `tblbug` AS `Extent1`
WHERE `Extent1`.`mytext` LIKE '%hello%'
y:
SELECT
`Extent1`.`id`,
`Extent1`.`mytext`,
FROM `tblbug` AS `Extent1`
WHERE `Extent1`.`mytext` LIKE '%p__linq__0%'
As you can see in "y"'s query, the parameter name is being used ('p__linq__0'), but not correctly, making the MySQL think it's the actual text to compare.
Wanted query:
SELECT
`Extent1`.`id`,
`Extent1`.`mytext`,
FROM `tblbug` AS `Extent1`
WHERE `Extent1`.`mytext` LIKE '%@p__linq__0%'
Suggested fix:
Use the SQL parameter correctly for the LIKE clause to work.
Description: When having a table containing a VARCHAR or text data type, trying to use the field's Contains method with a string variable will produce an WHERE LIKE clause containing the parameter name but not using it. How to repeat: Table: CREATE TABLE `tblbug` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mytext` varchar(250) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; Creating a model using the latest entity framework for that table. C# code: using (var db = new testEntities()) { var x = db.tblbug.Where(b => b.mytext.Contains("hello")); var param = "hello"; var y = db.tblbug.Where(b => b.mytext.Contains(param)); } Result queries: x: SELECT `Extent1`.`id`, `Extent1`.`mytext`, FROM `tblbug` AS `Extent1` WHERE `Extent1`.`mytext` LIKE '%hello%' y: SELECT `Extent1`.`id`, `Extent1`.`mytext`, FROM `tblbug` AS `Extent1` WHERE `Extent1`.`mytext` LIKE '%p__linq__0%' As you can see in "y"'s query, the parameter name is being used ('p__linq__0'), but not correctly, making the MySQL think it's the actual text to compare. Wanted query: SELECT `Extent1`.`id`, `Extent1`.`mytext`, FROM `tblbug` AS `Extent1` WHERE `Extent1`.`mytext` LIKE '%@p__linq__0%' Suggested fix: Use the SQL parameter correctly for the LIKE clause to work.