Creating dynamic DLinq tuple comparison query

[Andrus]

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

public static IQueryable<T> LessThanOrEqual<T>(this IQueryable<T> source,
string property, object value);
public static IQueryable<T> LessThan<T>(this IQueryable<T> source, string
property, object value);

For example

var q = db.Customers.LessThanOrEqual( "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.LessThanOrEqual( "City", "London" );
var q2 = db.Customers.LessThan( "City", "London" );
var q3 = db.Customers.LessThanOrEqual( "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]

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.Expressions;
using System.Reflection;

namespace ConsoleApplication32
{
static class Program
{
public static IQueryable<T> WhereLessThan<T>(this
IQueryable<T> source, T value, params string[] properties)
{
return WhereInequality(source, value, true, properties);
}
public static IQueryable<T> WhereGreaterThan<T>(this
IQueryable<T> source, T value, params string[] properties)
{
return WhereInequality(source, value, false, properties);
}

private static IQueryable<T> WhereInequality<T>(
IQueryable<T> source, T value,
bool lessThan,
params string[] properties) {

// sense-check argments
if (source == null) throw new
ArgumentNullException("source");
if (value == null) throw new
ArgumentNullException("value");
if (properties == null) throw new
ArgumentNullException("properties");
if (properties.Length == 0) throw new
ArgumentException("No properties to compare", "properties");

// xParam is the item being tested
// testValue is the single set of values to use for
comparison
ParameterExpression xParam =
Expression.Parameter(typeof(T), "x");
ConstantExpression testValue = Expression.Constant(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, BinaryExpression>
overallComparison, overallStringComparison;
if (lessThan)
{ // doesn't like the ternary conditional operator here...
overallComparison = Expression.LessThan;
}
else
{
overallComparison = Expression.GreaterThan;
}
Type[] twoStrings = new[] {typeof(string),
typeof(string)};
overallStringComparison = (x,y) => overallComparison(
Expression.Call(typeof(string).GetMethod("Compare",
twoStrings), x, y),
Expression.Constant(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.Length - 1; i >= 0; i--)
{
// locate the member (prop/field), and obtain a
// MemberExpression for both the row being tested and
// the known test values
string propName = properties;
MemberInfo member = typeof(T).GetMember(propName,
MemberTypes.Property | MemberTypes.Field,
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance).Single();
MemberExpression xMember, testMember;
bool isString;
switch (member.MemberType)
{
case MemberTypes.Field:
isString = ((FieldInfo)member).FieldType ==
typeof(string);
xMember = Expression.Field(xParam,
(FieldInfo)member);
testMember = Expression.Field(testValue,
(FieldInfo)member);
break;
case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType
== typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);
testMember = Expression.Property(testValue,
(PropertyInfo)member);
break;
default:
throw new
NotSupportedException(string.Format("{0} ({1})", member.Name,
member.MemberType));
}

// build the comparison for the member being
considered,
// composing the existing expression (if any)
Func<Expression, Expression, BinaryExpression>
comparison = isString
? overallStringComparison :
overallComparison;
if (expr == null)
{
expr = comparison(xMember, testMember);
}
else
{
expr =
Expression.OrElse(
comparison(xMember, testMember),
Expression.AndAlso(
Expression.Equal(xMember, testMember),
expr
)
);
}
}
// build a lambda and use it to filter the data
return source.Where(Expression.Lambda<Func<T, bool>>(expr,
xParam));
}
static void Main(string[] args)
{
using (var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out;

var qry = ctx.Products.WhereLessThan(
new Product { ProductName = "foo", UnitPrice =
34.3M, UnitsInStock = 3 },
"QuantityPerUnit", "UnitPrice", "UnitsInStock");

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

 

[Andrus]

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.MinDate 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]

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.MinDate 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.MinValue 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.Coalesce(member,
Expression.Constant(Activator.CreateInstance(type)), or
Expression.Condition(...), or Expression.Call(...) [on Nullable<T>'s
GetValueOrDefault()], but...

Marc

 

[Andrus]

Marc,

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

.... NULLs are to be considered equal in the ordering, and that NULLs should
sort either above or below all non-NULL values
[source: http://troels.arvin.dk/db/rdbms/#select-order_by]

Using IS NULL or null comparison violates this since it returns same order
in case of ascending and descending comparison
and thus produces result which does not depend on comparion type.
MSSQL considers nulls lower than any other value. So for expression

c1<v1 or c2<=v2

should be changed somehow to check for nulls and assume that null is lower
than any other value.
This makes expression complicated. So instead of this maybe I should to
change null values to

-infinity, "", MinDate, boolean constants.

This may be a daft question, but why not simply put zero/false/
DateTime.MinValue 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.

Is is not possible to use MinValue instead of null is DateRime is foreign
key reference.
null is used to represent unknown values it is not reasonable to change it
to MinDate.

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).

null values must sorted before any other values. I expect thatin descending
order they must be after other values then.
Comparing to null returns always null, so it produces same result for both
ascending and descending comparison. So it is probably incorrect.

Andrus.

 

[Marc Gravell]

We're not applying an ORDER BY; we're applying a WHERE. Under ANSI SQL,
*all* comparisons to null return false/unknown. But as far as I can tell,
the code (as previous) represents a correct implementation of a vectorial
comparison. Which was the original question... if that has changed, perhaps
you could re-state the question?

Re ISNULL - not sure how this is a problem; just set the second argument to
something very low...

Marc

 

[Andrus]

Marc,

Which was the original question... if that has changed, perhaps you could
re-state the question?

I have DataGridView in virtual mode which allows to edit tables in server
over internet based on MSDN datagridview virtual mode with caching sample
but uses DLinq Take()/Skip() for paged data access.
Those tables can contain up to 500000 rows.
User can sort this DataGridView and place conditions dynamically.
To make sort index unique, application adds primary key columns to order by
clause after user selected sort column(s).

I need to position grid to known entity for implementing those features:

1. If users sorts grid by arbitrary column, current entity in grid should
not changed.
2. It is required to postition grid to some known entity,e.q show list of
customers where current customer is as in current invoice.

Only way I know to position Virtual DataGridView is to set its current row
to some integer.
For this I need to calculate row number of query.

Only I know way to calculate row number is to use SELECT COUNT(*)
If current customer id is CUST23 and its city is null , when user sorts this
grid by City
discussed extension method generates query

SELECT COUNT(*)
FROM Customer
WHERE ... AND City<NULL OR City=NULL OR Id<'CUST23' ;

In this case SELECT COUNT(*) does not return row index to sorted grid since
expression containing nulls return null always and null is treated as false
in SQL WHERE. To fix this I can change NULL to empty string befor passing to
your extension method. In this case generated query is

SELECT COUNT(*)
FROM Customer
WHERE ... AND City<'' OR City='' OR Id<'CUST23' ;

If City column contains null values it still returns incorrect result. To
fix this I need to change this query to

SELECT COUNT(*)
FROM Customer
WHERE ... AND COALESCE(City,'')<'' OR COALESCE(City,'')='' OR Id<'CUST23'
;

but backend cannot use index on City in this case.

Andrus.

 

[Andrus]

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.Coalesce(member,
Expression.Constant(Activator.CreateInstance(type)), or
Expression.Condition(...), or Expression.Call(...) [on Nullable<T>'s
GetValueOrDefault()], but...

I changed your code

case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType ==
typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);
testMember = Expression.Property(testValue,
(PropertyInfo)member);
break;

to

case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType ==
typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);

if (((PropertyInfo)member).GetValue(value, null) !=
null)
testMember = Expression.Property(testValue,
(PropertyInfo)member);
// handle null values
else if (isString)
testMember = Expression.Constant("");
else if (((PropertyInfo)member).PropertyType ==
typeof(DateTime?))
testMember =
Expression.Constant(DateTime.MinValue);
else if (((PropertyInfo)member).PropertyType ==
typeof(Decimal?))
testMember =
Expression.Constant(Decimal.MinValue);
else if (((PropertyInfo)member).PropertyType ==
typeof(int?))
testMember = Expression.Constant(int.MinValue);
else throw new ArgumentException("unknown null
property type");
break;

but got compile errors

Cannot implicitly convert type 'System.Linq.Expressions.ConstantExpression'
to
'System.Linq.Expressions.MemberExpression'

Andrus.

 

[Andrus]

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.Coalesce(member,
Expression.Constant(Activator.CreateInstance(type)), or
Expression.Condition(...), or Expression.Call(...) [on Nullable<T>'s
GetValueOrDefault()], but...

I changed your code

case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType ==
typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);
testMember = Expression.Property(testValue,
(PropertyInfo)member);
break;

to

case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType ==
typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);

if (((PropertyInfo)member).GetValue(value, null) !=
null)
testMember = Expression.Property(testValue,
(PropertyInfo)member);
// handle null values
else if (isString)
testMember = Expression.Constant("");
else if (((PropertyInfo)member).PropertyType ==
typeof(DateTime?))
testMember =
Expression.Constant(DateTime.MinValue);
else if (((PropertyInfo)member).PropertyType ==
typeof(Decimal?))
testMember =
Expression.Constant(Decimal.MinValue);
else if (((PropertyInfo)member).PropertyType ==
typeof(int?))
testMember = Expression.Constant(int.MinValue);
else throw new ArgumentException("unknown null
property type");
break;

but got compile errors

Cannot implicitly convert type
'System.Linq.Expressions.ConstantExpression' to
'System.Linq.Expressions.MemberExpression'

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]

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]

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<TSource> WhereLessThanOrNull<TSource, TValue>(
this IQueryable<TSource> source, string propertyName, TValue value)
{
ParameterExpression x = Expression.Parameter(typeof(TSource), "x");
MemberExpression member = Expression.PropertyOrField(x,
propertyName);
Expression<Func<TSource, bool>> lambda =
Expression.Lambda<Func<TSource, bool>>(
Expression.OrElse(
Expression.Equal(
member,
Expression.Constant(null, member.Type)),
Expression.LessThan(
Expression.Convert(member, typeof(TValue)),
Expression.Constant(value, typeof(TValue)))),
x);
return source.Where(lambda);
}

 

[Andrus]

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.InvalidOperationException 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.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType
binaryType, String name, Expression left, Expression right, Boolean
liftToNull)
at
System.Linq.Expressions.Expression.GetComparisonOperator(ExpressionType
binaryType, String opName, Expression left, Expression right, Boolean
liftToNull)
at System.Linq.Expressions.Expression.LessThan(Expression left,
Expression right, Boolean liftToNull, MethodInfo method)
at System.Linq.Expressions.Expression.LessThan(Expression left,
Expression right)

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.Expressions;
using System.Reflection;

namespace ConsoleApplication32
{
static class Program
{
public static IQueryable<T> WhereLessThan<T>(this
IQueryable<T> source, T value, params string[] properties)
{
return WhereInequality(source, value, true, properties);
}
public static IQueryable<T> WhereGreaterThan<T>(this
IQueryable<T> source, T value, params string[] properties)
{
return WhereInequality(source, value, false, properties);
}

private static IQueryable<T> WhereInequality<T>(
IQueryable<T> source, T value,
bool lessThan,
params string[] properties) {

// sense-check argments
if (source == null) throw new
ArgumentNullException("source");
if (value == null) throw new
ArgumentNullException("value");
if (properties == null) throw new
ArgumentNullException("properties");
if (properties.Length == 0) throw new
ArgumentException("No properties to compare", "properties");

// xParam is the item being tested
// testValue is the single set of values to use for
comparison
ParameterExpression xParam =
Expression.Parameter(typeof(T), "x");
ConstantExpression testValue = Expression.Constant(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, BinaryExpression>
overallComparison, overallStringComparison;
if (lessThan)
{ // doesn't like the ternary conditional operator here...
overallComparison = Expression.LessThan;
}
else
{
overallComparison = Expression.GreaterThan;
}
Type[] twoStrings = new[] {typeof(string),
typeof(string)};
overallStringComparison = (x,y) => overallComparison(
Expression.Call(typeof(string).GetMethod("Compare",
twoStrings), x, y),
Expression.Constant(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.Length - 1; i >= 0; i--)
{
// locate the member (prop/field), and obtain a
// MemberExpression for both the row being tested and
// the known test values
string propName = properties;
MemberInfo member = typeof(T).GetMember(propName,
MemberTypes.Property | MemberTypes.Field,
BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance).Single();
MemberExpression xMember, testMember;
bool isString;
switch (member.MemberType)
{
case MemberTypes.Field:
isString = ((FieldInfo)member).FieldType ==
typeof(string);
xMember = Expression.Field(xParam,
(FieldInfo)member);
testMember = Expression.Field(testValue,
(FieldInfo)member);
break;
case MemberTypes.Property:
isString = ((PropertyInfo)member).PropertyType
== typeof(string);
xMember = Expression.Property(xParam,
(PropertyInfo)member);
testMember = Expression.Property(testValue,
(PropertyInfo)member);
break;
default:
throw new
NotSupportedException(string.Format("{0} ({1})", member.Name,
member.MemberType));
}

// build the comparison for the member being
considered,
// composing the existing expression (if any)
Func<Expression, Expression, BinaryExpression>
comparison = isString
? overallStringComparison :
overallComparison;
if (expr == null)
{
expr = comparison(xMember, testMember);
}
else
{
expr =
Expression.OrElse(
comparison(xMember, testMember),
Expression.AndAlso(
Expression.Equal(xMember, testMember),
expr
)
);
}
}
// build a lambda and use it to filter the data
return source.Where(Expression.Lambda<Func<T, bool>>(expr,
xParam));
}
static void Main(string[] args)
{
using (var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out;

var qry = ctx.Products.WhereLessThan(
new Product { ProductName = "foo", UnitPrice =
34.3M, UnitsInStock = 3 },
"QuantityPerUnit", "UnitPrice", "UnitsInStock");

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

 

[Marc Gravell]

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]

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]

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

Marc