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?

Answer 1

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.

Answer 2

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.