Geek Answers Handbook

Build Expression Tree is converted to valid SQL dynamically, which can compare a row with doubling

I have the following table in SQL Server:

ProductAttribute

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

This maps through the Entity Framework to my class:

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

Some ProductAttributes strings are as follows:

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

I need to dynamically build ExpressionTree so that it is converted to SQL , which can do the following:

  • If the value starts with a number followed by or not an alphanumeric string, extract the number and compare it with the 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 annoying thing is that I need to build this expression tree dynamically .

So far I have dealt with this (this is considered a value as decimal not as a string, so it doesn’t even try to do the whole contents of regular expressions):

 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 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 listing to double, and then a numerical comparison:

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

Obviously, this does not convert to SQL: double.Parse() .

How can I create a translation so that it can be parsed in SQL from my expression?

+6
source share
3 answers

I think that it makes sense for Jakub Massad to ask what SQL should look like. If there is no way to write SQL that executes your query, how can there be an expression tree that translates to the required SQL?

The main problem is that regular expression is not natively supported by SQL Server. You can import the CLR function into your database and use it in UDF, but this is not the easiest way to make it work with EF.

So, again, start by creating SQL to do the job.

Now I found this little stone that extracts the numerical (left) part from the string:

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

This will return "3000" from "3000 KB."

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

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

Which, obviously, will return from your examples 8 and 3000 .

Now the tricky part is done, you can use this result to apply the predicate to this numerical 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 will see that every time you use numPart in the LINQ statement, all this PatIndex material PatIndex repeated in the SQL statement (even if you wrap it in a subquery). Unfortunately, this is how SQL works. It cannot store a temporary result in a statement. Well, the language specification is over 40 years old, quite good.

+2
source

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

+1
source

I'm going to go with the impossible.

How would you reliably retrieve a number using SQL? You cannot use regex. Best of all, you can find some kind of separator between the possible number and the text that your test data does not always have: "RAM" has a space of "8 GB", but "Cache" is not in the "300 KB" ".

0
source

More articles:


All Articles