Description:
My scenario is a code-first EF 6.1.1 data layer DLL (C# .NET 4.5.1). I'm using the latest versions of MySql connector (MySql.Data and MySql.Data.Entity.EF6, both at version 6.9.5.0), in a project upgraded from version 6.8.x. The project has a number of unit tests which were all successful before the upgrade; after it, I'm getting failures whenever (at least it seems so) a string comparison with LIKE should be used.
If I inspect the generated SQL with the debugger, I can see some artifacts and what seems an unresolved parameter in the built SQL; it looks like this (note the "p__linq__0" value between %% in the LIKE clause; apart from this, the subquery with C1 looks a little weird but I'm not sure this is related):
SELECT
`Project1`.`C1`,
`Project1`.`Id`,
`Project1`.`CategoryId`,
`Project1`.`Title`,
`Project1`.`Subtitle`,
`Project1`.`Author`,
`Project1`.`Tag`,
`Project1`.`Isbn`,
`Project1`.`Description`,
`Project1`.`IsPresent`,
`Project1`.`Year`,
`Project1`.`Edition`,
`Project1`.`Note`,
`Project1`.`Id1`,
`Project1`.`Name`
FROM (SELECT
`Extent1`.`Id`,
`Extent1`.`CategoryId`,
`Extent1`.`Title`,
`Extent1`.`Subtitle`,
`Extent1`.`Author`,
`Extent1`.`Tag`,
`Extent1`.`Isbn`,
`Extent1`.`Description`,
`Extent1`.`IsPresent`,
`Extent1`.`Year`,
`Extent1`.`Edition`,
`Extent1`.`Note`,
`Extent2`.`Id` AS `Id1`,
`Extent2`.`Name`,
1 AS `C1`
FROM `Book` AS `Extent1` INNER JOIN `Category` AS `Extent2` ON `Extent1`.`CategoryId` = `Extent2`.`Id`
WHERE `Extent1`.`Title` LIKE '%p__linq__0%') AS `Project1`
ORDER BY
`Project1`.`Title` ASC,
`Project1`.`Year` ASC LIMIT 0,20
The DDL SQL for the relevant tables is:
a) for category:
CREATE TABLE `category` (
`Id` varchar(5) NOT NULL,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
b) for book:
CREATE TABLE `book` (
`Id` varchar(50) NOT NULL,
`CategoryId` varchar(5) NOT NULL,
`Title` varchar(100) NOT NULL,
`Subtitle` varchar(200) NOT NULL,
`Author` varchar(100) NOT NULL,
`Tag` varchar(50) NOT NULL,
`Isbn` varchar(20) NOT NULL,
`Description` varchar(5000) NOT NULL,
`IsPresent` tinyint(1) NOT NULL,
`Year` smallint(6) NOT NULL,
`Edition` smallint(6) NOT NULL,
`Note` varchar(5000) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IX_CategoryId` (`CategoryId`) USING HASH,
CONSTRAINT `FK_Book_Category_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `category` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The code I'm using in my repository looks like the excerpt pasted below; the method gets an options object with the paging and filtering parameters, and should return a page of items matching them:
public IEnumerable<Book> GetBooksPage(BookFilterOptions options)
{
if (options == null) throw new ArgumentNullException("options");
using (CatalogContext db = CreateCatalogContext())
{
var books = db.Books.Include("Category").AsNoTracking().AsQueryable();
#if DEBUG
// logging for diagnosing generated SQL
db.Database.Log = s => Debug.WriteLine(s);
#endif
if (!String.IsNullOrEmpty(options.Title))
books = books.Where(b => b.Title.Contains(options.Title));
if (!String.IsNullOrEmpty(options.CategoryId))
books = books.Where(b => b.CategoryId == options.CategoryId);
if (options.IsPresent.HasValue)
books = books.Where(b => b.IsPresent == options.IsPresent.Value);
if (options.MinYear > 0)
books = books.Where(b => b.Year >= options.MinYear);
if (options.MaxYear > 0)
books = books.Where(b => b.Year <= options.MaxYear);
if (options.Edition > 0)
books = books.Where(b => b.Edition == options.Edition);
books = books.OrderBy(b => b.Title).ThenBy(b => b.Year);
Book[] aPageBooks = (options.PageSize == 0
? books.ToArray()
: books.Skip((options.PageNumber - 1) * options.PageSize).Take(options.PageSize).ToArray());
return aPageBooks;
}
}
If I avoid setting the title filter, this works fine as no string filtering is involved. As soon as I set the title filter, I get no record as the generated SQL seems broken.
Similarly, all the tests involving string comparisons with LIKE seem broken too, while others pass.
How to repeat:
At present I did not manage to reproduce the issue if I just use a couple of tables extracted from the real database. I tried using a fake data context with just these two tables and make a similar query, but this returned an SQL where the LIKE parameter correctly contains the string value of my filter. The whole repro code was:
const int nPageSize = 20, nPageNumber = 1;
using (CatalogContext db = new CatalogContext())
{
var books = db.Books.Include("Category").AsNoTracking().AsQueryable();
books = books.Where(b => b.Title.Contains("test"));
books = books.OrderBy(b => b.Title).ThenBy(b => b.Year);
Book[] aPageBooks = (nPageSize == 0
? books.ToArray()
: books.Skip((nPageNumber - 1) * nPageSize).Take(nPageSize).ToArray());
Console.WriteLine(aPageBooks.Length);
}