Dynamic Linq on Datatables

[=?Utf-8?B?RXZlcnQ=?=]

In my (Windows Forms) project I am using strongly typed datatables. Now I am
trying to convert my 'rowfilter/group by' query logic to Linq. Most queries I
can easily convert, but some I am having difficulties with: the ones dynamic
in nature. I solved it (temporary) by copy-pasting static queries. This is
not an ideal solution because now my code contains some duplicate code.

Some of the problems I have are:

- My select statements differ based on input from a dropdown. So for example
if a user selects from a dropdown 'A' the linq query must select 'ColomnX,
ColomnY, ColomnZ'. If he selects 'B' the query must select 'ColumnD,
ColumnE, ColumnZ'.

- Based on the number of active checkboxes from a CheckedListBox a 'Where'
statement must be dynamically created (just the same as you would do with a
SQL 'IN' statement). I tried something like:

for (int index = 0; index < checkedListBox. Item.Count; index++)
{
if (... Checked..)
{
query = query.Where(the StonglyTypedRow =theStonglyType dRow .SomeColumn
== checkedListBox. Item[index] .Value)
}
}

But then I was caught by 'Deffered Execution' (my old construction was based
on DataView.RowFil ter which worked fine this way)

Now I was looking around for some library and found in Scott Gu's blog a
reference to a Visual Studio 2008 example, '\LinqSamples\D ynamicQuery'. No I
am having difficulties in getting it to work on datables because they seem to
implement the IQueryable interface. Does anybody have a working solution?

Thanks for any help!

Yours sincerely,

Evert Wiesenekker

[=?Utf-8?B?RXZlcnQ=?=]

Sorry an important typo:
'they seem to implement the IQueryable interface' must be ' they seem NOT to
implement the IQueryable interface'
"Evert" wrote:

In my (Windows Forms) project I am using strongly typed datatables. Now I am
trying to convert my 'rowfilter/group by' query logic to Linq. Most queries I
can easily convert, but some I am having difficulties with: the ones dynamic
in nature. I solved it (temporary) by copy-pasting static queries. This is
not an ideal solution because now my code contains some duplicate code.

Some of the problems I have are:

- My select statements differ based on input from a dropdown. So for example
if a user selects from a dropdown 'A' the linq query must select 'ColomnX,
ColomnY, ColomnZ'. If he selects 'B' the query must select 'ColumnD,
ColumnE, ColumnZ'.

- Based on the number of active checkboxes from a CheckedListBox a 'Where'
statement must be dynamically created (just the same as you would do with a
SQL 'IN' statement). I tried something like:

for (int index = 0; index < checkedListBox. Item.Count; index++)
{
if (... Checked..)
{
query = query.Where(the StonglyTypedRow =theStonglyType dRow .SomeColumn
== checkedListBox. Item[index] .Value)
}
}

But then I was caught by 'Deffered Execution' (my old construction was based
on DataView.RowFil ter which worked fine this way)

Now I was looking around for some library and found in Scott Gu's blog a
reference to a Visual Studio 2008 example, '\LinqSamples\D ynamicQuery'. No I
am having difficulties in getting it to work on datables because they seem to
implement the IQueryable interface. Does anybody have a working solution?

Thanks for any help!

Yours sincerely,

Evert Wiesenekker

[Marc Gravell]

btw, you can use .AsQueryable() to get any IEnumerable<Tto an
IQueryable<T- although in this case I think the IEnumerable<T>
should be fine (it should work pretty similarly either way... i.e.
using the LINQ-to-objects behaviour).

Marc

[Marc Gravell]

Is it mainly the "or" we are struggling with here? Suggest Union? (not
tested); "and" maps to another Where.

However - the code really doesn't make it clear what you are trying to
do and which bits are working (and which aren't) - however, if you
have it working with RowFilter, perhaps use it?

Marc

[Marc Gravell]

(generic Or/And)

static Func<T, boolOr<T>(this Func<T, boollhs, Func<T, bool>
rhs) {
return x =lhs(x) || rhs(x);
}
static Func<T, boolAnd<T>(this Func<T, boollhs, Func<T, bool>
rhs) {
return x =lhs(x) && rhs(x);
}

[Marc Gravell]

Or, since we know this is LINQ-to-objects (not full expression), can
simplify and simply combine the predicates?

Marc

static Func<int, boolOr(this Func<int, boollhs, Func<int,
boolrhs) {
return x =lhs(x) || rhs(x);
}
static Func<int, boolAnd(this Func<int, boollhs, Func<int,
boolrhs) {
return x =lhs(x) && rhs(x);
}
static void Main() {
int[] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
15 };
// filter to even
Func<int, boolwhere = x =x % 2 == 0;
// or divisible by 3
where = where.Or(x =x % 3 == 0);
// and greater than 5
where = where.And(x =x 5);
// get the results
int[] results = data.Where(wher e).ToArray();
}

