MySQL Linq using .Contains(variable)

Question

Setup info:

• VS2013 / C#
• EF6
• MySQL database
• .Net Connector 6.9.5

I'm trying to create a method that returns a collection of Account records using a partial name as the search criteria. If I hard code a string value using the IQueryable .Contains() extension method, it returns data. However, when I attempt to use a variable no data is returned.

Public class Test() {

MyEntities db = new MyEntities();

//Works....but the search criteria is hard coded.
  public IQueryable<Account> WorksButValueHardCoded() {

    return (from a in db.Accounts
           where a.accountname.Contains("Test")
           select a);
  }

//Does not return anything
  public IQueryable<Account> DoesNotReturnAnyData() {

    //Obviously I would use a parameter, but even this test fails
    string searchText = "Test";  

    return (from a in db.Accounts
           where a.accountname.Contains(searchText)
           select a);
  }
}

I can see in the LINQ generated SQL used the LIKE operator, but I don't understand how the variable is injected as it reads:

SELECT
`Extent1`.`accountid`, 
`Extent1`.`accountname`
FROM `account` AS `Extent1`
WHERE `Extent1`.`accountname` LIKE '%p__linq__0%'

So...why does it work with the hard coded value and not a string variable?

Answer 1

I ran into the same problem and followed the error step by step with Glimpse (nice tool to inspect what the server is doing). It turned out that the SQL-Statement is built correctly, because I got results by executing it on the database. The problem could be the replacement of the string variables in the statement. I guess LINQ isn't replacing just the string you pass but fills the variable with spaces to the VARCHAR limit so your query looks like

SELECT`Extent1`.`accountid`, `Extent1`.`accountname`
FROM `account` AS `Extent1`
WHERE `Extent1`.`accountname` LIKE '%Test                ...       %'

Add

.Trim()

to your string and it works.

public IQueryable<Account> DoesNotReturnAnyData() {
    string searchText = "Test";

    // Use Trim() here
    return (from a in db.Accounts
       where a.accountname.Contains(searchText.Trim())
       select a);
}

Answer 2

This is a reported bug with MySQL Entity Framework 6.9.5

Bug #74918 : Incorrect query result with Entity Framework 6: https://bugs.mysql.com/bug.php?id=74918

It has been fixed in MySQL Connector/Net 6.7.7 / 6.8.5 / 6.9.6 releases.

Changelog: With Entity Framework 6, passing in a string reference to the "StartWith" clause would return incorrect results.

Alternatively, a workaround is to use .Substring(0) which forces Entity not to use LIKE (might affect performance).

return (from a in db.Accounts
    where a.accountname.Contains(searchText.Substring(0))

Answer 3

There is nothing wrong in theory i can see

Update

This is working fine for me on sqlServer

public IQueryable<Account> DoesNotReturnAnyData(MyEntities db,string searchText) {
    return (from a in db.Accounts
        where a.accountname.Contains(searchText )
        select a)
}