DLinq dynamic order by child entity property
Andrus
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
via ildasm/reflector. I could perhaps take a look tomorrow, but that
is essentially it...
Marc
Andrus
> 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
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
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
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)
Andrus
> 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.
Andrus
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)
...
Marc Gravell
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
> are discussing ?
> 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
Marc Gravell
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
Andrus
> 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
Marc
Andrus
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" <marc.g...@gmail.com> wrote in message
news:ef27b3c6-7c4d-4814...@b1g2000hsg.googlegroups.com...
Marc Gravell
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
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
> 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