Dynamic "WHERE" like queries on memory objects

Question

What would be the best approach to allow users to define a WHERE-like constraints on objects which are defined like this:

Collection<object[]> data
Collection<string> columnNames

where object[] is a single row.

I was thinking about dynamically creating a strong-typed wrapper and just using Dynamic LINQ but maybe there is a simpler solution?

DataSet's are not really an option since the collections are rather huge (40,000+ records) and I don't want to create DataTable and populate it every time I run a query.

Answer 1

What kind of queries do you need to run? If it's just equality, that's relatively easy:

public static IEnumerable<object[]> WhereEqual(
    this IEnumerable<object[]> source,
    Collection<string> columnNames,
    string column,
    object value)
{
    int columnIndex = columnNames.IndexOf(column);
    if (columnIndex == -1)
    {
        throw new ArgumentException();
    }
    return source.Where(row => Object.Equals(row[columnIndex], value);
}

If you need something more complicated, please give us an example of what you'd like to be able to write.

Answer 2

When just using object, LINQ isn't really going to help you very much... is it worth the pain? And Dynamic LINQ is certainly overkill. What is the expected way of using this? I can think of a few ways of adding basic Where operations.... but I'm not sure how helpful it would be.

Answer 3

I'm thinking about something like this:

((col1 = "abc") or (col2 = "xyz")) and (col3 = "123")

Ultimately it would be nice to have support for LIKE operator with % wildcard.

Answer 4

How about embedding something like IronPython in your project? We use that to allow users to define their own expressions (filters and otherwise) inside a sandbox.

Answer 5

If I get your point : you'd like to support users writting the where clause externally - I mean users are real users and not developers so you seek solution for the uicontrol, code where condition bridge. I just though this because you mentioned dlinq.

---

So if I'm correct what you want to do is really :

• give the user the ability to use column names
• give the ability to describe a bool function (which will serve as where criteria)
• compose the query dynamically and run

For this task let me propose : Rules from the System.Workflow.Activities.Rules namespace. For rules there're several designers available not to mention the ones shipped with Visual Studio (for the web that's another question, but there're several ones for that too).I'd start with Rules without workflow then examine examples from msdn. It's a very flexible and customizable engine.

One other thing: LINQ has connection to this problem as a function returning IQueryable can defer query execution, you can previously define a query and in another part of the code one can extend the returned queryable based on the user's condition (which then can be sticked with extension methods).

Answer 6

Thank you all guys - I've finally found it. It's called NQuery and it's available from CodePlex. In its documentation there is even an example which contains a binding to my very structure - list of column names + list of object[]. Plus fully functional SQL query engine.

Just perfect.