Creating dynamic DLinq tuple comparison query

[Andrus]

I created dynamic extension methods for <= and < SQL comparison operators:

public static IQueryable<TLes sThanOrEqual<T> (this IQueryable<Tsou rce,
string property, object value);
public static IQueryable<TLes sThan<T>(this IQueryable<Tsou rce, string
property, object value);

For example

var q = db.Customers.Le ssThanOrEqual( "City", "London" );
var res = q.Count();

generates SQL

SELECT COUNT(*)
FROM Customers
WHERE City <= 'London';

I need to generate similar SQL statement for 2 property tuple comparison
(c1,c2)<=(v1,v2 ):

WHERE ... AND c1<=v1 AND ( c1<v1 OR c2<=v2 )

like (City,Id) <= ('London', 'CUST23' ):

WHERE ... AND City<='London' AND ( City<'London' OR Id<='CUST23' );

I tried

var q1 = q.LessThanOrEqu al( "City", "London" );
var q2 = db.Customers.Le ssThan( "City", "London" );
var q3 = db.Customers.Le ssThanOrEqual( "Id", "CUST23" );
var qResult = q1.Where( q2.Or(q3) );

but last line causes error.
How to fix ?
Should I use predicate builder or is it possible to combine IQueryable<T>
extension methods using OR ?
How to create general method which compares up to 6-property tuples (c1,c2,
.... c6 )<=(v1, v2, ... v6) where property names and values are passed as
arrays ?

Andrus.

[Marc Gravell]

Re: Creating dynamic DLinq tuple comparison query

OK; this seems to work... tested with LINQ-to-SQL, but nothing very
complex here (from a LINQ-provider perspective), so it should
translate fairly well to other providers.

Marc

using System;
using System.Linq;
using System.Linq.Exp ressions;
using System.Reflecti on;

namespace ConsoleApplicat ion32
{
static class Program
{
public static IQueryable<TWhe reLessThan<T>(t his
IQueryable<Tsou rce, T value, params string[] properties)
{
return WhereInequality (source, value, true, properties);
}
public static IQueryable<TWhe reGreaterThan<T >(this
IQueryable<Tsou rce, T value, params string[] properties)
{
return WhereInequality (source, value, false, properties);
}

private static IQueryable<TWhe reInequality<T> (
IQueryable<Tsou rce, T value,
bool lessThan,
params string[] properties) {

// sense-check argments
if (source == null) throw new
ArgumentNullExc eption("source" );
if (value == null) throw new
ArgumentNullExc eption("value") ;
if (properties == null) throw new
ArgumentNullExc eption("propert ies");
if (properties.Len gth == 0) throw new
ArgumentExcepti on("No properties to compare", "properties ");

// xParam is the item being tested
// testValue is the single set of values to use for
comparison
ParameterExpres sion xParam =
Expression.Para meter(typeof(T) , "x");
ConstantExpress ion testValue = Expression.Cons tant(value,
typeof(T));

// need a repatable mechanism to construct the inequality,
noting that
// for strings we need to additionally Expression.Call
string.Compare,
// since inequality is not directly defined for strings;
we'll re-use
// the overall inequality to aid in building the string-
comparison
Func<Expression , Expression, BinaryExpressio n>
overallComparis on, overallStringCo mparison;
if (lessThan)
{ // doesn't like the ternary conditional operator here...
overallComparis on = Expression.Less Than;
}
else
{
overallComparis on = Expression.Grea terThan;
}
Type[] twoStrings = new[] {typeof(string) ,
typeof(string)} ;
overallStringCo mparison = (x,y) =overallCompari son(
Expression.Call (typeof(string) .GetMethod("Com pare",
twoStrings), x, y),
Expression.Cons tant(0, typeof(int))
);

// build the expression backwards to simplify construction
// note: already checked non-zero props, so expr *will*
get set
Expression expr = null;
for (int i = properties.Leng th - 1; i >= 0; i--)
{
// locate the member (prop/field), and obtain a
// MemberExpressio n for both the row being tested and
// the known test values
string propName = properties[i];
MemberInfo member = typeof(T).GetMe mber(propName,
MemberTypes.Pro perty | MemberTypes.Fie ld,
BindingFlags.Pu blic | BindingFlags.No nPublic |
BindingFlags.In stance).Single( );
MemberExpressio n xMember, testMember;
bool isString;
switch (member.MemberT ype)
{
case MemberTypes.Fie ld:
isString = ((FieldInfo)mem ber).FieldType ==
typeof(string);
xMember = Expression.Fiel d(xParam,
(FieldInfo)memb er);
testMember = Expression.Fiel d(testValue,
(FieldInfo)memb er);
break;
case MemberTypes.Pro perty:
isString = ((PropertyInfo) member).Propert yType
== typeof(string);
xMember = Expression.Prop erty(xParam,
(PropertyInfo)m ember);
testMember = Expression.Prop erty(testValue,
(PropertyInfo)m ember);
break;
default:
throw new
NotSupportedExc eption(string.F ormat("{0} ({1})", member.Name,
member.MemberTy pe));
}

// build the comparison for the member being
considered,
// composing the existing expression (if any)
Func<Expression , Expression, BinaryExpressio n>
comparison = isString
? overallStringCo mparison :
overallComparis on;
if (expr == null)
{
expr = comparison(xMem ber, testMember);
}
else
{
expr =
Expression.OrEl se(
comparison(xMem ber, testMember),
Expression.AndA lso(
Expression.Equa l(xMember, testMember),
expr
)
);
}
}
// build a lambda and use it to filter the data
return source.Where(Ex pression.Lambda <Func<T, bool>>(expr,
xParam));
}
static void Main(string[] args)
{
using (var ctx = new DataClasses1Dat aContext())
{
ctx.Log = Console.Out;

var qry = ctx.Products.Wh ereLessThan(
new Product { ProductName = "foo", UnitPrice =
34.3M, UnitsInStock = 3 },
"QuantityPerUni t", "UnitPrice" , "UnitsInStock") ;

foreach (var row in qry)
{
Console.WriteLi ne("{0}: {1}", row.ProductName ,
row.ProductID);
}
}
}
}
}

