473,324 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Creating dynamic DLinq tuple comparison query

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
11 3501
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
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
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
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
>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
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
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
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
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
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
I'm not sure that would help any - but feel free to give it a go.

Marc
Jul 14 '08 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Senna | last post by:
Hi Have a question about DLinq. The example code floating around looks like this: Northwind db = new Northwind(@"C:\...\northwnd.mdf"); var custs = from c in db.Customers where c.City ==...
3
by: Chiranjib | last post by:
I have the following queries/Observations about DLINQ. 1. I could not find any direct way of handling many to many relations. Suppose if User and Role are related by a join table UserRole then I...
0
by: Scott Nonnenberg [MSFT] | last post by:
This is our first official DLinq chat. We're still early in the planning and development stage for this very cool technology, so we can react to your feedback much more easily. Show up and tell us...
0
by: Scott Nonnenberg [MSFT] | last post by:
The DLinq team will be ready and waiting for your questions and comments at this date and time at this location: http://msdn.microsoft.com/chats/chatroom.aspx. This is otherwise known as a chat -...
0
by: Scott Nonnenberg [MSFT] | last post by:
Show up and talk to members of the DLinq team. What's DLinq, you ask? Well, to understand that you'll need to know what LINQ is - you can start with the blurb below, read more about it here:...
4
by: Brett Romero | last post by:
I've downloaded the DLINQ samples from Microsoft and have always been able to compile these in VS.NET 2005 Pro. I have a new project that I added DLINQ references to and put in a simlpe query. It...
1
by: Michel Walsh | last post by:
In the same spirit, but more LINQ related, you can also use ExecuteQuery: var query = dataContext.ExecuteQuery<className>( @"SELECT ... WHERE ... AND... OR... "); where className is...
2
by: Andrus | last post by:
I need to pass DLinq query to RDLDEsigner. RDLDesigner does not accept IQueryable<T>. It accepts SQL select statement in plain text format as data source. How to get SELECT statement which...
0
by: Andrus | last post by:
I implemented cached DLinq ExecuteQuery method. However some queries are run against database always because keys are not found in cache. Maybe equality comparer or other parts of implementation...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.