EFcore where clause with custom function to determine type of comparison-CodePudding

What I'm trying to do

I have a repository function that I want to be able support searching by string Equals, Contains, StartsWith, EndsWith. I've created a simple extension method that wraps around these string functions, but EFCore seems unable to translate this.

Are there are any alternative, reusable approaches similar to this?

How I'm trying to do it

public enum StringComparisonType
{
    Equals,
    Contains,
    BeginsWith,
    EndsWith
}

public static bool CompareTo(this string inputText, string comparisonText, StringComparisonType comparisonType) => comparisonType switch
{
    StringComparisonType.Equals => inputText.Equals(comparisonText),
    StringComparisonType.BeginsWith => inputText.StartsWith(comparisonText),
    StringComparisonType.Contains => inputText.Contains(comparisonText),
    StringComparisonType.EndsWith => inputText.EndsWith(comparisonText),
    _ => throw new NotImplementedException($"{nameof(StringComparisonType)} {comparisonType} not currently supported.")
};

var searchText = "hello";
var comparison = StringComparisonType.BeginsWith;
_context.Records.Where(r => r.Text.CompareTo(searchText, comparison))

The problem with the approach

This throws an error along the lines of:

The LINQ expression could not be translated

Alternative approach

The only alternative I've found that works is just inlining the logic to determine the type of comparison to apply, but this is horrible to read, horrible to write, and is not reusable, e.g.

_context.Records
    .Where(r => comparison == StringComparisonType.Equals 
        ? r.Text.Equals(searchText) 
        : comparison == StringComparisonType.BeginsWith 
            ? r.Text.StartsWith(searchText) 
            : comparison == StringComparisonType.EndsWith 
                ? r.Text.EndsWith(searchText) 
                : r.Text.Contains(searchText))

I'm currently using EFCore 7.

CodePudding user response：

If you use it on predefined type (be Record in example) try something like this:

public static IQueryable<Record> WhereCompare(this IQueryable<Record> query, string comparisonText, StringComparisonType comparisonType) => comparisonType switch
{
    StringComparisonType.Equals => query.Where(r => r.Text.Equals(comparisonText)),
    StringComparisonType.BeginsWith => query.Where(r => r.Text.StartsWith(comparisonText)),
    StringComparisonType.Contains => query.Where(r => r.Text.Contains(comparisonText)),
    StringComparisonType.EndsWith => query.Where(r => r.Text.EndsWith(comparisonText)),
    _ => throw new NotImplementedException($"{nameof(StringComparisonType)} 
             {comparisonType} not currently supported.")
}

And then use it like this:

var result = _context.Records.WhereCompare(searchText, comparison).ToList();

Not tested, just quick answer

CodePudding user response：

I think you can modify your custom function to take an IQueryable as input and return a IQueryable in response. The return IQueryable would be one of the four comparison variations to want, but each should be translatable.

Something like:

public static IQueryable<T> CustomCompare<T>(this IQueryable<T> query, Func<T, string> selector, string comparisonText, StringComparisonType comparisonType)
    => comparisonType switch
    {
        StringComparisonType.Equals => query.Where(item => selector(item).Equals(comparisonText)),
        StringComparisonType.BeginsWith => query.Where(item => selector(item).StartsWith(comparisonText)),
        StringComparisonType.Contains => query.Where(item => selector(item).Contains(comparisonText)),
        StringComparisonType.EndsWith => query.Where(item => selector(item).EndsWith(comparisonText)),
        _ => throw new NotImplementedException($"{nameof(StringComparisonType)} {comparisonType} not currently supported.")
    };
...
var searchText = "hello";
var comparison = StringComparisonType.BeginsWith;
_context.Records.CustomCompare(r => r.Text, searchText, comparison));

This is off the top of my head, so I might not have the syntax entirely correct. (If the above has errors, please comment so that I can improve the answer.)

(Although posted independently, the above appears virtually identical to Kazbek earlier answer, with the exception of the use of property selector.)