Add capability for utilizing "DbFunctions"

[fiseni]

I wanted this feature for quite some time. I want to utilize DbFunctions (e.g. Like) through specifications somehow. In production not rarely we need "Like" queries. It offers some quasi full-text search. Not applicable everywhere, but there are cases where it's quite useful.
The idea is to have the following usage. In specification, we should have the capability to define what we want to search for:

public class CustomerSpec : Specification<Customer>
{
	public CustomerSpec(string searchTerm)
	{
		Query.Search(x => x.Name, searchTerm)
			.Search(x => x.BIC, searchTerm);
	}
}

And once you have those information stored somehow, it should be utilized into something as below

dbContext.Customers.Where(x => EF.Functions.Like(x.Name, searchTerm) ||
                                EF.Functions.Like(x.BIC, searchTerm));

The way how DbFunctions are parsed internally in EF, makes it really tricky to implement this. Also, if you noticed, you should use the "x" of Where's Func argument (not just provide expression in Like), otherwise won't be parsed correctly. I tried to implement this, and it includes complete expression reconstruction, a lot of reflection.. and shortly said, I failed badly :)

I do believe someone can come up with much brighter idea, and you're more than welcome to do so.

[fiseni]

This feature is implemented. If users want this functionality I can prepare a PR here.

The implementation complexity is hidden, and clean usage is provided. If you define following specification:

public class CustomerSpec : Specification<Customer>
{
    public CustomerSpec(string searchTerm)
    {
        Query.Search(x => x.Name, searchTerm)
             .Search(x => x.Address, searchTerm);
    }
}

The evaluator will translate this automatically into

dbContext.Customers.Where(x => EF.Functions.Like(x.Name, searchTerm) ||
                                EF.Functions.Like(x.Address, searchTerm));

You can also provide separate/different search terms for each property. The Search expressions ultimately will be evaluated into OR conditions while building the query. If you need AND condition, you can provide additional paremeter SearchGroup to Search. The usage is as following:

public class CustomerSpec : Specification<Customer>
{
    public CustomerSpec(string searchTerm)
    {
        Query.Search(x => x.Name, searchTerm, 1)
             .Search(x => x.Address, searchTerm, 1);
             .Search(x => x.Phone, searchTerm, 2);
    }
}

The evaluator will translate this into

dbContext.Customers.Where(x => EF.Functions.Like(x.Name, searchTerm) ||
                                EF.Functions.Like(x.Address, searchTerm))
		   .Where(x => EF.Functions.Like(x.Phone, searchTerm));

[ardalis]

I like it but am unsure about the searchGroup implementation. Why is it intuitive that Name and Address, belonging to group 1, are OR'd together while Phone is ANDed with them since it's a different group? I'm not sure I have a better syntax but we should think about it more perhaps.

Possible options:

