Dynamic Linq on Datatables

[Evert]

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(theStonglyTypedRow => theStonglyTypedRow .SomeColumn
== checkedListBox.Item[index] .Value)
}
}

But then I was caught by 'Deffered Execution' (my old construction was based
on DataView.RowFilter 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\DynamicQuery'. 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

 

[Evert]

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

 

[Marc Gravell]

I have posted a good assortment of dynamic LINQ samples to this forum;
if you post something that is complete (i.e. compiles even if it
doesn't run) I'll happily try to fill in some blanks - but actually, I
think your current approach (compiled, selecting by case) is the most
reliable - all we need to fix is the deferred execution, which is
usually as simply as bringing a "capture" into the same scope (rather
than using the outer scope).

For example:

for (int index = 0; index < checkedListBox.Item.Count; index++)
{
if (... Checked..)
{
SomeType value= checkedListBox.Item[index] .Value;
query = query.Where(theStonglyTypedRow => theStonglyTypedRow
..SomeColumn
== value);
}
}

"value" gets it's value immediately (not deferred), and the way that
captured variables work means that this should act as different
variables on each pass.

But like I say: if you can provide something that almost works, I
should be able to tweak it.

Marc

 

[Marc Gravell]

btw, you can use .AsQueryable() to get any IEnumerable<T> to 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

 

[Jon Skeet [C# MVP]]

I have posted a good assortment of dynamic LINQ samples to this forum;
if you post something that is complete (i.e. compiles even if it
doesn't run) I'll happily try to fill in some blanks - but actually, I
think your current approach (compiled, selecting by case) is the most
reliable - all we need to fix is the deferred execution, which is
usually as simply as bringing a "capture" into the same scope (rather
than using the outer scope).

For example:

for (int index = 0; index < checkedListBox.Item.Count; index++)
{
if (... Checked..)
{
SomeType value= checkedListBox.Item[index] .Value;
query = query.Where(theStonglyTypedRow => theStonglyTypedRow
.SomeColumn
== value);
}
}

I may have been misreading the OP's intention, but the reference to
SQL "IN" clauses suggests that instead of wanting an "AND" he really
wants logical "OR" - in which case we'd need to capture all the values
once, and use a single Where clause.

As I say though, I could be totally wrong

Jon

 

[Marc Gravell]

Yes, reading it again it appears that a List<T> to hold the desired
("any of") values, and single Where => Contains should do the job
nicely. Hard to be 100% sure either way without clarification...

Marc

 

[Marc Gravell]

For info on the dynamic "select" - this is a little bit more
problematic. The approach here depends on the scenario - for example
here I'm using data-binding to do it fairly easily (although a bit
messily); note that this also contains a first-stab at an "in"
implementation:

using System;
using System.Windows.Forms;
using System.Linq;
using System.Collections.Generic;

class Program {
class Foo {
public int Key { get; set; }
public double Value { get; set; }
}
static void Main() {
Application.EnableVisualStyles();
// invent some data
List<Foo> foos = new List<Foo>();
Random rand = new Random(123456);
for (int i = 0; i < 1000; i++) {
foos.Add(new Foo { Key = rand.Next(1, 6), Value =
rand.NextDouble() });
}

using (RadioButton rb1 = new RadioButton
{
Text = "Projection 1",
Dock = DockStyle.Bottom,
Checked = true})
using (RadioButton rb2 = new RadioButton
{
Text = "Projection 2",
Dock = DockStyle.Bottom
})
using (DataGridView dgv = new DataGridView {
Dock = DockStyle.Fill
})
using (Button btn = new Button {
Text = "Fill",
Dock = DockStyle.Bottom
})
using (CheckedListBox clb = new CheckedListBox
{
Dock = DockStyle.Bottom,
Items = {1,2,3,4,5}
})
using (Form form = new Form
{
Text = "LINQ demo",
Controls =
{
rb1, rb2, btn, dgv, clb
}
}) {
btn.Click += (o,e) => {
// build the "in" where
var selected =
clb.CheckedItems.Cast<int>().ToDictionary(x=>x);
var query = foos.Where(x =>
selected.ContainsKey(x.Key));

// use the desired projection
if (rb1.Checked) {
dgv.DataSource = query.ToArray(); // vanilla
} else {
dgv.DataSource = query.Select(x => new
{
Fixed = 1,
Bloop = x.Value,
Blap = 2 * x.Value,
Blop = -x.Value,
x.Key
}).ToArray();
}

};
Application.Run(form);
}
}
}

 

[Evert]

Thank you for your very quick reponse, to clariy thing below (simplified)
extracts from working code.

NOTE:

- It is code from a program reporting climate change effects for different
plants/animals ect
- Wharehouse is the (denormalized) strongly typed datatable
- As you can see, I could not find a Linq alternative for my
CheckedListBoxControl problem, so I reverted to the RowFilter solution
- For each case statement I need to repeat code (here only two are shown,
but my program has 8 cases).

So hopelfully you can help this Linq-Newbie to simplify some things.


--- CODE EXTRACT - START

switch (selector3ReportItem)
{
case ReportItems.FloraFaunaList:
{
var query =
(from ClimateResponseDataSet.WharehouseRow wr in
_climateResponseDataSet.Wharehouse
where !String.IsNullOrEmpty(wr.ResponseGroup)
&& !String.IsNullOrEmpty(wr.FloraFaunaList)
select new { speciesGroupNameLabel =
wr.Field<string>(GetSpeciesGroupColumnName()), floraFaunaListLabel =
wr.FloraFaunaList, responseGroupLabel = wr.ResponseGroup, scientificNameLabel
= wr.ScientificName, speciesNameLabel =
wr.Field<string>(GetSpeciesColumnName()) }).Distinct();

if (selector1ComboItem.Value !=
ReportItems.All.ToString())
{
query = query.Where(wr =>
wr.speciesGroupNameLabel == selector1ComboItem.Value);
}

tableHack = query.CopyToDataTableHack();
viewHack = new DataView(tableHack);

if ((selector4PopupContainerEdit.Enabled == true))
{
string dynamicOperator = String.Empty;
bool nothingChecked = true;
for (int i = 1; i <
selector4CheckedListBoxControl.Items.Count; i++)
{
if
(selector4CheckedListBoxControl.Items.CheckState == CheckState.Checked)
{
nothingChecked = false;
if (viewHack.RowFilter != String.Empty)
{
dynamicOperator = " OR ";
}
viewHack.RowFilter += dynamicOperator +
String.Format("floraFaunaListLabel = '{0}'",
selector4CheckedListBoxControl.Items.Value);
}
}

if (nothingChecked)
{
viewHack = null;
}
}
}
break;

case ReportItems.TargetSpecies:
{
var query =
(from ClimateResponseDataSet.WharehouseRow wr in
_climateResponseDataSet.Wharehouse
where !String.IsNullOrEmpty(wr.ResponseGroup)
&& !String.IsNullOrEmpty(wr.TargetSpecies)
select new { speciesGroupNameLabel =
wr.Field<string>(GetSpeciesGroupColumnName()), targetSpeciesLabel =
wr.TargetSpecies, responseGroupLabel = wr.ResponseGroup, scientificNameLabel
= wr.ScientificName, speciesNameLabel =
wr.Field<string>(GetSpeciesColumnName()) }).Distinct();

if (selector1ComboItem.Value !=
ReportItems.All.ToString())
{
query = query.Where(wr =>
wr.speciesGroupNameLabel == selector1ComboItem.Value);
}

RowFilter code...

}
break;



--- CODE EXTRACT - END

 

[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]

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

Marc

static Func<int, bool> Or(this Func<int, bool> lhs, Func<int,
bool> rhs) {
return x => lhs(x) || rhs(x);
}
static Func<int, bool> And(this Func<int, bool> lhs, Func<int,
bool> rhs) {
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, bool> where = 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(where).ToArray();
}

 

[Marc Gravell]

(generic Or/And)

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

 

[Evert]

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]

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>(this Expression<Func<T,
bool>> lhs, Expression<Func<T, bool>> rhs) {
ParameterExpression param = Expression.Parameter(typeof(T),
"x");
return Expression.Lambda<Func<T,
bool>>(Expression.AndAlso(Expression.Invoke(lhs, param),
Expression.Invoke(rhs, param)), param);
}
static Expression<Func<T, bool>> Or<T>(this Expression<Func<T,
bool>> lhs, Expression<Func<T, bool>> rhs) {
ParameterExpression param = Expression.Parameter(typeof(T),
"x");
return Expression.Lambda<Func<T,
bool>>(Expression.OrElse(Expression.Invoke(lhs, param),
Expression.Invoke(rhs, param)), param);
}

 

[Evert]

Thank you for your help!

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

Evert

 

[Evert]

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
CheckedListBoxControl in a generic List of type string. Then I (equi) join
this list with my query.