[=?Utf-8?B?RXZlcnQ=?=]

Thank you!

Yes I was struggling with the Or. No I have to figure out how to incorporate
the values of a selected checkboxes inside the ListBox list into the where
clause.
But I still have to get the picture, the linq syntax using predicates is
still somewhat confusing for me.

That's why I liked Scotts Gu's blog because the dynamic string syntax is
very comfortable to work with. But I guess this is not the way-to-linq.
Evert

"Marc Gravell" wrote:

(generic Or/And)

static Func<T, boolOr<T>(this Func<T, boollhs, Func<T, bool>
rhs) {
return x =lhs(x) || rhs(x);
}
static Func<T, boolAnd<T>(this Func<T, boollhs, Func<T, bool>
rhs) {
return x =lhs(x) && rhs(x);
}

[Marc Gravell]

That's why I liked Scotts Gu's blog because the dynamic string syntax is

very comfortable to work with. But I guess this is not the way-to-linq.

The string approach is useful as a fallback, but if you know the types
I would use compiled code. For completeness, here is the Expression
equivalents for combining and/or:

static Expression<Func <T, bool>And<T>(thi s Expression<Func <T,
bool>lhs, Expression<Func <T, bool>rhs) {
ParameterExpres sion param = Expression.Para meter(typeof(T) ,
"x");
return Expression.Lamb da<Func<T,
bool>>(Expressi on.AndAlso(Expr ession.Invoke(l hs, param),
Expression.Invo ke(rhs, param)), param);
}
static Expression<Func <T, bool>Or<T>(this Expression<Func <T,
bool>lhs, Expression<Func <T, bool>rhs) {
ParameterExpres sion param = Expression.Para meter(typeof(T) ,
"x");
return Expression.Lamb da<Func<T,
bool>>(Expressi on.OrElse(Expre ssion.Invoke(lh s, param),
Expression.Invo ke(rhs, param)), param);
}

[=?Utf-8?B?RXZlcnQ=?=]

Thank you for your help!

I will dive further into your examples and consider (in advance) it answered.

Evert

"Marc Gravell" wrote:

That's why I liked Scotts Gu's blog because the dynamic string syntax is
very comfortable to work with. But I guess this is not the way-to-linq.

The string approach is useful as a fallback, but if you know the types
I would use compiled code. For completeness, here is the Expression
equivalents for combining and/or:

static Expression<Func <T, bool>And<T>(thi s Expression<Func <T,
bool>lhs, Expression<Func <T, bool>rhs) {
ParameterExpres sion param = Expression.Para meter(typeof(T) ,
"x");
return Expression.Lamb da<Func<T,
bool>>(Expressi on.AndAlso(Expr ession.Invoke(l hs, param),
Expression.Invo ke(rhs, param)), param);
}
static Expression<Func <T, bool>Or<T>(this Expression<Func <T,
bool>lhs, Expression<Func <T, bool>rhs) {
ParameterExpres sion param = Expression.Para meter(typeof(T) ,
"x");
return Expression.Lamb da<Func<T,
bool>>(Expressi on.OrElse(Expre ssion.Invoke(lh s, param),
Expression.Invo ke(rhs, param)), param);
}

[=?Utf-8?B?RXZlcnQ=?=]

Maybe the following helps other people:

For my problem I found (another) solution:

In a foreach loop I put all the values of checked items inside my
CheckedListBoxC ontrol in a generic List of type string. Then I (equi) join
this list with my query.

"Evert" wrote:

Thank you for your help!

I will dive further into your examples and consider (in advance) it answered.

Evert

"Marc Gravell" wrote:

That's why I liked Scotts Gu's blog because the dynamic string syntax is
very comfortable to work with. But I guess this is not the way-to-linq.

The string approach is useful as a fallback, but if you know the types
I would use compiled code. For completeness, here is the Expression
equivalents for combining and/or:

static Expression<Func <T, bool>And<T>(thi s Expression<Func <T,
bool>lhs, Expression<Func <T, bool>rhs) {
ParameterExpres sion param = Expression.Para meter(typeof(T) ,
"x");
return Expression.Lamb da<Func<T,
bool>>(Expressi on.AndAlso(Expr ession.Invoke(l hs, param),
Expression.Invo ke(rhs, param)), param);
}
static Expression<Func <T, bool>Or<T>(this Expression<Func <T,
bool>lhs, Expression<Func <T, bool>rhs) {
ParameterExpres sion param = Expression.Para meter(typeof(T) ,
"x");
return Expression.Lamb da<Func<T,
bool>>(Expressi on.OrElse(Expre ssion.Invoke(lh s, param),
Expression.Invo ke(rhs, param)), param);
}