Custom Query Extension, which works with NHibernate

Question

I am trying to write a custom Linq extension, which can also be performed within the DB.

Basically, what I want to do is collectionOfStrings.Where(x => !x.IsNullOrWhiteSpace) Unfortunately it's not supported.

=============== What I've tried so far =============

This part is only interesting to those, who might come up with another idea apart from the one below.

There is a workaround by going this way collection.Where(x => x != null && x.Trim() != string.Empty), but since I use it frequently, it's not the best solution.

The prettiest solution would be, to find a way to write a string extension IsNullOrWhiteSpaceDB, which works or to kind of add the IsNullOrWhiteSpace method to the database programmatically to ensure support.

That's been my attempt to create a working IsNullOrWhiteSpace method, but it's not supported too:

public static bool IsNullOrWhiteSpaceDB(this string? str) =>
    str == null || str.Trim() == String.Empty;

So I've started writing a predicate, which is working fine:

    public IQueryable<string> GetAll() =>
        GetAll().Select(x => x.property).Where(StringIsNotNullOrWhiteSpace).Distinct();

    private static Expression<Func<string?, bool>> StringIsNotNullOrWhiteSpace =>
        x => x != null && x.Trim() != string.Empty;

=============== The current problem =============

Neverthless I'd actually like to be able to run it on another collection than on a collection of strings. So I tried to build a custom linq extension (inspired by this solution (https://stackoverflow.com/a/40924558/9487478)):

public class QueryVisitor : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if (node.Method.IsStatic && node.Method.Name == "IsNullOrWhiteSpace")
        {
            //!(b.Diameter == null || b.Diameter.Trim() == string.Empty)
            var arg = node.Arguments[0];
            var argTrim = Expression.Call(arg, typeof(string).GetMethod("Trim", Type.EmptyTypes));

            var exp = Expression.MakeBinary(ExpressionType.Or,
                Expression.MakeBinary(ExpressionType.Equal, arg, Expression.Constant(null, arg.Type)),
                Expression.MakeBinary(ExpressionType.Equal, argTrim, Expression.Constant(string.Empty, arg.Type))
            );

            return exp;
        }

        return base.VisitMethodCall(node);
    }
}

public static class EfQueryableExtensions
{
    public static IQueryable<T> WhereEx<T>(this IQueryable<T> queryable, Expression<Func<T, bool>> where)
    {
        var visitor = new QueryVisitor();
        return queryable.Where(visitor.VisitAndConvert(where, "WhereEx"));
    }
}

And that's what my custom extension actually looks like:

public static class QueryHelper
{
    public static IQueryable<T> WhereIsNotNullOrWhiteSpace<T>(this IQueryable<T> query, Expression<Func<T, string?>> expression)
    {
        var arg = expression.Body;
        var argTrim = Expression.Call(arg, typeof(string).GetMethod("Trim", Type.EmptyTypes));

        var exp = Expression.MakeBinary(ExpressionType.And,
            Expression.MakeBinary(ExpressionType.NotEqual, arg, Expression.Constant(null, arg.Type)),
            Expression.MakeBinary(ExpressionType.NotEqual, argTrim, Expression.Constant(string.Empty, arg.Type))
        );

        var lambda = Expression.Lambda<Func<T, bool>>(exp, expression.Parameters);
        var result = query.Where(lambda);

        return result;
    }
}

After the query.Where(lambda) is executed there is an inner exception within the result:

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: A recognition error occurred.

The "original version" throws the same error too, so I thought it might be the created expression ((x == null) Or (x.Trim() == "")) (copied from the debugger). For me it looks actually quite good and I don't understand the cause of the error.

Any ideas? I would be pleased!

score 1 · Answer 1 · answered Nov 10 '20 at 16:39

You don't need to build an expression for this. You just need an extension method that takes and returns IQueryable<string>.

public static class Extensions
{
    public static IQueryable<string> IsNullOrWhiteSpaceDB(this IQueryable<string> input)
    {
        return input.Where(x => x != null && x.Trim() != string.Empty);
    }
}

score 1 · Answer 2 · answered Nov 10 '20 at 18:06

I have created PR for LINQKit which should simplify your life https://github.com/scottksmith95/LINQKit/pull/127 Idea is to add ExpandableAttribute to such methods which points to static function with expression for substitution.

public static class Extensions
{
    [Expandable(nameof(IsNotNullOrWhiteSpaceDBImpl))]
    public static bool IsNotNullOrWhiteSpaceDB(string str)
       => throw new NotImplementedException();

    public static Expression<Func<string, bool>> IsNotNullOrWhiteSpaceDBImpl()
        => x => x != null && x.Trim() != string.Empty;
}

So query should use AsExpandable() at least once. Put this call somewhere in repository.

 db.Users.AsExpandable()
   .Where(u => u.FirstName.IsNotNullOrWhiteSpaceDB() || u.MiddleName.IsNotNullOrWhiteSpaceDB())

Thanks for your effort and your suggestion! Is it just working with EntityFramework or also with NHibernate? Since we don't use EntityFramework. — Paul, Nov 11 '20 at 08:20
It works with any LINQ provider. Better to compile this branch by yourself. — Svyatoslav Danyliv, Nov 11 '20 at 08:53

Custom Query Extension, which works with NHibernate

2 Answers2