By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,705 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,705 IT Pros & Developers. It's quick & easy.

Creating dynamic DLinq tuple comparison query

P: n/a
I created dynamic extension methods for <= and < SQL comparison operators:

public static IQueryable<TLessThanOrEqual<T>(this IQueryable<Tsource,
string property, object value);
public static IQueryable<TLessThan<T>(this IQueryable<Tsource, 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.

Jul 1 '08 #1
Share this Question
Share on Google+
11 Replies


P: n/a
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<TWhereLessThan<T>(this
IQueryable<Tsource, T value, params string[] properties)
{
return WhereInequality(source, value, true, properties);
}
public static IQueryable<TWhereGreaterThan<T>(this
IQueryable<Tsource, T value, params string[] properties)
{
return WhereInequality(source, value, false, properties);
}

private static IQueryable<TWhereInequality<T>(
IQueryable<Tsource, 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[i];
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);
}
}
}
}
}
Jul 7 '08 #2

P: n/a
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.

Jul 7 '08 #3

P: n/a
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
Jul 8 '08 #4

P: n/a
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.

Jul 8 '08 #5

P: n/a
>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.

Jul 8 '08 #6

P: n/a
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
Jul 9 '08 #7

P: n/a
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<TSourceWhereLessThanOrNull<TSource, TValue>(
this IQueryable<TSourcesource, 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);
}
Jul 9 '08 #8

P: n/a
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.GetUserDefinedB inaryOperatorOrThrow(ExpressionType
binaryType, String name, Expression left, Expression right, Boolean
liftToNull)
at
System.Linq.Expressions.Expression.GetComparisonOp erator(ExpressionType
binaryType, String opName, Expression left, Expression right, Boolean
liftToNull)
at System.Linq.Expressions.Expression.LessThan(Expres sion left,
Expression right, Boolean liftToNull, MethodInfo method)
at System.Linq.Expressions.Expression.LessThan(Expres sion left,
Expression right)

"Marc Gravell" <ma**********@gmail.comwrote in message
news:61**********************************@f63g2000 hsf.googlegroups.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.Expressions;
using System.Reflection;

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

private static IQueryable<TWhereInequality<T>(
IQueryable<Tsource, 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[i];
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);
}
}
}
}
}
Jul 13 '08 #9

P: n/a
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
Jul 13 '08 #10

P: n/a
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.
Jul 14 '08 #11

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

Marc
Jul 14 '08 #12

This discussion thread is closed

Replies have been disabled for this discussion.