[Andrus]

Re: Creating dynamic DLinq tuple comparison query

Marc,

OK; this seems to work... tested with LINQ-to-SQL, but nothing very
complex here (from a LINQ-provider perspective), so it should
translate fairly well to other providers.

Thank you.
I have found two issues with null values.

A. null property values.

DbLinq generates query like

SELECT COUNT(*)
FROM dok d$ WHERE d$.krdokumnr < NULL OR d$.krdokumnr = NULL AND d$.dokumnr
< '1495'

Whis in incorrect.
NULL values should probably be treated for sorting as empty string, 0, false
or DateTime.MinDat e depending on data type.

Should I create some pre-processor whcih replaces null values with those
fake values or try to create some other expression ?

B. NULL values in database columns.
Column values can also be nulls and cause incorrect result returned. Should
I bracket all column values with COALSESCE( ) calls (this may break using
database indexes) or try to modify expression to support nulls ?

Andrus.

[Marc Gravell]

Re: Creating dynamic DLinq tuple comparison query

Whis in incorrect.
Well, I'd have liked it if the = NULL had become IS NULL, but
otherwise it looks pretty OK to me...

NULL values should probably be treated for sorting as empty string, 0, false
or DateTime.MinDat e depending on data type.

Why? That isn't what NULL means... it is doing exactly what you
asked...

Should I create some pre-processor whcih replaces null values with those
fake values or try to create some other expression ?

This may be a daft question, but why not simply put zero/false/
DateTime.MinVal ue in your original tuple? If you want to compare to
zero, ask it about zero... note also that your db's zero datetime may
not be the same as .NET's zero datetime.

B. NULL values in database columns.
Column values can also be nulls and cause incorrect result returned.

I'd argue that the right results are being returned, under the SQL
rules for NULL. It is like you are asking it about bananas and then
complaining when it doesn't return pears... (IMO).

...COALESCE...

