Build Expression Tree convertible to valid SQL dynamically that can compare string with doubles

Question

I have the following table in SQL Server:

ProductAttribute

Name: nvarchar(100)
Value: nvarchar(200)

This is mapped via Entity Framework into my class:

public class ProductAttribute
{
   public string Name {get;set;}
   public string Value {get;set;}
}

Some rows of ProductAttributes have the following form:

{Name: "RAM", Value: "8 GB"}, {Name: "Cache", Value: "3000KB"}

I need to construct dynamically an ExpressionTree that is convertible to SQL that can does the following:

If the Value starts with a number followed or not by an alphanumeric string, extract the number and compare it with a given value

double value = ...;

Expression<Func<ProductAttribute, bool>> expression = p => 
{
            Regex regex = new Regex(@"\d+");
            Match match = regex.Match(value);

            if (match.Success && match.Index == 0)
            {
                matchExpression = value.Contains(_parserConfig.TokenSeparator) ? 
                    value.Substring(0, value.IndexOf(_parserConfig.TokenSeparator)) : 
                    value;

                string comparand = match.Value;
                if(double.Parse(comparand)>value) 
                    return true;
            }

            return false;
}

The really nasty thing is that I need to construct this expression tree dynamically.

So far I've managed this (this considers the value as a decimal not as a string, so it doesn't even try to do the whole regex stuff):

private Expression GenerateAnyNumericPredicate(
        Type type, 
        string valueProperty,
        string keyValue, 
        double value) {
        ParameterExpression param = Expression.Parameter(type, "s"); 
        MemberExpression source = Expression.Property(param, valueProperty);
        ConstantExpression targetValue = GetConstantExpression(value, value.GetType());
        BinaryExpression comparisonExpression = Expression.GreaterThan(source, targetValue);  

        return Expression.Lambda(comparisonExpression, param);
    }

EDIT: With the help provided below, this works:

  Expression<Func<ProductSpecification, bool>> expo = ps=> ps.Value.Substring(0, (SqlFunctions.PatIndex("%[^0-9]%", ps.Value + ".") ?? 0) - 1) == "1000";

But I also need a cast to double and then a numeric comparison that is:

  Expression<Func<ProductSpecification, bool>> expo = ps=> double.Parse(ps.Value.Substring(0, (SqlFunctions.PatIndex("%[^0-9]%", ps.Value + ".") ?? 0) - 1)) > 1000;

Obviously this is not convertible to SQL: double.Parse().

How could I construct the cast so it can be parsed into SQL from my Expression?

If the data is a-lot then you may face performance problem if this type of query is running again and again, probably INDEX cannot be used. Therefore a better way is to have a separate numeric column having numeric value only, you can update that column at the time of insert/update. — sallushan, Jul 19 '16 at 11:13
Indeed, this will have a performance impact, but for the moment this is not our main concern. And I don't have control for now over the underlying DB. This format is given and I gotta adapt unfortunately. — Tamas Ionut, Jul 19 '16 at 11:44

score 2 · Accepted Answer · edited May 23 '17 at 12:22

I think Yacoub Massad has a point by asking what the SQL should look like. If there is no way to write SQL that executes your query, how can there possibly be an expression tree that converts into the required SQL?

The main problem is that regex is not supported natively by SQL Server. You could import a CLR function into your database and use it in an UDF, but that's not the easiest way to make it work with EF.

So, again, start by imaging the SQL that would do the job.

Now I found this little gem that extracts the numeric (left) part from a string:

select left(@str, patindex('%[^0-9]%', @str+'.') - 1)

This would return "3000" from "3000KB".

Fortunately, we can use SqlFunctions.PatIndex to reproduce this in a LINQ statement:

from pa in context.ProductAttributes
select pa.Value.Substring(0, (SqlFunctions.PatIndex("%[^0-9]%", pa.Value + ".") ?? 0) - 1)

Which would obviously return 8 and 3000 from your examples.

Now the hard part is done, you can use this result to apply a predicate to this numeric part:

from pa in context.ProductAttributes
let numPart = pa.Value.Substring(0, (SqlFunctions.PatIndex("%[^0-9]%", pa.Value + ".") ?? 0) - 1)
where numPart .... // go ahead

You'll see that for each time you use numPart in the LINQ statement, this whole PatIndex stuff is repeated in the SQL statement (even if you'd wrap it in a subquery). Unfortunately, that's how SQL works. It can't store a temporary in-statement result. Well, the language specification is over 40 years old, not bad at all.

I've edited the question a bit. Your answer almost works, but I also need a cast string->double + comparison. Do you know how I can achieve this? — Tamas Ionut, Jul 19 '16 at 09:08
Sorry, that's not supported in LINQ to Entities. Type conversion is one great lack in EF. — Gert Arnold, Jul 19 '16 at 21:05

score 1 · Answer 2 · answered Jul 19 '16 at 17:42

Do NOT do this. Reason: comparing to doubles, would suggest, that you can say: RAM > 4, but 4 what? if you store 2000 KB, then it will be true, but if you store 8 MB, it will not, which is obviously false. Instead: store a normalized value for the double in the db next to your field, and comapre to this. If you already have the data, then better migrate.

score 0 · Answer 3 · answered Jul 18 '16 at 22:06

I'm going to go with impossible.

How would you reliably extract the number using SQL? You can't use regex. Best you can do is search for some sort of separator between the possible number and text, which your test data doesn't consistently have: "RAM" has a space in "8 GB", but "Cache" doesn't in "300kb".

Build Expression Tree convertible to valid SQL dynamically that can compare string with doubles

3 Answers3