String.Compare in async method and EntityFramework Core

Question

I tried these :

public async Task<bool> NameExists(string name)
{
    var productCategories = await this._dbContext.ProductCategories
        .Where(c => String.Compare(c.Name, name, StringComparison.InvariantCultureIgnoreCase) > 0).ToListAsync();

    return productCategories.Count > 0;
}

public async Task<bool> NameExists(string name)
{
    int count = await this._dbContext.ProductCategories
        .CountAsync(c => String.Compare(c.Name, name, StringComparison.InvariantCultureIgnoreCase) > 0).t

    return count != 0;
}

but in both cases I get this error message :

System.InvalidOperationException: 'The LINQ expression 'DbSet<ProductCategory>()
    .Where(p => string.Compare(
        strA: p.Name, 
        strB: __name_0, 
        comparisonType: InvariantCultureIgnoreCase) > 0)' could not be translated

I searched around the web but don't find any solution.

As temp "solution" I use this :

public async Task<bool> NameExists(string name)
{
    return await this._dbContext.ProductCategories.AnyAsync(x => x.Name.Trim().ToLower() == name.Trim().ToLower());

Do you have an idea how solve this ?

Thanks, }

I hope this link helps u: https://stackoverflow.com/questions/10202735/linq-is-this-string-compare-in-the-query-inefficient-and-is-there-a-better-way. String.Compare is not in the list of functions supported by entity framework. — D A, Nov 15 '21 at 10:27
Or this one: https://stackoverflow.com/questions/841226/case-insensitive-string-compare-in-linq-to-sql — Jeroen van Langen, Nov 15 '21 at 10:28

score 0 · Answer 1 · answered Nov 15 '21 at 11:33

You can force a case-insensitive collation, see the documentation for more details

public async Task<bool> NameExists(string name)
{
    var productCategories = await this._dbContext.ProductCategories
        .Where(c => EF.Functions.Collate(c.Name, "Latin1_General_CI_AS") == name)
        .ToListAsync();

    return productCategories.Count > 0;
}

Setting the collation on that column to be case-insensitive would mean you don't need any of this. The comparison would automatically be case-insensitive, and could use an index.

This particular query is in any case inefficient. You only want to know about existence, so use .Any instead of .Count

public async Task<bool> NameExists(string name)
{
    var exists = await this._dbContext.ProductCategories
        .Where(c => EF.Functions.Collate(c.Name, "Latin1_General_CI_AS") == name)
        .Any();

    return exists;
}

String.Compare in async method and EntityFramework Core

1 Answers1