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