• OrGroup, AndGroup (still might be confusing - are the groups OR'd or their contents?)
• AnyGroup, AllGroup (LINQ-like)

[fiseni]

The actual numbers don't hold any importance. Whatever number, it just has to be the same within the group. Then, any group will OR within itself. I added this in the last minute, so it might not be the best syntax. Anyhow, we need some identifier for groups.

This is how it is evaluated afterward:

foreach (var searchCriteria in specification.SearchCriterias.GroupBy(x => x.SearchGroup))
{
    var criterias = searchCriteria.Select(x => (x.Selector, x.SearchTerm));
    query = query.Search(criterias);
}

The Search method is an extension I wrote for IQueryable, which does the actual job.

public static class SearchExtension
{
    public static IQueryable<T> Search<T>(this IQueryable<T> source, IEnumerable<(Expression<Func<T, string>> selector, string searchTerm)> criterias)
    {
        Expression? expr = null;
        var parameter = Expression.Parameter(typeof(T), "x");

        foreach (var criteria in criterias)
        {
            if (criteria.selector == null || string.IsNullOrEmpty(criteria.searchTerm))
                continue;

            var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
            var like = typeof(DbFunctionsExtensions).GetMethod(nameof(DbFunctionsExtensions.Like), new Type[] { functions.Type, typeof(string), typeof(string) });

            var propertySelector = ParameterReplacerVisitor.Replace(criteria.selector, criteria.selector.Parameters[0], parameter);

            var likeExpression = Expression.Call(
                                    null,
                                    like,
                                    functions,
                                    (propertySelector as LambdaExpression)?.Body,
                                    Expression.Constant("%" + criteria.searchTerm + "%"));

            expr = expr == null ? (Expression)likeExpression : Expression.OrElse(expr, likeExpression);
        }

        return expr == null
            ? source
            : source.Where(Expression.Lambda<Func<T, bool>>(expr, parameter));
    }
}

You will need the parameter replacer as well.

internal class ParameterReplacerVisitor : ExpressionVisitor
{
    private Expression newExpression;
    private ParameterExpression oldParameter;

    private ParameterReplacerVisitor(ParameterExpression oldParameter, Expression newExpression)
    {
        this.oldParameter = oldParameter;
        this.newExpression = newExpression;
    }

    internal static Expression Replace(Expression expression, ParameterExpression oldParameter, Expression newExpression)
    {
        return new ParameterReplacerVisitor(oldParameter, newExpression).Visit(expression);
    }

    protected override Expression VisitParameter(ParameterExpression p)
    {
        if (p == this.oldParameter)
        {
            return this.newExpression;
        }
        else
        {
            return p;
        }
    }
}

[fiseni]

Ok, @ardalis pointed out something to me, and I had to crosscheck this.

In the previous EF versions, if you wanted to query some substring in a given column, and if you do it in the following way

var result = await dbContext.Customers
                            .Where(x => x.Address.Contains("mer2"))
                            .ToListAsync();

then, the EF was not able to translate this correctly, and the search was done in memory after the data has been retrieved from DB. But, in EF Core 3, it seems this behaviour is changed, and EF can evaluate much more complex expressions now. So, the expression will be translated into the following SQL query

exec sp_executesql N'SELECT [c].[Id], [c].[Address], [c].[Email], [c].[Name]
FROM [Customer] AS [c]
WHERE (@__filter_Address_0 = N'''') OR (CHARINDEX(@__filter_Address_0, [c].[Address]) > 0)'
,N'@__filter_Address_0 nvarchar(4000)',@__filter_Address_0=N'mer2'

On the other hand, if you utilize the EF DbFunctions, this is what you get

var result = await dbContext.Customers
                            .Where(x => EF.Functions.Like(x.Address, "%mer2%"))
                            .ToListAsync();

SELECT [c].[Id], [c].[Address], [c].[Email], [c].[Name]
FROM [Customer] AS [c]
WHERE [c].[Address] LIKE N'%mer2%'

As a conclusion, if you want to query a substring, then you might use "Contains" as well. The new Search feature seems redundant after EF Core 3 :). Actually, performace wise, I'm not sure which SQL query is more efficient, we have to cross check that.

[ardalis]

So should we leave in the Search or roll it back and just rely on .Contains? Do we need to do some perf analysis to know for sure?

[fiseni]

Actualy, they produce quite different results.

• Where(x => x.Address.Contains(temp)) - translates into CHARINDEX in SQL
• Where(x => EF.Functions.Like(x.Address, "%temp%")) - translates into LIKE in SQL

If we wanna find something by substring, then indeed these functionalities will overlap. But, that's where the similarities end.
LIKE is much powerful than CHARINDEX, and offers some complex pattern matching. For an instance we can search for all strings which first character is A,B,C,D [A-D]%.

Since we already have it, I'd say let's keep it. If you have a reason why not, we can rollback.

PS. I have done this analysis in the past, can't remember exactly the details (should have blogged). At one point Contains() failed me and I stopped using it completely. I'm not sure if it was EF version related, or something more specific. The type of the columns, varchar vs nvarchar? Unicode characters? or maybe something with SQL collations?

[ardalis]

Ok, sounds good.

[ardalis]

Done.