String comparison

[sviluppo]

Hi all,

Is there a way to compare two string in the where clause?

Where
(FIELD1 > “SOMETHING”)
AND ((FIELD2 > “SOMETHING2”) OR (FIELD3 > “SOMETHING3”))

I tryed:

public Expression CompareLessThanOrEqualTo(Expression e1, Expression e2)
{
var compare = Expression.Call(typeof(string),
“Compare”,
null,
new[] { e1, e2 });

        return Expression.LessThanOrEqual(compare, Expression.Constant(0));

}

var param = Expression.Parameter(typeof(vvTest), “x”);
var member = Expression.Property(param, “field1”);
var body = CompareLessThanOrEqualTo(member, Expression.Constant(“ZZ”));
var exp = Expression.Lambda<Func<vvTest, bool>>(body, param);

If I perform exp, it works. If I use this predicate inside a complex expression it does not work because the parameter is not defined.

Than, I tried compareTo, but OrmLite does not recognize this method.

thanks so much.

[mythz]

You should just use the natural API:

q.Where(x => x.Field1 > "SOMETHING" 
    && (x.Field2 > "SOMETHING2" || x.Field3 > "SOMETHING3"));

OrmLite needs the concrete types when constructing the Where expression in order to access the table metadata which is sometimes lost when trying to dynamically construct lambda expressions.

[sviluppo]

thanks Mithz,

Premise:

I have to build the where clause dynamically. Using a recursive function, I have to nest different predicates defined by the user.

Filed (==, >, >=, etc) Value

Filed and value can be int, float, string or date.

Declaring the expression dinamically, I can not use (==, >, >= etc) for the string comparison.

Expression<Func<vvTest, bool>> xb = (x) => (x.field1 > “XXX”);

The best would be:

var parameter = Expression.Parameter(typeof(vvTest), “x”);
var member = Expression.Property(parameter, ‘field1’);
var constant = Expression.Constant(value);
var body = Expression.GreaterThan_FOR_ALL_TYPES(member, constant);

var finalExpression = Expression.Lambda<Func<vvTest, bool>>(body, parameter);

maybe, It would be enought:

Expression<Func<vvTest, bool>> xb = (x) => (x.field1.CompareTo(“XXX”) > 0);

Is there a way to do this?

thank you very much

[mythz]

It needs to use the > operator or more explicitly a BinaryExpression with the ExpressionType.GreaterThan/ExpressionType.GreaterThanOrEqual/etc node type.

Honestly if you’re constructing expressions dynamically just create SQL string fragments and use the UnsafeWhere(string)/UnsafeAdd/UnsafeOr APIs. You could also construct an entire SQL statement and execute them through db.SqlList<Table>(sql).

Also have you evaluated if using AutoQuery RDBMS is a valid solution?

[sviluppo]

Hi Mithz,

Hi Mithz,

I have changed some lines of code in SqlExpression.cs. (I have renamed the file in jpg for uploading it).

You have to declar a static class with the following methods.

public static class StrCompare
{
public static bool StringGreaterThan(string str, string val)
{
return string.Compare(str, val) > 0;
}
public static bool StringGreaterThanOrEqual(string str, string val)
{
return string.Compare(str, val) >= 0;
}
public static bool StringLessThan(string str, string val)
{
return string.Compare(str, val) < 0;
}
public static bool StringLessThanOrEqual(string str, string val)
{
return string.Compare(str, val) <= 0;
}
}

You can write a lambda expression dinamically as follows:

var parameter = Expression.Parameter(…);
var member = Expression.Property(parameter, “hfkjdshkf”);

                body = Expression.Call(typeof(StrCompare),
                                   "StringGreaterThan",
                                   null,
                                   new[] { member, constant });

the ORM “translate” the lambda using the function VisitStaticStringMethodCall.

In my opinion, it works. Could you check the solution, please?
Thanks

[mythz]

I mean if it works with your customized version of OrmLite that’s great, I’d personally create custom SQL fragments myself.

The Custom classes is not a change we can add to OrmLite/master tho.

[sviluppo]

yes, but I can not because the program is cross DB: oracle, sql server and postgres. I would prefer not to use unsafe sql.

I think that my changes can be useful fom many developer. Could you include them in a next version of the orm, please?

If not, could you give me your opinion about them? are they dangerous? can they generate problems?

Thanks a lot

[mythz]

They include artificial Custom classes that are unintuitive and undiscoverable, i.e. we’d add code bloat to OrmLite that only you would think about consider using. If you can modify it to work with string.Compare() directly we could accept it.

The < > operators are universal you can easily create SQL that will work in each database, e.g. AutoQuery just uses this template to generate its SQL complaint condition:

const string GreaterThanOrEqualFormat = "{Field} >= {Value}";
const string GreaterThanFormat =        "{Field} >  {Value}";

[sviluppo]

you are right.

string.Compare() returns an int value. I need boolean values for joining expressions with predicatebuilder.

I tryed to nest Expression.Call (… “compare” … ) inside a graterThen, but it does not work because it does not see the parameters.

I do not want to modify the original code. Can I ovverride the SqlExpression class? How can I use the ovverrided one instead of the original?

[mythz]

You can override SqlExpression<T> but each RDBMS has their own SqlExpression classes so you’d need to override it for each you want to support e.g. SqlServerExpression, SqliteExpression, etc.

To get OrmLite to use it you’d also need to override the Dialect Provider to return your custom class, e.g:

public override SqlExpression<T> SqlExpression<T>()
{
    return new CustomSqlServerExpression<T>(this);
}

[sviluppo]

Thank you Mythz.

I am sorry, I need to override:

private static bool IsStaticStringMethod(MethodCallExpression m)

but it is private.

any suggestion, please?

[mythz]

Easiest way is to send a PR to change the visibility of APIs you need like I’ve done in this commit.

This change is available from v4.5.9 that’s now available on MyGet.

[sviluppo]

Hi Mythz
thank you very much for your help