Can we use ToLowerInvariant() in LINQ to SQL?-CodePudding

I have a method to retrieve Restaurant table results using DbContext and EF Core with the following code:

    public IEnumerable<Restaurant> GetRestaurantByName(string? name = null)
    {
        if (string.IsNullOrEmpty(name))
        {
            return _dbContext.Restaurants;
        }
        else
        {
            return _dbContext.Restaurants
                .Where(r => r.Name.ToLower().Contains(name.ToLowerInvariant()));
        }
    }

The problem is when I try to use r.Name.ToLowerInvariant() instead of r.Name.ToLower() I catch an exception with the following detail:

InvalidOperationException: The LINQ expression 'DbSet() .Where(r => r.Name.ToLowerInvariant().Contains(__ToLowerInvariant_0))' could not be translated.

I like to understand the exact reason and logic behind the scene.

CodePudding user response：

I like to understand the exact reason and logic behind the scene.

You need to appreciate that EFC is a translator. It looks at the LINQ query you have built and translates it piece by piece into SQL, then it runs the SQL

When you say:

context.Persons.Where(p => p.Name == "SMITH")

It becomes:

SELECT * FROM persons WHERE name = 'SMITH'

When you say:

context.Persons.Where(p => p.Name.ToLower() == "SMITH")

It becomes:

SELECT * FROM persons WHERE LOWER(name) = 'SMITH'

These two probably function the same anyway; C# is case sensitive but SQL Server probably isn't (by default it uses insensitive collation) so even though the C# looks like nonsense:

Name.ToLower() == "SMITH" //this would never return anything in C#

SQLServer in insensitive mode will run it and return results:

LOWER(name) = 'SMITH' --SQLServer will return

You have to remain conscious of the fact that your C# is translated into another language and run according to the rules of that language, not C#

If you want to see what your query becomes, don't run it:

var q = context.Persons.Where(p => p.Name.ToLower() == "SMITH")
  //.ToList() //don't run it

Now point to q in the debugger and inspect the DebugView- EFC will tell you what SQL it generates (EFC5 feature)

Now remember we said it's a translation exercise - EFC can only translate what it knows - that's for sql server, different providers translate differently. If you use a function that it doesn't know, like ToLowerInvariant, you get an error. No one at Microsoft(or whoever wrote the provider you're using) has ever sat down and worked out a way to convert ToLowerInvariant into SQL in a meaningful way.

If you make your LINQ too complex you also get an error; you have to find another way to write the LINQ that can be translated, or take the hit and download a (?huge) amount of data into C# and process it there

CodePudding user response：

This is a problem that I always suffer from and hard to debug. The where function is somehow limited when it comes to using Functions inside DB.

Here is an example:

DataContext.Products.Where(product => product.Count > 5); \\ This works fine
DataContext.Products.Where(product => product.Name == "Mac".ToLower()); \\ Also works fine because "Mac" will be converted into "mac" before being inserted in the query, so it's value is handled in the app, not db server, and will be in the query as a static value.
\\ Now here let's make a call on the product
DataContext.Products.Where(product => product.Name.ToLowerInvariant() == "mac"); \\ Throws an exception because function ToLowerInvariantis not registered in the DB, so the DB doesn't know how to call this function or handle it.

and the same thing happens if you try to call functions inside these where statements that work against DB

public bool HasItems(Product prod) => prod.Count > 0;
DataContext.Products.Where(prod => HasItems(Prod)); // Also throws the same exception cause the function is again is not mapped to any DB Function.

What if you try to make HasItems as Getter Property Only?

public class Product {
    public int Count {get;set;}
    public bool HasItems => Count > 0;
}

DataContext.Products.Where(prod => prod.HasItems); 
// Will throw an exception, cause migration won't register HasItems as computed column, so this property is not known in the DB and it doesn't know how to get its value.

So as long as functions or columns you try to call are not registered in the DB Server, and the query will work inside the DB Server, it will throw an exception. But if you use the same queries with data that are available in the app, they will work just fine.