ToLower in Array.Contains in Entity Framework Core
entity-framework entity-framework-core postgresql
Question
It seems that in EF Core's query like myContext.Books.Where(b => new [] { "harry potter", "the lord of the rings" }.Contains(b.Title.ToLower())) filtering part is not executed on sql server, no 'where' close is built. I'm using postgresql and npgsql driver, but I've checked that the same behavior is for ms sql server.
So, do you know if it's possible to built query which will generate sql where clause like this (for postgresql):
where lower(title) in ('harry potter', 'the lord of the rings')
Accepted Answer
You are hitting limitation EF Core SQL translation. Upto release 1.1.2 EF core assumed that in Array.Contains(item), item would be member expression or EF.Property method call indicating property access. But in your case, you have a different method call because you are calling ToLower() hence EF Core fails to recognize the pattern and does not translate Contains to server.
Co-incidentally this limitation was removed while fixing a different issue.
Now EF Core translate item if possible. Since ToLower() is can be translated it works correctly and generate IN statement. That issue is fixed in 2.0.0-preview1. So it should be available in 2.0 release of EF Core.
I tested the query with nightly build of 2.0.0 of EF Core. Following is the generated SQL.
SELECT [x].[Id], [x].[Title]
FROM [Blogs] AS [x]
WHERE LOWER([x].[Title]) IN (N'harry potter', N'the lord of the rings')
Popular Answer
Your linq statement appears to be a little off for doing a loose matching is all.
class Program
{
static void Main(string[] args)
{
var contextMock = new List<Book>
{
new Book(1, "Harry Potter and The Sorcerer's Stone", "Fiction"),
new Book(2, "Harry Potter and The Secret Chamber", "Fiction"),
new Book(3, "Dune", "Fiction"),
new Book(4, "The Lord of The Rings The Fellowship of the Ring", "Fiction"),
new Book(5, "The Lord of The Rings Return of the King", "Fiction"),
new Book(6, "A Brief History of Time", "NonFiction")
};
var wrong = contextMock.Where(x => (new[]{ "harry potter", "the lord of the rings" }).Contains(x.Title.ToLower())).ToList();
var right = contextMock.Where(x => (new List<string> { "harry potter", "the lord of the rings" }).Any(y => x.Title.ToLower().Contains(y.ToLower()))).ToList();
Console.ReadLine();
}
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow