DLinq dynamic order by child entity property

[Andrus]

I tried Marc dynamic query ordering method to retrieve customer name
property from order like

UtilityOrderBy<Order>( db.Orders, "Order.CustomerId.CustomerName",
doOrderBy );

but got error in line

MemberExpression member = Expression.Property(param, propertyName);

since Expression.Property does not allow to reference to Customer property.

How to change this method so that it can order by
Order.CustomerId.CustomerName property ?

Andrus.

static IOrderedQueryable<T> UtilityOrderBy<T>(
IQueryable<T> query, string propertyName, MethodInfo method)
{
// crete a property-getter expression
ParameterExpression param = Expression.Parameter(typeof(T),
"p");

// this line does not allow child properties:
MemberExpression member = Expression.Property(param,
propertyName);
object[] reflArgs = { query, member, param };
// invoke the specified method (for the appropriatePropertyType)
return (IOrderedQueryable<T>)method.MakeGenericMethod(
typeof(T), ((PropertyInfo)member.Member).PropertyType)
.Invoke(null, reflArgs);
}

 

[Marc Gravell]

Well, I'd start by reverse-engineering a regular (similar) LINQ query
via ildasm/reflector. I could perhaps take a look tomorrow, but that
is essentially it...

Marc

 

[Andrus]

Marc,

Well, I'd start by reverse-engineering a regular (similar) LINQ query
via ildasm/reflector. I could perhaps take a look tomorrow, but that
is essentially it...

I do'nt know IL. So for mey it may be best probably to use dynamic assembly
compiling to create OrderBy method calls which have hard-coded names like
..OrderBy( o=> o.Customers.CustomerName )

if creating .OrderBy( "Customers.CustomerName" ) fails.

I need probably generic IQueryable<T>. MS Dynamic Linq library has dynamic
OrderBy() but it returns non-generic IQueryable.

Some time ago you sent code which flattens properties for Select() column
list.
Can similar technique used in OrderBy() method ?

Andrus.

 

[Marc Gravell]

You don't want much ;-p

Try this; tested with LINQ-to-SQL

Marc

public static IOrderedQueryable<T> OrderBy<T>(this
IQueryable<T> source, string property)
{
return ApplyOrder<T>(source, property, "OrderBy");
}
public static IOrderedQueryable<T> OrderByDescending<T>(this
IQueryable<T> source, string property)
{
return ApplyOrder<T>(source, property,
"OrderByDescending");
}
public static IOrderedQueryable<T> ThenBy<T>(this
IOrderedQueryable<T> source, string property)
{
return ApplyOrder<T>(source, property, "ThenBy");
}
public static IOrderedQueryable<T> ThenByDescending<T>(this
IOrderedQueryable<T> source, string property)
{
return ApplyOrder<T>(source, property,
"ThenByDescending");
}
static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T>
source, string property, string methodName) {
string[] props = property.Split('.');
Type type = typeof(T);
ParameterExpression arg = Expression.Parameter(type, "x");
Expression expr = arg;
foreach(string prop in props) {
// use reflection (not ComponentModel) to mirror LINQ
PropertyInfo pi = type.GetProperty(prop);
expr = Expression.Property(expr, pi);
type = pi.PropertyType;
}
Type delegateType =
typeof(Func<,>).MakeGenericType(typeof(T), type);
LambdaExpression lambda = Expression.Lambda(delegateType,
expr, arg);

object result = typeof(Queryable).GetMethods().Single(
method => method.Name == methodName
&& method.IsGenericMethodDefinition
&& method.GetGenericArguments().Length ==
2
&& method.GetParameters().Length == 2)
.MakeGenericMethod(typeof(T), type)
.Invoke(null, new object[] {source, lambda});
return (IOrderedQueryable<T>)result;
}

 

[Marc Gravell]

I do'nt know IL

For the record - you don't need to; that is the beauty of Reflector -
most of what you need to know translates fine into C#. It is then just
a case of applying the same approach to your own scenario (in this
case, doing a Split('.') and loop), and a little bit of
MakeGenericMethod etc.

Marc

 

[Marc Gravell]

(patch to work with fields or properties, as LINQ does - note that
this is also simpler)

static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T>
source, string property, string methodName) {
ParameterExpression arg = Expression.Parameter(typeof(T),
"x");
Expression expr = arg;
foreach(string prop in property.Split('.')) {
// use reflection (not ComponentModel) to mirror LINQ
expr = Expression.PropertyOrField(expr, prop);
}
Type delegateType =
typeof(Func<,>).MakeGenericType(typeof(T), expr.Type);
LambdaExpression lambda = Expression.Lambda(delegateType,
expr, arg);

return (IOrderedQueryable<T>)
typeof(Queryable).GetMethods().Single(
method => method.Name == methodName
&& method.IsGenericMethodDefinition
&& method.GetGenericArguments().Length ==
2
&& method.GetParameters().Length == 2)
.MakeGenericMethod(typeof(T), expr.Type)
.Invoke(null, new object[] {source, lambda});
}

 

[Andrus]

Marc,

thank you. Line

expr = Expression.PropertyOrField(expr, prop);

in ApplyOrder causes AmbiguousMatchException

How to change this that it respects passed property case ?

Andrus.

System.Reflection.AmbiguousMatchException was unhandled
Message="Ambiguous match found."
Source="mscorlib"
StackTrace:
at System.RuntimeType.GetPropertyImpl(String name, BindingFlags
bindingAttr, Binder binder, Type returnType, Type[] types,
ParameterModifier[] modifiers)
at System.Type.GetProperty(String name, BindingFlags bindingAttr)
at System.Linq.Expressions.Expression.PropertyOrField(Expression
expression, String propertyOrFieldName)
....

 

