LINQ: How to build dynamic query?

[Stefan Wilhelm]

Hello,
played a little with orcas and went into a problem by building dynamic
queries. I want to build a dynamic where clause but only with parameters that
are not empty => (TextBox.Text != "").
Doing this by building a string is very simple, but how to do it with LINQ?

string sql = "";
if (TextBox1.Text != "")
sql += "Columns_1='"+TextBox1.Text+"'";
if (TextBox2.Text != "")
sql += "Columns_2='"+TextBox2.Text+"'";

How to include the if query to linq???

var query = from c in db.MyTable
where c.Column_1 == TextBox1.Text &&
c.Column_2 == TextBox2.Text
select c;

Thank you!

-Stefan

[Marc Gravell]

Queries are "composable" - hence you can do things like the following:

var query = from s in dataContext.SomeEntities
select s;

if (!string.IsNullOrEmpty(inputA)) {
query = from x in query
where x.PropertyA == inputA
select x;
}
if (!string.IsNullOrEmpty(inputB)) {
query = from x in query
where x.PropertyB == inputB
select x;
}

Then enumerate query.

Marc

[Marc Gravell]

(slightly simpler):

if (!string.IsNullOrEmpty(inputA)) {
query = query.Where(x => x.PropertyB == inputA);
}
if (!string.IsNullOrEmpty(inputB)) {
query = query.Where(x => x.PropertyB == inputB);
}

Marc

[Marc Gravell]

[watch for typo - should have been PropertyA]