Except now you are talking about avacados... using TSQL COALESCE to
get the first non-null value is something completely different again!
Unless you just mean to use COALESCE as ISNULL (I normally think of
COALESCE for merging together multiple values (first non-null), and
ISNULL for defaulting a single value).
I might consider coalescing at the expression end, as though we had
done our original expression on "x.Foo ?? 0"; but IMO the whole idea
of comparing null as zero is deeply flawed anyway... you could try
Expression.Coal esce(member,
Expression.Cons tant(Activator. CreateInstance( type)), or
Expression.Cond ition(...), or Expression.Call (...) [on Nullable<T>'s
GetValueOrDefau lt()], but...

Marc

[Andrus]

Re: Creating dynamic DLinq tuple comparison query

I might consider coalescing at the expression end, as though we had

done our original expression on "x.Foo ?? 0"; but IMO the whole idea
of comparing null as zero is deeply flawed anyway... you could try
Expression.Coal esce(member,
Expression.Cons tant(Activator. CreateInstance( type)), or
Expression.Cond ition(...), or Expression.Call (...) [on Nullable<T>'s
GetValueOrDefau lt()], but...

I changed your code

case MemberTypes.Pro perty:
isString = ((PropertyInfo) member).Propert yType ==
typeof(string);
xMember = Expression.Prop erty(xParam,
(PropertyInfo)m ember);
testMember = Expression.Prop erty(testValue,
(PropertyInfo)m ember);
break;

to

case MemberTypes.Pro perty:
isString = ((PropertyInfo) member).Propert yType ==
typeof(string);
xMember = Expression.Prop erty(xParam,
(PropertyInfo)m ember);

if (((PropertyInfo )member).GetVal ue(value, null) !=
null)
testMember = Expression.Prop erty(testValue,
(PropertyInfo)m ember);
// handle null values
else if (isString)
testMember = Expression.Cons tant("");
else if (((PropertyInfo )member).Proper tyType ==
typeof(DateTime ?))
testMember =
Expression.Cons tant(DateTime.M inValue);
else if (((PropertyInfo )member).Proper tyType ==
typeof(Decimal? ))
testMember =
Expression.Cons tant(Decimal.Mi nValue);
else if (((PropertyInfo )member).Proper tyType ==
typeof(int?))
testMember = Expression.Cons tant(int.MinVal ue);
else throw new ArgumentExcepti on("unknown null
property type");
break;

but got compile errors

Cannot implicitly convert type 'System.Linq.Ex pressions.Const antExpression'
to
'System.Linq.Ex pressions.Membe rExpression'

Andrus.

[Andrus]

Re: Creating dynamic DLinq tuple comparison query

>I might consider coalescing at the expression end, as though we had

>done our original expression on "x.Foo ?? 0"; but IMO the whole idea
>of comparing null as zero is deeply flawed anyway... you could try
>Expression.Coa lesce(member,
>Expression.Con stant(Activator .CreateInstance (type)), or
>Expression.Con dition(...), or Expression.Call (...) [on Nullable<T>'s
>GetValueOrDefa ult()], but...

>
I changed your code
>
case MemberTypes.Pro perty:
isString = ((PropertyInfo) member).Propert yType ==
typeof(string);
xMember = Expression.Prop erty(xParam,
(PropertyInfo)m ember);
testMember = Expression.Prop erty(testValue,
(PropertyInfo)m ember);
break;
>
to
>
case MemberTypes.Pro perty:
isString = ((PropertyInfo) member).Propert yType ==
typeof(string);
xMember = Expression.Prop erty(xParam,
(PropertyInfo)m ember);
>
if (((PropertyInfo )member).GetVal ue(value, null) !=
null)
testMember = Expression.Prop erty(testValue,
(PropertyInfo)m ember);
// handle null values
else if (isString)
testMember = Expression.Cons tant("");
else if (((PropertyInfo )member).Proper tyType ==
typeof(DateTime ?))
testMember =
Expression.Cons tant(DateTime.M inValue);
else if (((PropertyInfo )member).Proper tyType ==
typeof(Decimal? ))
testMember =
Expression.Cons tant(Decimal.Mi nValue);
else if (((PropertyInfo )member).Proper tyType ==
typeof(int?))
testMember = Expression.Cons tant(int.MinVal ue);
else throw new ArgumentExcepti on("unknown null
property type");
break;
>
but got compile errors
>
Cannot implicitly convert type
'System.Linq.Ex pressions.Const antExpression' to
'System.Linq.Ex pressions.Membe rExpression'

This approach considers nulls and empty strings equal and seems to be wrong.

Only solution seems to be for comparison (c1,c2) < (v1,v2)

instead of

c1<v1 or ( c1=v1 and c2<v2)

generate expression with null checks

c1<v1 or (c1 is null and v1 is not null)
or
(
( c1=v1 or (c1=null and v1=null) )
and
(c2<v2 or (c2 is null and v2 is not null) )
)

Is this best solution ?
Can this expression simplified ?
Should I try to change LessThan() extension method to generate this
expression instead ?

Andrus.

[Marc Gravell]

Re: Creating dynamic DLinq tuple comparison query

generate expression with null checks

My biggest point here is that I'm not convinced that you should be comparing
to NULL in this way... now, that might be because I'm mainly an SQL-Server
person, so I'm not familiar with the vectorial compare, but to my
(unfamiliar) mind it seems crazy that (a,b) < (c,d) would include records
where any of a,b,c,d are NULL - 'cos that simply isn't how I think when
writing SQL.

If you want the expression to generate that, then go for it! Assuming that
the value is nullable in the source, I would have thought that the
approaches I mentioned previously would do this... I will investigate in a
simple example, but I haven't the time to change/debug the WhereLessThan() .

Marc

[Marc Gravell]

Re: Creating dynamic DLinq tuple comparison query

This should get you pointed in the right direction; with LINQ-to-SQL this
generates:

-- snip (example on NWind.Orders; RequiredDate)
FROM [dbo].[Orders] AS [t0]
WHERE ([t0].[RequiredDate] IS NULL) OR (([t0].[RequiredDate]) < @p0)

Marc

static IQueryable<TSou rceWhereLessTha nOrNull<TSource , TValue>(
this IQueryable<TSou rcesource, string propertyName, TValue value)
{
ParameterExpres sion x = Expression.Para meter(typeof(TS ource), "x");
MemberExpressio n member = Expression.Prop ertyOrField(x,
propertyName);
Expression<Func <TSource, bool>lambda =
Expression.Lamb da<Func<TSource , bool>>(
Expression.OrEl se(
Expression.Equa l(
member,
Expression.Cons tant(null, member.Type)),
Expression.Less Than(
Expression.Conv ert(member, typeof(TValue)) ,
Expression.Cons tant(value, typeof(TValue)) )),
x);
return source.Where(la mbda);
}

[Andrus]

Re: Creating dynamic DLinq tuple comparison query

Marc,

for boolean columns this extension method causes exception
The binary operator LessThan is not defined for the types 'System.Boolean '
and 'System.Boolean '

How to fix ?

Andrus.

System.InvalidO perationExcepti on was unhandled
Message="The binary operator LessThan is not defined for the types
'System.Boolean ' and 'System.Boolean '."
Source="System. Core"
StackTrace:
at
System.Linq.Exp ressions.Expres sion.GetUserDef inedBinaryOpera torOrThrow(Expr essionType
binaryType, String name, Expression left, Expression right, Boolean
liftToNull)
at
System.Linq.Exp ressions.Expres sion.GetCompari sonOperator(Exp ressionType
binaryType, String opName, Expression left, Expression right, Boolean
liftToNull)
at System.Linq.Exp ressions.Expres sion.LessThan(E xpression left,
Expression right, Boolean liftToNull, MethodInfo method)
at System.Linq.Exp ressions.Expres sion.LessThan(E xpression left,
Expression right)

"Marc Gravell" <marc.gravell@g mail.comwrote in message
news:6177b4fb-8118-4e80-9e80-f4a5ee903665@f6 3g2000hsf.googl egroups.com...

OK; this seems to work... tested with LINQ-to-SQL, but nothing very
complex here (from a LINQ-provider perspective), so it should
translate fairly well to other providers.
>
Marc
>
using System;
using System.Linq;
using System.Linq.Exp ressions;
using System.Reflecti on;
>
namespace ConsoleApplicat ion32
{
static class Program
{
public static IQueryable<TWhe reLessThan<T>(t his
IQueryable<Tsou rce, T value, params string[] properties)
{
return WhereInequality (source, value, true, properties);
}
public static IQueryable<TWhe reGreaterThan<T >(this
IQueryable<Tsou rce, T value, params string[] properties)
{
return WhereInequality (source, value, false, properties);
}
>
private static IQueryable<TWhe reInequality<T> (
IQueryable<Tsou rce, T value,
bool lessThan,
params string[] properties) {
>
// sense-check argments
if (source == null) throw new
ArgumentNullExc eption("source" );
if (value == null) throw new
ArgumentNullExc eption("value") ;
if (properties == null) throw new
ArgumentNullExc eption("propert ies");
if (properties.Len gth == 0) throw new
ArgumentExcepti on("No properties to compare", "properties ");
>
// xParam is the item being tested
// testValue is the single set of values to use for
comparison
ParameterExpres sion xParam =
Expression.Para meter(typeof(T) , "x");
ConstantExpress ion testValue = Expression.Cons tant(value,
typeof(T));
>
// need a repatable mechanism to construct the inequality,
noting that
// for strings we need to additionally Expression.Call
string.Compare,
// since inequality is not directly defined for strings;
we'll re-use
// the overall inequality to aid in building the string-
comparison
Func<Expression , Expression, BinaryExpressio n>
overallComparis on, overallStringCo mparison;
if (lessThan)
{ // doesn't like the ternary conditional operator here...
overallComparis on = Expression.Less Than;
}
else
{
overallComparis on = Expression.Grea terThan;
}
Type[] twoStrings = new[] {typeof(string) ,
typeof(string)} ;
overallStringCo mparison = (x,y) =overallCompari son(
Expression.Call (typeof(string) .GetMethod("Com pare",
twoStrings), x, y),
Expression.Cons tant(0, typeof(int))
);
>
// build the expression backwards to simplify construction
// note: already checked non-zero props, so expr *will*
get set
Expression expr = null;
for (int i = properties.Leng th - 1; i >= 0; i--)
{
// locate the member (prop/field), and obtain a
// MemberExpressio n for both the row being tested and
// the known test values
string propName = properties[i];
MemberInfo member = typeof(T).GetMe mber(propName,
MemberTypes.Pro perty | MemberTypes.Fie ld,
BindingFlags.Pu blic | BindingFlags.No nPublic |
BindingFlags.In stance).Single( );
MemberExpressio n xMember, testMember;
bool isString;
switch (member.MemberT ype)
{
case MemberTypes.Fie ld:
isString = ((FieldInfo)mem ber).FieldType ==
typeof(string);
xMember = Expression.Fiel d(xParam,
(FieldInfo)memb er);
testMember = Expression.Fiel d(testValue,
(FieldInfo)memb er);
break;
case MemberTypes.Pro perty:
isString = ((PropertyInfo) member).Propert yType
== typeof(string);
xMember = Expression.Prop erty(xParam,
(PropertyInfo)m ember);
testMember = Expression.Prop erty(testValue,
(PropertyInfo)m ember);
break;
default:
throw new
NotSupportedExc eption(string.F ormat("{0} ({1})", member.Name,
member.MemberTy pe));
}
>
// build the comparison for the member being
considered,
// composing the existing expression (if any)
Func<Expression , Expression, BinaryExpressio n>
comparison = isString
? overallStringCo mparison :
overallComparis on;
if (expr == null)
{
expr = comparison(xMem ber, testMember);
}
else
{
expr =
Expression.OrEl se(
comparison(xMem ber, testMember),
Expression.AndA lso(
Expression.Equa l(xMember, testMember),
expr
)
);
}
}
// build a lambda and use it to filter the data
return source.Where(Ex pression.Lambda <Func<T, bool>>(expr,
xParam));
}
static void Main(string[] args)
{
using (var ctx = new DataClasses1Dat aContext())
{
ctx.Log = Console.Out;
>
var qry = ctx.Products.Wh ereLessThan(
new Product { ProductName = "foo", UnitPrice =
34.3M, UnitsInStock = 3 },
"QuantityPerUni t", "UnitPrice" , "UnitsInStock") ;
>
foreach (var row in qry)
{
Console.WriteLi ne("{0}: {1}", row.ProductName ,
row.ProductID);
}
}
}
}
}

[Marc Gravell]

Re: Creating dynamic DLinq tuple comparison query

Well, it is correct ;-p

With LINQ-to-Objects you could use a custom comparer, but that won't
go to an "expression " at all.

Marc

[Andrus]

Re: Creating dynamic DLinq tuple comparison query

Marc,

With LINQ-to-Objects you could use a custom comparer, but that won't
go to an "expression " at all.

Should I try for bool comparison to convert expression c<v to

(NOT c AND v ) OR c IS NULL

?

Andrus.

[Marc Gravell]

Re: Creating dynamic DLinq tuple comparison query

I'm not sure that would help any - but feel free to give it a go.

Marc