[Andrus]

Marc,

For the record - you don't need to; that is the beauty of Reflector -
most of what you need to know translates fine into C#. It is then just
a case of applying the same approach to your own scenario

I need add <= comparison to IQueryable<T> to rows (c1,c2)<=(v1,v2)

where c1 and c2 are property names as strings and v1, v2 are corresponding
values as objects.
I know also property types t1,t2.

I think must create expression

c1<=v1 && ( c1<v1 || c2<=v2 )

I have created extension method for left side of &&
I need to create extension method to right side on && :

public static IQueryable<T> LessThanOrEqual<T>(this IQueryable<T> source,
string propertyName[2], object value[2], Type propertyType[2] );

or event better to support 1.. 6 properties with signature

public static IQueryable<T> LessThanOrEqual<T>(this IQueryable<T> source,
string propertyName[], object value[], Type propertyType[] );

For example

var q = db.Customers.LessThanOrEqual( new string[] {"City", "Id"} ,
new object[] {"London", "C12"},
new Type[] { typeof(string), typeof(string) } );
var res = q.Count();

should generate

SELECT COUNT(*)
FROM Customers
WHERE City <= 'London' AND ( City<'London' OR Id<='C12' );

Should I try to use reflector for this ?
Or is it possible to use the comparer which adding to miscutil you and Jon
are discussing ?

Andrus.

 

[Marc Gravell]

AmbiguousMatchException

Well, you could look at the original sample, which uses reflection;
you have a lot more control here, so could do this check case-
sensitive, then use Expression.Property or Expression.Field as
desired.

Marc

 

[Marc Gravell]

Or is it possible to use the comparer which adding to miscutil you and Jon

are discussing ?

That won't help you here; this is LINQ-to-Objects only.

Should I try to use reflector for this ?

I looked at this when you posted in last time; the problem is that it
is notoriously tricky to compose expressions after-the-fact in a way
that all LINQ implementations are happy with. For example, you can use
Expression.Invoke to apply an inner-expression, but LINQ-to-EF doesn't
(at last build) like this (although LINQ-to-SQL is quite happy). But
if you don't use .Invoke, you have real pain passing down the original
parameters.

Marc

 

[Andrus]

Marc,

I looked at this when you posted in last time; the problem is that it
is notoriously tricky to compose expressions after-the-fact in a way
that all LINQ implementations are happy with. For example, you can use
Expression.Invoke to apply an inner-expression, but LINQ-to-EF doesn't
(at last build) like this (although LINQ-to-SQL is quite happy). But
if you don't use .Invoke, you have real pain passing down the original
parameters.

I can implement only

c1<v1 or c2<=v2

This seems to be simple expression tree creation, without Invoke, isn't it ?
I can probably use Where to add c1<=v1 clause which which creation is even
simpler.

c1,c2 is string parameter name and v1,v2 type is string or decimal or some
other value type for which <= operator overload has implemented in
Expression class.
So it seems that there is no pain.

Andrus.

 

[Marc Gravell]

Tuple question answered on the original thread...

Please don't ask me to combine the two (tuples and nested properties);
in theory it is possible, but I don't imagine it would be pretty...

Marc

 

[Marc Gravell]

See my other post on the tuple chain.

Marc

 

[Andrus]

Marc,

thank you.
This solution works.
However PostgreSQL sorts null strings after all other.
My UI shows null strings as empty strings.
When user sorts string column, there are empty values in start and end of
table.
This confuses users.

I think best solution in to use COALESCE in order like

ORDER BY COALESCE( Order.Customer.Name, '' )

How to change this method so that

ORDER BY COALESCE( expr, '' )

is generated for string property and

ORDER BY COALESCE( expr, 0 ) for decimal? and int? properties ?

Andrus.

 

[Marc Gravell]

You would use Expression.Coalesce - but note that this may cripple
indexing. If you are going to display this in a UI, it might be more
efficient to get the data out "as is" (without changing anything) into
a list/array, and then apply this last sort in your C# code. You can
probably do it optimally by finding the index of the first non-null
item is, then doing a block remove/copy/whatever to move them to the
end instead (easier if creating a new list/array).

Marc

 

[Andrus]

You would use Expression.Coalesce - but note that this may cripple

indexing. If you are going to display this in a UI, it might be more
efficient to get the data out "as is" (without changing anything) into
a list/array, and then apply this last sort in your C# code. You can
probably do it optimally by finding the index of the first non-null
item is, then doing a block remove/copy/whatever to move them to the
end instead (easier if creating a new list/array).

I have fairly big tabes accesed over internet.
Same tables contain 200000 rows, it takes 1-2 minutes to load whole table.
Please re-confirm, should I read whole table to memory and perform in-memory
sorting in local workstations.

Andrus.

 

[Marc Gravell]

Please re-confirm, should I read whole table to memory and perform in-memory

sorting in local workstations.

It is your system; only you can make architectural decisions. Data
volume is always a major consideration, so if you have huge volumes
bringing it to the client isn't practical - but if the database can't
efficiently order them in the way you want you have problems. If this
was SQL Server ('cos that is what I know) I might put some thought
into using a calculated, persisted column that is the ordinal-sortable
version of an actual column (or set of columns) - i.e. if I want my
NULLs to specifically sort high/low (and ISNULL/COALESCE is killing
performance) it might have a high/low value. And for the most common
sort, cluster the data on that. But as always it depends on the entire
system. There isn't necessarily an easy answer...

Marc