[Jon Skeet [C# MVP]]

It's actually slightly easier than that if you ignore the query
expression syntax:

var query = dataContext.SomeEntities.AsQueryable();
if (!string.IsNullOrEmpty(inputA))
{
query = query.Where(x => x.PropertyA==inputA);
}
if(!string.IsNullOrEmpty(inputB))
{
query = query.Where(x => x.PropertyB==inputB);
}

You could even write a method to abstract it out if necessary.

Query expression syntax is nice in many cases, but just to add a
"where" clause I prefer to use the direct method call.

Jon

[Chris Dunaway]

Hi Jon.

I've pre-ordered your book from Amazon but was wondering if it will
cover LINQ in much detail?

Thanks,

Chris

[Jon Skeet [C# MVP]]

On Nov 14, 4:43 pm, Chris Dunaway <dunaw...@gmail.com> wrote:
> I've pre-ordered your book from Amazon

Yay - thanks very much :)

> but was wondering if it will cover LINQ in much detail?

It covers the translation of query expressions into "normal" method
calls, and it covers the standard query operators, but it only skims
over the different LINQ providers. Going into any level of detail
about LINQ basically requires a book in its own right - and that's
where "LINQ in Action" (also from Manning) comes into play. I confess
I haven't looked at any *other* LINQ books, but LINQ in Action is
good :)

Jon

[Marc Gravell]

> It's actually slightly easier than that if you ignore the query
> expression syntax:

> ...

> Query expression syntax is nice in many cases, but just to add a
> "where" clause I prefer to use the direct method call.

Yes - I posted that right after my initial post (albeit with a
typo)... I agree.

> You could even write a method to abstract it out if necessary.

Care to expand on this? I can think of a few ways of interpreting
this, but I'm just interested in how you mean it...?

Marc

[Stefan Wilhelm]

"Jon Skeet [C# MVP]" wrote:
> Query expression syntax is nice in many cases, but just to add a
> "where" clause I prefer to use the direct method call.
>
> Jon

Hi Jon and Marc,
thank you for this quick post!

@Jon: What do you mean with "the direct method call"?

@all: Now I can filter with linq dynamically, but what is with ORDER BY?
string s = "select * from mytable order by " + DropDownList1.SelectedValue;

-Stefan

[Jon Skeet [C# MVP]]

On Nov 14, 10:19 pm, Marc Gravell <marc.grav...@gmail.com> wrote:
> > You could even write a method to abstract it out if necessary.
>
> Care to expand on this? I can think of a few ways of interpreting
> this, but I'm just interested in how you mean it...?

Well, you could have (off the top of my head - apologies if
Predicate<T> should be Func<T,bool>)
(Further apologies for the formatting - Google groups uses a
proportional font)

IQueryable<T> ConditionalWhere<T>(static this IQueryable<T> query,
string value,
Expression<Predicate<T>> predicate)
{
if (!string.IsNullOrEmpty(value))
{
return query.Where(predicate);
}
}

then call it with:

var query = query.ConditionalWhere(valueA, x => x.PropertyA==valueA)
.ConditionalWhere(valueB, x => x.PropertyB==valueB);

Jon

[Jon Skeet [C# MVP]]

On Nov 15, 10:01 am, Stefan Wilhelm

<StefanWilh...@discussions.microsoft.com> wrote:
> thank you for this quick post!
>
> @Jon: What do you mean with "the direct method call"?

I mean explicitly calling Where instead of letting the query
expression translation do it for you.

> @all: Now I can filter with linq dynamically, but what is with ORDER BY?
> string s = "select * from mytable order by " + DropDownList1.SelectedValue;

What do you mean by "what is with ORDER BY"? Do you mean you want to
express the ordering in LINQ? If so, you'll need to use the OrderBy
method or "order by" part of the query expression syntax - but that
will be more strongly typed than your SQL example.

Jon

[Stefan Wilhelm]

"Jon Skeet [C# MVP]" wrote:
>

> What do you mean by "what is with ORDER BY"? Do you mean you want to
> express the ordering in LINQ? If so, you'll need to use the OrderBy
> method or "order by" part of the query expression syntax - but that
> will be more strongly typed than your SQL example.
>
> Jon

I want to change the orderby dynamic:

var q = from f in DB.Firmas
where f.PLZ == 89077
orderby DropDownList1.SelectedValue
select f;

E.g. I have a filtermask for a grid, the user should be able to enter
several optional filter parameter. This can be done with you first example.
But maybe I want to set a dropdownlist to allow the user to sort the query
result (e.g. sortby column_1, or sortby column_4 or sortby column_5).

The only (complicated) way to do this is:

if (DropDownList1.SelectedValue=="column_1")
{
var q = from f in DB.Firmas
where f.PLZ == 89077
orderby f.column_1
select f;
}
elseif (DropDownList1.SelectedValue=="column_4")
{
var q = from f in DB.Firmas
where f.PLZ == 89077
orderby f.column_4
select f;
}

There must be a more simple way.

-Stefan

[Jon Skeet [C# MVP]]

On Nov 15, 10:48 am, Stefan Wilhelm
<StefanWilh...@discussions.microsoft.com> wrote:

<snip>

> I want to change the orderby dynamic:
>
> var q = from f in DB.Firmas
> where f.PLZ == 89077
> orderby DropDownList1.SelectedValue
> select f;

It can be dynamic, but it has to be verifiable at compile time. In
other words, you need to do things based on the drop down list, not
just use the text of it directly.

> E.g. I have a filtermask for a grid, the user should be able to enter
> several optional filter parameter. This can be done with you first example.
> But maybe I want to set a dropdownlist to allow the user to sort the query
> result (e.g. sortby column_1, or sortby column_4 or sortby column_5).
>
> The only (complicated) way to do this is:
>
> if (DropDownList1.SelectedValue=="column_1")
> {
> var q = from f in DB.Firmas
> where f.PLZ == 89077
> orderby f.column_1
> select f;}
>
> elseif (DropDownList1.SelectedValue=="column_4")
> {
> var q = from f in DB.Firmas
> where f.PLZ == 89077
> orderby f.column_4
> select f;
>
> }
>
> There must be a more simple way.

Yes - do something like:

var query = ... // unordered

switch (DropDownList1.SelectedValue)
{
case "column_1":
query = query.OrderBy (firm => firm.SomeProperty);
break;
...
}

Again, this uses the composability of queries.

Jon

[Stefan Wilhelm]

"Jon Skeet [C# MVP]" wrote:

> Yes - do something like:
>
> var query = ... // unordered
>
> switch (DropDownList1.SelectedValue)
> {
> case "column_1":
> query = query.OrderBy (firm => firm.SomeProperty);
> break;
> ...
> }
>
> Again, this uses the composability of queries.
>
> Jon

Is there no other way to sort? This is too complex, I think. When I will
have 20 columns to sort, I have to build 20 switch/case.
In pure sql, I can do this by "select * from myTable order by " +
DropDownList1.SelectedValue - no switsch/case - this is what I'm missing.
No way to get it work?

[Jon Skeet [C# MVP]]

On Nov 15, 12:46 pm, Stefan Wilhelm
<StefanWilh...@discussions.microsoft.com> wrote:

<snip>

> Is there no other way to sort? This is too complex, I think. When I will

> have 20 columns to sort, I have to build 20 switch/case.
> In pure sql, I can do this by "select * from myTable order by " +
> DropDownList1.SelectedValue - no switsch/case - this is what I'm missing.
> No way to get it work?

You *can* do that in SQL, but that doesn't mean it's a good idea.
Hopefully ASP.NET will do some validation of the value that the client
claims is selected, but I'm not sure I'd rely on it. What happens when
the client comes back with a selected value of "foo; drop table
myTable"? Basically you're using user input in a raw SQL form, which
is just asking for an injection attack.

To avoid putting it into a switch/case block, you could have a
dictionary - but the problem is that unless *all* your ordering
columns use the same type, you won't know what the signature of the
delegate involved is :( I suppose you could have a few well known
types with a set of dictionaries, but that's not terribly pleasant...

Jon

[Stefan Wilhelm]

"Jon Skeet [C# MVP]" wrote:

> You *can* do that in SQL, but that doesn't mean it's a good idea.
> Hopefully ASP.NET will do some validation of the value that the client
> claims is selected, but I'm not sure I'd rely on it. What happens when
> the client comes back with a selected value of "foo; drop table
> myTable"? Basically you're using user input in a raw SQL form, which
> is just asking for an injection attack.

OK, in ASP.Net this will not a good idea, but in Windows.Forms this should
be not a problem.

"Jon Skeet [C# MVP]" wrote:

> To avoid putting it into a switch/case block, you could have a
> dictionary - but the problem is that unless *all* your ordering
> columns use the same type, you won't know what the signature of the
> delegate involved is :( I suppose you could have a few well known
> types with a set of dictionaries, but that's not terribly pleasant...

Maybe it will work over reflection?
It is a little bit pity that there is no
query.OrderBy<Firma>("columnAsString");

Thank you for your answers!!!

[Jon Skeet [C# MVP]]

On Nov 15, 1:16 pm, Stefan Wilhelm

<StefanWilh...@discussions.microsoft.com> wrote:
> > You *can* do that in SQL, but that doesn't mean it's a good idea.
> > Hopefully ASP.NET will do some validation of the value that the client
> > claims is selected, but I'm not sure I'd rely on it. What happens when
> > the client comes back with a selected value of "foo; drop table
> > myTable"? Basically you're using user input in a raw SQL form, which
> > is just asking for an injection attack.
>
> OK, in ASP.Net this will not a good idea, but in Windows.Forms this should
> be not a problem.

I still don't think it's a good idea, whatever platform you're on, to
be honest.

> > To avoid putting it into a switch/case block, you could have a
> > dictionary - but the problem is that unless *all* your ordering
> > columns use the same type, you won't know what the signature of the
> > delegate involved is :( I suppose you could have a few well known
> > types with a set of dictionaries, but that's not terribly pleasant...
>
> Maybe it will work over reflection?

You probably *could* do it with reflection, although it wouldn't be
particularly simple. You'd have to look up the property, find out the
type, create the appropriate (typed) expression tree, look up OrderBy
and create the appropriate generic version, then call it. Not nice.
I'll look into it at home if you're particularly interested in going
this way, but I think the way using "proper" lambda expressions is a
better idea.

> It is a little bit pity that there is no
> query.OrderBy<Firma>("columnAsString");

Except that at that point you no longer have any compile-time safety,
which is half the point of LINQ.

Jon

[Marc Gravell]

You could presumably put an expression tree and lambda together
through code (using Expression.Property(...) at some point), but it
wouldn't be pretty. The switch would be easier to maintain.

Marc

[Marc Gravell]

> Maybe it will work over reflection?

See my other reply

> but in Windows.Forms this should
> be not a problem.

winform is easy to abuse too... at least with ASP.NET the user doesn't
tend to have a direct connection to the database...

Marc

[Andrus]

> You could presumably put an expression tree and lambda together through
> code (using Expression.Property(...) at some point), but it wouldn't be
> pretty. The switch would be easier to maintain.

Some columns in my winform datagridview are defined by user in runtime.
So I cannot use hard-coded swith with all column names.

My grid columns can be string, bool and decimal types only.
Maybe it is possible to use switch for types only like

swith (SortExpression.GetTypeCode) {
case TypeCode.String:
....

case TypeCode.Bool:
...

case TypeCode.Decimal:
....

default:
MessageBox.Show("Cannot sort by this column");
}

Andrus.

[Marc Gravell]

For LINQ-to-object (since expressions aren't going to work with
runtime properties), how about below (note that the "let" ensures that
only one "get" is called per object)

First time I've used a "let" in anger... quite handy actually ;-p

static class PropertyExtensions {
public static T GetPropertyValue<T>(this object component, string
propertyName) {
return (T)
TypeDescriptor.GetProperties(component)[propertyName].GetValue(component);
}
}
var data = new[] {
new {Name = "Fred", Key = 1},
new {Name = "Barney", Key = 4},
new {Name = "Wilma", Key = 3},
new {Name = "Betty", Key = 7}
};
var dynamicOrder =
from x in data
let val = x.GetPropertyValue<IComparable>("Key")
orderby val
select x;
foreach(var who in dynamicOrder) {
Console.WriteLine("{0}: {1}", who.Key, who.Name);
}

Marc

[Andrus]

Marc,

I have table containing 500000 rows.
I use WinForms DataGrdiView in virtual mode and Linq-SQL Skip(n).Take(m) to
get only the rows which are visible in screen.
Some DataGrdiView columns are defined dynamically by user.

How to run dynamic ordered linq sliced query against database if user
clicks in some column of my virtual DataGridView ?

Andrus.

[Marc Gravell]

Either the switch (as already shown), or (assuming you are still using
the "compile some c# with extra properties" approach) you could
*technically* build an Expression of the same with a string argument.
The problem is, I don't know how to build the Expression, and haven't
got time to start R&D on it.

If these are runtime properties, then forget it; LINQ-to-SQL (in
common with ActiveRecord) uses reflection (MemberInfo, via an
Expression), so you need compile-time properties.

Anybody else feel confident in putting an Expression together to
"orderby"?

Marc

[Jon Skeet [C# MVP]]

It wouldn't be particularly hard if it's just a property - the easiest
way to work out what to do would be to create a sample, then look at
the generated code. The main problem is that the compiler knows the
type involved, and also gets to use methodof rather than using real
reflection to get a MethodInfo.

--
Jon Skeet - <sk...@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Marc Gravell]

There is an Expression.Property method (IIRC) that pairs to memberof;
I'll see what I can find...

[Marc Gravell]

Et voila; this should be compatible with LINQ-to-SQL (although I
haven't tested it). The important bit is marked with comments. The
things we do...

using System;
using System.Linq;
using System.Linq.Expressions;

class Person {
public string Name { get; set; }
public int Age { get; set; }

static void Main() {
var query = new[] {
new Person {Name="Barney", Age=3},
new Person {Name="Betty", Age=5},
new Person {Name="Fred", Age=2}
}.AsQueryable();

// fun bit...
string propertyName = "Name";
ParameterExpression param =
Expression.Parameter(typeof(Person), "p");
Expression<Func<Person, IComparable>> comparer =
Expression.Lambda<Func<Person, IComparable>>(
Expression.Property(param, propertyName), param);
var ordered = query.OrderBy(comparer);
// end fun...

foreach (var p in ordered) {
Console.WriteLine("{0}: {1}", p.Age, p.Name);
}
}
}

[Marc Gravell]

It gnawed at me, so I checked:

var query = db.Customers.AsQueryable();
string propertyName = "CompanyName";
ParameterExpression param =
Expression.Parameter(typeof(Customer), "p");
Expression<Func<Customer, IComparable>> comparer =
Expression.Lambda<Func<Customer, IComparable>>(

Expression.Property(param, propertyName),
param);
var ordered = query.OrderBy(comparer);

var paged = ordered.Skip(20).Take(10);
string sql = db.GetCommand(paged).CommandText;

uses:

SELECT TOP 10 [t1].[CustomerID], [t1].[CompanyName], [t1].
[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].
[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CompanyName]) AS
[ROW_NUMBER], [t0].[CustomerID], [t0].[CompanyName], [t0].
[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].
[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
ORDER BY [t1].[CompanyName]

So it seems to work ;-p

Obviously it could be refactored to suit, perhaps making an
OrderBy<T>(this IEnumerable<T>, params string[]) extension method so
you can call someSource.OrderBy("Name", "Number", "DateOfBirth") etc
(using ThenBy appropriately obviously).

Marc

[Andrus]

> So it seems to work ;-p

Thank you. Excellent.

I tied it in DBLinq using the code below.
I found that simply presence of the statement
Expression<Func<Klient, IComparable>> comparer
=Expression.Lambda<Func<Klient, IComparable>>( Expression.Property(param,
propertyName), param);

in code causes exception (shown below) in line

return query.ToList();

This exception means probably that parameters are passed to Klient
constructor in wrong order.

If I simply comment comparer declaration line out, code runs OK.

Any idea how to fix ?

Andrus.

My code:
static public object GetData() {

db = new myDb(connStr);

var query = db.Klients; //.AsQueryable();

string propertyName = "Nimi";

ParameterExpression param = Expression.Parameter(typeof(Klient), "p");

// if this line is not commented, parameters are passed in wrong order to
constructor

Expression<Func<Klient, IComparable>> comparer
=Expression.Lambda<Func<Klient, IComparable>>(

Expression.Property(param, propertyName), param);

return query.ToList();

}

Exception in query.ToList() line:

System.ArgumentException was unhandled
Message="Expression of type 'System.String' cannot be used for constructor
parameter of type 'System.Decimal'"
Source="System.Core"
StackTrace:
at System.Linq.Expressions.Expression.ValidateNewArgs(Type type,
ConstructorInfo constructor, ReadOnlyCollection`1& arguments)
at System.Linq.Expressions.Expression.New(ConstructorInfo
constructor, IEnumerable`1 arguments)
at
DBLinq.util.RowEnumeratorCompiler`1.CompileColumnRowDelegate(ProjectionData
projData, Int32& fieldID) in
C:\dblinqrev78\DbLinq\util\RowEnumeratorCompiler.cs:line 147
at DBLinq.util.RowEnumeratorCompiler`1.CompileRowDelegate(SessionVars
vars, Int32& fieldID) in
C:\dblinqrev78\DbLinq\util\RowEnumeratorCompiler.cs:line 69
at DBLinq.util.RowEnumerator`1.CompileReaderFct() in
C:\dblinqrev78\DbLinq\util\RowEnumerator.cs:line 100
at DBLinq.util.RowEnumerator`1..ctor(SessionVars vars, Dictionary`2
rowCache) in C:\dblinqrev78\DbLinq\util\RowEnumerator.cs:line 92
at DBLinq.Linq.MTable`1.GetEnumerator() in
C:\dblinqrev78\DbLinq\Linq\MTable.cs:line 149
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Business.Business.GetData()
at Program.Main(String[] args) in C:\Documents and
Settings\Administrator\My Documents\Visual Studio
2008\Projects\WindowsFormsApplication1\WindowsFormsApplication1\Program.cs:line
20
..

[Marc Gravell]

Well, I don't know anything about the Klient ctor (and I can't see a
custom projection).
To be honest, you might be better off using a default ctor? This might
remove a range of possible issues? I'm very surprised that the
"orderby" makes a difference here... it might be useful to compare/
contrast the SQL with/without the order by? (my previous code shows
how). Perhaps post this?

For reference, I did some refactoring, which now
a: allows simple use from IQueryable<T> (extension method)
b: uses the correct type internally (rather than IComparable), just in
case there are some optimisations done internally

To simplify your code, you might choose to swap for this. At some
point I must find some space in the blogosphere/extraweb to dump this
stuff...

This allows for usage as below:

static void Main() {
using (NWind.Northwind db = new NWind.Northwind()) {
var query = db.Customers.AsQueryable();
// filter
query = query.Where("Region", "SP");
// sort (note uses IOrdered... between hops)
query =
query.OrderByDescending("City").ThenBy("CompanyName");
// page and project
var custs = query.Skip(2).Take(99).ToList();
}
}

The backing (extension) code has methods:

static class DynamicQueryExtensions {
// cached MethodInfo instances used for reflection invoke
private readonly static MethodInfo
doOrderBy, doOrderByDescending,
doThenBy, doThenByDescending;
static DynamicQueryExtensions() {
// pre-cache MethodInfo instances
const BindingFlags STATIC_NONPUBLIC = BindingFlags.Static |
BindingFlags.NonPublic;
doOrderBy =
typeof(DynamicQueryExtensions).GetMethod("DoOrderBy",
STATIC_NONPUBLIC);
doOrderByDescending =
typeof(DynamicQueryExtensions).GetMethod("DoOrderByDescending",
STATIC_NONPUBLIC);
doThenBy =
typeof(DynamicQueryExtensions).GetMethod("DoThenBy",
STATIC_NONPUBLIC);
doThenByDescending =
typeof(DynamicQueryExtensions).GetMethod("DoThenByDescending",
STATIC_NONPUBLIC);
}

public static IQueryable<TEntity> Where<TEntity, TValue>(this
IQueryable<TEntity> query, string propertyName, TValue value) {
ParameterExpression param =
Expression.Parameter(typeof(TEntity), "p");
BinaryExpression testExp = LambdaExpression.Equal(
Expression.Property(param, propertyName),
Expression.Constant(value, typeof(TValue))
);
return query.Where(Expression.Lambda<Func<TEntity,
bool>>(testExp, param));
}

// visible order methods
public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T>
query, string propertyName) {
return UtilityOrderBy(query, propertyName, doOrderBy);
}
public static IOrderedQueryable<T> OrderByDescending<T>(this
IQueryable<T> query, string propertyName) {
return UtilityOrderBy(query, propertyName,
doOrderByDescending);
}
public static IOrderedQueryable<T> ThenBy<T>(this
IOrderedQueryable<T> query, string propertyName) {
return UtilityOrderBy(query, propertyName, doThenBy);
}
public static IOrderedQueryable<T> ThenByDescending<T>(this
IOrderedQueryable<T> query, string propertyName) {
return UtilityOrderBy(query, propertyName,
doThenByDescending);
}
private static IOrderedQueryable<T>
UtilityOrderBy<T>(IQueryable<T> query, string propertyName, MethodInfo
method) {
// crete a property-getter expression
ParameterExpression param = Expression.Parameter(typeof(T),
"p");
MemberExpression member = Expression.Property(param,
propertyName);
object[] reflArgs = { query, member, param };
// invoke the specified method (for the appropriate
PropertyType)
return (IOrderedQueryable<T>)method.MakeGenericMethod(
typeof(T), ((PropertyInfo)member.Member).PropertyType)
.Invoke(null, reflArgs);
}
private static IOrderedQueryable<TEntity> DoOrderBy<TEntity,
TProperty>(IQueryable<TEntity> source, Expression body,
ParameterExpression param) {
return source.OrderBy(
Expression.Lambda<Func<TEntity, TProperty>>(body, param));
}
private static IOrderedQueryable<TEntity>
DoOrderByDescending<TEntity, TProperty>(IQueryable<TEntity> source,
Expression body, ParameterExpression param) {
return source.OrderByDescending(
Expression.Lambda<Func<TEntity, TProperty>>(body, param));
}
private static IOrderedQueryable<TEntity> DoThenBy<TEntity,
TProperty>(IOrderedQueryable<TEntity> source, Expression body,
ParameterExpression param) {
return source.ThenBy(
Expression.Lambda<Func<TEntity, TProperty>>(body, param));
}
private static IOrderedQueryable<TEntity>
DoThenByDescending<TEntity, TProperty>(IOrderedQueryable<TEntity>
source, Expression body, ParameterExpression param) {
return source.ThenByDescending(
Expression.Lambda<Func<TEntity, TProperty>>(body, param));
}
}

[Marc Gravell]

Another question; the bit that you need to comment isn't clear, since
you don't actually change "query" with the comparer - query is still
"db.Klients"; is there a bit missing that might contain the glitch? It
may be unrelated, but it will be easier to investigate if you post the
verbatim code that isn't working...

Marc

[Andrus]

> Well, I don't know anything about the Klient ctor (and I can't see a
> custom projection).

Full code of Klient class. Constructor has 52 parameters.

[Table(Name = "Klient")]

public class Klient : Entity.Klient {

public Klient() : base() { }

public Klient( System.String p_kood, System.String regnr,
System.String vatpayno, System.String piirkond,
System.String postiindek, System.String tanav,
System.String kontaktisi, System.String telefon,
System.String faks, System.String email, System.String infomail,
System.String wwwpage, System.String liik, System.String viitenr,
System.String riik, System.String riik2, System.String riigikood,
System.String hinnak, System.String erihinnak,
System.String myygikood, System.String objekt2,
System.String objekt5, System.String objekt7,
System.String maksetin, System.String omakseti,
System.Decimal krediit, System.Decimal ostukredii,
System.String masin, System.String info, System.String maksja,
System.Decimal elanikud, System.Decimal pindala,
System.String grmaja, System.Decimal apindala,
System.Decimal kpindala, System.String idmakett,
System.String tulemus, System.String omandisuhe,
System.String username, System.String changedby,
System.String parool, System.String hinnaale,
System.Boolean mitteakt, System.DateTime? kontakteer,
System.String klikaart, System.String kaibegrupp,
System.Decimal mhprotsent, System.String aadress,
System.String timestamp, System.String atimestamp,
System.String kood, System.String nimi
) :

base(p_kood, regnr, vatpayno, piirkond, postiindek, tanav, kontaktisi,
telefon, faks, email, infomail, wwwpage, liik, viitenr, riik, riik2,
riigikood, hinnak, erihinnak, myygikood, objekt2, objekt5, objekt7,
maksetin, omakseti, krediit, ostukredii, masin, info, maksja, elanikud,
pindala, grmaja, apindala, kpindala, idmakett, tulemus, omandisuhe,
username, changedby, parool, hinnaale, mitteakt, kontakteer, klikaart,
kaibegrupp, mhprotsent, aadress, timestamp, atimestamp, kood, nimi) {

}}

> I'm very surprised that the
> "orderby" makes a difference here... it might be useful to compare/
> contrast the SQL with/without the order by? (my previous code shows
> how). Perhaps post this?

sql statement generated in same in both cases:

SELECT x$.p_kood, x$.regnr, x$.vatpayno, x$.piirkond, x$.postiindek,
x$.tanav, x$.kontaktisi, x$.telefon, x$.faks, x$.email, x$.infomail,
x$.wwwpage, x$.liik, x$.viitenr, x$.riik, x$.riik2, x$.riigikood, x$.hinnak,
x$.erihinnak, x$.myygikood, x$.objekt2, x$.objekt5, x$.objekt7, x$.maksetin,
x$.omakseti, x$.krediit, x$.ostukredii, x$.masin, x$.info, x$.maksja,
x$.elanikud, x$.pindala, x$.grmaja, x$.apindala, x$.kpindala, x$.idmakett,
x$.tulemus, x$.omandisuhe, x$.username, x$.changedby, x$.parool,
x$.hinnaale, x$.mitteakt, x$.kontakteer, x$.klikaart, x$.kaibegrupp,
x$.mhprotsent, x$.aadress, x$.timestamp, x$.atimestamp, x$.kood, x$.nimi
FROM klient x$

exception seems to occur before database access.

> For reference, I did some refactoring, which now
> a: allows simple use from IQueryable<T> (extension method)
> b: uses the correct type internally (rather than IComparable), just in
> case there are some optimisations done internally

Thank you.
I switched to this code but it has the same issue.

Andrus.

[Andrus]

I looked into DbLinQ driver CompileColumnRowDelegate where the exception
occurs.
This code uses expression parameter like your code.
Maybe using expression parameters in user code and in DbLinq driver causes
this exception.

I tried to create simple repro against pg_columns table but in this case it
works.

Andrus.

DbLinq driver method where exception occurs:

/// <summary>
/// given column type T (eg. Customer or Order),
/// construct and compile a 'new
Customer(reader.GetInt32(0),reader.GetString(1));'
/// delegate (or similar).
/// </summary>
public static Func<DataReader2,T>

CompileColumnRowDelegate(ProjectionData projData, ref int fieldID)
{

#region CompileColumnRowDelegate
if(projData==null || projData.ctor2==null)
throw new ArgumentException("CompileColumnRow: need projData with ctor2");
ParameterExpression rdr = Expression.Parameter(typeof(DataReader2),"rdr");

List<Expression> ctorArgs = new List<Expression>();
foreach(ProjectionData.ProjectionField projFld in projData.fields)
{
Type fieldType = projFld.FieldType;
MethodCallExpression arg_i = GetFieldMethodCall(fieldType,rdr,fieldID++);
ctorArgs.Add(arg_i);
}

//List<Expression> paramZero = new List<Expression>();
//paramZero.Add( Expression.Constant(0) ); //that's the zero in GetInt32(0)
//MethodCallExpression body = Expression.CallVirtual(minfo,rdr,paramZero);

// exception occurs in this line:
NewExpression newExpr = Expression.New(projData.ctor2, ctorArgs);

List<ParameterExpression> paramListRdr = new List<ParameterExpression>();
paramListRdr.Add(rdr);
StringBuilder sb = new StringBuilder(500);
...

[Marc Gravell]

This might sound daft (repetition), but can you please confirm that
simply removing the OrderBy makes it work?
And it works OK using a regular LINQ "order by" (without the dynamic
property code).
Assuming that is so, it sounds possibly like a glitch in the open-
source provider you are using. However, another line of attack would
be to code a simply query, and then use reflector to look at what the
query is - i.e. reverse something like:

query = query.OrderBy(x => x.City); // your property here...

It would be interesting to contrast to what OrderBy(string) emits..

Marc

[Andrus]

> This might sound daft (repetition), but can you please confirm that
> simply removing the OrderBy makes it work?

Test doesn't actually use any ordering. The comparer variable is unused so
that it cannot
affect to query result.

If I comment out the lines
//Expression<Func<Klient, IComparable>> comparer =

// Expression.Lambda<Func<Klient, IComparable>>(

// Expression.Property(param, propertyName), param);

Code works OK.

> And it works OK using a regular LINQ "order by" (without the dynamic
> property code).

I tried

var query = db.Klients.OrderBy("Nimi");

return query.ToList();

and this causes the same error. So error occurs even without using your
code.

> Assuming that is so, it sounds possibly like a glitch in the open-
> source provider you are using. However, another line of attack would
> be to code a simply query, and then use reflector to look at what the
> query is - i.e. reverse something like:
>
> query = query.OrderBy(x => x.City); // your property here...
>
> It would be interesting to contrast to what OrderBy(string) emits..

Given the results above, is it worth to try it ?

Andrus.

[Marc Gravell]

> Test doesn't actually use any ordering. The comparer variable is
> unused so that it cannot
> affect to query result.
>
> If I comment out the lines
> //Expression<Func<Klient, IComparable>> comparer =
>
> // Expression.Lambda<Func<Klient, IComparable>>(
>
> // Expression.Property(param, propertyName), param);
>
> Code works

I'm having difficulty following this; Expressions are immutable, so
the Expression you had originally (query) should be /completely/
unaffected by you creating **and not applying** an unrelated comparer
Expression. I'm not entirely convinced that this makes sense... the C#
equivalent of spooky action at a distance or something...

> I tried
>
> var query = db.Klients.OrderBy("Nimi");
>
> return query.ToList();
>
> and this causes the same error. So error occurs even without using
> your code.

That *is* my code. You'd need to try
var query = db.Klients.OrderBy(x => x.Nimi);

> Given the results above, is it worth to try it ?

Yes. That would allow us to rule things out. As I see it, there are a
range of contenders:
a: Expression just being plain wrong - but validates fine against
Northwind and LINQ-to-SQL
b: DbLinq not parsing expression correctly - makes no sense if you
never *gave it* the expression
c: The act of building the Expression in this way (without ever using
it) causing an issue - unlikely, but that is the claimed symptom
d: The act of building *any* such (similar) order-by Expression
causing this issue

testing the two vanilla LINQ formats:
var query = db.Klients.OrderBy(x => x.Nimi);
and
var query = from x in db.Klients
orderby x.Nimi
select x;
and comparing to:

var query = db.Klients.OrderBy("Nimi");

would investigate "d" versus "c", and a "reflector" look at the
generated code would indicate any major difference in how the
Expression is built. I'm not doing anything that exciting in building
the Expression in "c" that makes me suspect it, but...

Marc

[Andrus]

Marc,

Trying to reply to your last message causes error from MS news server
similar to:
ill-formed reference id (I'm using OE news reader).

So I replied to your previous message.

> I'm having difficulty following this; Expressions are immutable, so the
> Expression you had originally (query) should be /completely/ unaffected by
> you creating **and not applying** an unrelated comparer Expression. I'm
> not entirely convinced that this makes sense... the C# equivalent of
> spooky action at a distance or something...

I also did'nt believe this.
I re-checked it several times and still found that simply commenting out
unused lines

Expression<Func<Klient, IComparable>> comparer =
Expression.Lambda<Func<Klient, IComparable>>(
Expression.Property(param, propertyName), param);

causes exception to disappear.

> That *is* my code. You'd need to try
> var query = db.Klients.OrderBy(x => x.Nimi);

I'm sorry. I tried

var query = db.Klients.OrderBy(x => x.Nimi);

and this works OK.

> testing the two vanilla LINQ formats:
> var query = db.Klients.OrderBy(x => x.Nimi);

db.GetQueryText(query) returns

SELECT x$.p_kood, x$.regnr, x$.vatpayno, x$.piirkond, x$.postiindek,
x$.tanav, x$.kontaktisi, x$.telefon, x$.faks, x$.email, x$.infomail,
x$.wwwpage, x$.liik, x$.viitenr, x$.riik, x$.riik2, x$.riigikood, x$.hinnak,
x$.erihinnak, x$.myygikood, x$.objekt2, x$.objekt5, x$.objekt7, x$.maksetin,
x$.omakseti, x$.krediit, x$.ostukredii, x$.masin, x$.info, x$.maksja,
x$.elanikud, x$.pindala, x$.grmaja, x$.apindala, x$.kpindala, x$.idmakett,
x$.tulemus, x$.omandisuhe, x$.username, x$.changedby, x$.parool,
x$.hinnaale, x$.mitteakt, x$.kontakteer, x$.klikaart, x$.kaibegrupp,
x$.mhprotsent, x$.aadress, x$.timestamp, x$.atimestamp, x$.kood, x$.nimi

FROM klient x$ ORDER BY x$.nimi

> and
> var query = from x in db.Klients
> orderby x.Nimi
> select x;

db.GetQueryText(query) returns

SELECT x$.p_kood, x$.regnr, x$.vatpayno, x$.piirkond, x$.postiindek,
x$.tanav, x$.kontaktisi, x$.telefon, x$.faks, x$.email, x$.infomail,
x$.wwwpage, x$.liik, x$.viitenr, x$.riik, x$.riik2, x$.riigikood, x$.hinnak,
x$.erihinnak, x$.myygikood, x$.objekt2, x$.objekt5, x$.objekt7, x$.maksetin,
x$.omakseti, x$.krediit, x$.ostukredii, x$.masin, x$.info, x$.maksja,
x$.elanikud, x$.pindala, x$.grmaja, x$.apindala, x$.kpindala, x$.idmakett,
x$.tulemus, x$.omandisuhe, x$.username, x$.changedby, x$.parool,
x$.hinnaale, x$.mitteakt, x$.kontakteer, x$.klikaart, x$.kaibegrupp,
x$.mhprotsent, x$.aadress, x$.timestamp, x$.atimestamp, x$.kood, x$.nimi

FROM klient x$ ORDER BY x$.nimi

> and comparing to:
> var query = db.Klients.OrderBy("Nimi");

db.GetQueryText(query) returns

SELECT p$.p_kood, p$.regnr, p$.vatpayno, p$.piirkond, p$.postiindek,
p$.tanav, p$.kontaktisi, p$.telefon, p$.faks, p$.email, p$.infomail,
p$.wwwpage, p$.liik, p$.viitenr, p$.riik, p$.riik2, p$.riigikood, p$.hinnak,
p$.erihinnak, p$.myygikood, p$.objekt2, p$.objekt5, p$.objekt7, p$.maksetin,
p$.omakseti, p$.krediit, p$.ostukredii, p$.masin, p$.info, p$.maksja,
p$.elanikud, p$.pindala, p$.grmaja, p$.apindala, p$.kpindala, p$.idmakett,
p$.tulemus, p$.omandisuhe, p$.username, p$.changedby, p$.parool,
p$.hinnaale, p$.mitteakt, p$.kontakteer, p$.klikaart, p$.kaibegrupp,
p$.mhprotsent, p$.aadress, p$.timestamp, p$.atimestamp, p$.kood, p$.nimi
FROM klient p$ ORDER BY p$.nimi

I tried to change in your DynamicQueryExtensions class parameter name to x
but problem persists.

Andrus.

[Marc Gravell]

Would it be possible for you to post the /generated/ code? Are you
familiar with "Reflector"? If you compile the *working* code
(x=>x.Nimi) and load it into Reflector, we could have a look at what
it generated? I should then be able to compare this to what the
dynamic code uses...

Unfortunately without runnable code this isn't something I can do
directly... but it has piqued my interest...

Marc

[Andrus]

> Would it be possible for you to post the /generated/ code? Are you
> familiar with "Reflector"?
> If you compile the *working* code
> (x=>x.Nimi) and load it into Reflector, we could have a look at what
> it generated? I should then be able to compare this to what the
> dynamic code uses...

Generally No. I desided to download reflector sometime.
However before downloading it asks my personal data.
So I hit Cancel and did'nt download it.
I can study reflector if you need.

> Unfortunately without runnable code this isn't something I can do
> directly... but it has piqued my interest...

I sent test case to you by e-mail.

Andrus.

[Marc Gravell]

I got it, cheers.

There doesn't seem to be anything too awry in the query code... the
only remaining thing I can think is to try something simpler:

ParameterExpression param =
Expression.Parameter(typeof(Klient), "x");
IOrderedQueryable<Klient> query =
db.Klients.OrderBy<Klient, string>(
Expression.Lambda<Func<Klient, string>>(
Expression.Property(param, "Nimi"), param ));

This is different to the others not by much... it uses "string"
instead of IComparable in my earlier code - but this is exactly what
the reflection approach does too (just more indirectly). The only
difference between the above and the compiler code is that the
compiler uses "memberof" in Expression.Property(), where-as we use the
string overload. However, the fact that you aren't seeing a
PropertyNotDefinedForType (ArgumentException) means that this has
worked

Again, it isn't runnable without the database (*please* don't send me
that! I don't "do" pg...), but I wonder if the issue isn't more
related to either the assembly-resolve tricks you are pulling, or
maybe (less likely) tied into the property ordering issue you are
seeing elsewhere.

But frankly, I don't know why it doesn't want to play. It works fine
on the test samples I have here...

Marc

[Andrus]

> There doesn't seem to be anything too awry in the query code... the
> only remaining thing I can think is to try something simpler:

I tried this but got same error.

> Again, it isn't runnable without the database

It may be possible to change dbLinq driver code so that error occurss before
accessing to database but this is a lot of work.

> (*please* don't send me that!

I can create account to you to access my test database over internet

> I don't "do" pg...),

DbLinq suppors MS Sql server also.

> but I wonder if the issue isn't more
> related to either the assembly-resolve tricks you are pulling,

I commented out first two lines in Main()

//System.IO.File.Delete("EntityExtension.dll");
//AppDomain.CurrentDomain.AssemblyResolve +=

In this case there aren't any custom assembly resolution in my code. However
the problem
persists.

> or
> maybe (less likely) tied into the property ordering issue you are
> seeing elsewhere.

I added DbLinq driver source code to my solution and tried to debug it.
I do'nt have enough knowledge to find the issue.
Also as I described other thread VS 2008B2 debugger shows only start of my
53 parameter
constructor argument list, even with copy/paste to notepad and there is no
magnifier glass like for string properties. This makes comparison of
passes/expected constructor arguments complicated.
Error occurs in line

NewExpression newExpr = Expression.New(projData.ctor2, ctorArgs);

definitions are:

List<Expression> ctorArgs = new List<Expression>();

ProjectionData projData;
public class ProjectionData{
...
public System.Reflection.ConstructorInfo ctor2;
..
}

How to create method which shows which compares or dumps
projData.ctor2 and ctorArgs arguments and shows nonmatching arguments ?

MS Framework Exception info does not contain such information.

> But frankly, I don't know why it doesn't want to play. It works fine
> on the test samples I have here...

I tried to create simple test sample but it works also in this case.

Andrus.