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?

Please include the bug you submitted to MySQL. I am having the same issue — Matt
– Matt, Commented Jan 7, 2015 at 6:33
Seeing same issue w/ 6.9.5, rolling back to "Install-Package MySql.Data.Entities.EF6 " which is 6.8.3 as noted by comment from @Ablue (also: bugs.mysql.com/bug.php?id=75193 and bugs.mysql.com/bug.php?id=74918 and bugs.mysql.com/bug.php?id=74943) Yuck! — sobelito
– sobelito, Commented Jan 19, 2015 at 21:35
yes, 6.9.3 works okay. luckily nuget has that package, oracle is less than helpful with finding it. There is certainly no sense of urgency towards fixing this bug. — Matt
– Matt, Commented Jan 19, 2015 at 22:34

chrizzl · Accepted Answer · 2015-01-06 12:39:48Z

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);
}

TheGeneral · Accepted Answer · 2014-12-07 01:39:10Z

0

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)
}

edited Dec 7, 2014 at 1:39

answered Dec 7, 2014 at 1:05

TheGeneral

82.1k9 gold badges111 silver badges152 bronze badges

8 Comments

Phil Cazella Over a year ago

Yes, you are right in that the context is disposed before returning (I've since change to using a class property for the context) Using the .ToList() function does force the query. I tried it, but had the same results. Works when hard coded, does not work when using a string variable.

Phil Cazella Over a year ago

I know there were some changes to the .Contains, .StartsWith, & .EndsWith extensions in the latest .Net connector for MySQL that had to do with the use of the keyword 'LIKE'. I'm almost wondering if this is a new bug. Is there another way to do the same thing that I could test?

TheGeneral Over a year ago

Just updated, the above works fine on SQL server, you could be right, however i don't use mysql so i cant test that for you unfortunatly

TheGeneral Over a year ago

I guess you could try StartsWith or even just a straight comparison Ie == with your searchText, see what happens

Phil Cazella Over a year ago

where a.accountname.StartWith(searchText) Fails

|

jsallaberry · Accepted Answer · 2015-03-19 19:45:28Z

0

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))

answered Mar 19, 2015 at 19:45

jsallaberry

3182 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQL Linq using .Contains(variable)

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related