Dynamic Linq query - how do I build the select clause?

Question

I'm trying to do some tests with Dynamic Linq at the moment, but being new at it, I am having troubles.

Currently I have one DataTable object that I have filled by an SQL query to a database. Now I want to execute dynamic linq queries on this DataTable. This may seem illogical, but my end goal is be able to perform joins on two different DataTables that are filled by two different databases, so with that in mind I hope it makes more sense. I'm trying to understand a simpler situation and experiment a bit with it before I go on to solve that main problem.

Question one is that I am not entirely sure about the choice between IQueryable(Of T) and IEnumerable(Of T). The way I understand is that if you do everything in-memory you choose IEnumerable. I would think that fits for my case then, right? However, when I looked at changing the IEnumerable in IQuerbyable (in the code snippet below), I was surprised to see that "x.Item(Fieldname)" does not work!? What am I missing? The error it gave is: "Late Binding operations cannot be converted to an Expression Tree".

Anyway, lets take a look. I already got part of it working:

    Dim desc As String = "Description"
    Dim status As String = "Status"

    Dim query As IEnumerable(Of DataRow) = From x In tab.AsEnumerable()
    query = query.Where(Function(x As Object) x.Item(status) < 100)

    For Each row As DataRow In query.ToList()
    'Do something
    Next row

This seems to work fine. I used the two string variables, because in the end I want to dynamically decide which field to take. So, "x!Description" is not the way to go for me. However, now I wanted to add a projection to the query, i.e. Select a number of columns.My expectation was that this should work like this:

query = query.Select(Of String)(Function(x As Object) x.Item(desc))

(N.B.: I use Select(Of String), because this specific column has that as type. This should of course in the end be dynamically filled too)

However, this fails during runtime on an InvalidCastException: "Unable to cast object of type 'WhereSelectEnumerableIterator2[System.Data.DataRow,System.String]' to type 'System.Collections.Generic.IEnumerable1[System.Data.DataRow]'." I dont really understand what happens here; I'm guessing something is wrong because I want to return DataRow's, but at the moment only select one field, which happens to be a String. Can anyone explain/help me?

Thanks in advance!

OK, EDIT, as I should probably have given some more info from scratch: I could have a variable number of where- or select-clauses. My idea was I could add these dynamically by looping through the list that contains this information. So I thought I kinda need the .Where() and .Select() functions that IEnumerable offers.

Solution

You declare query to be of type IEnumerable(Of DataRow) in the following line:

Dim query As IEnumerable(Of DataRow) = From x In tab.AsEnumerable()

Now, you want to create an IEnumerable(Of String) with your Select, which is fine.

But you try to asign the result, which is of type IEnumerable(Of String), to query, which is of type IEnumerable(Of DataRow).

query = query.Select(Of String)(Function(x As Object) x.Item(desc))

And since IEnumerable(Of String) can't be cast to IEnumerable(Of DataRow) and vice versa, you get an InvalidCastException.

---

Also, I don't know why you use Function(x As Object) in your Where and Select, better use Function(row As DataRow), since you already know that you are working with a DataRow.

Furthermore, your code could be rewritten like this:

Dim desc As String = "Description"
Dim status As String = "Status"

Dim columnToUse = status

Dim query = From x In tab.AsEnumerable()
            Where x.Item(status) < 100
            Select x(columnToUse)

For Each item as String In query.ToList()
    'Do something
Next 

Changing columnToUse would make you able to select the field you want dynamically.

To select more than one field, you need to return a colleciton of Dictionarys, ExpandoObjects or Tuples or something like that.

Example:

Dim columnToUse = new String() {desc, status} ' select columns dynamically

Dim query = tab.AsEnumerable().Where(Function(row) row.Item(status) < 100)

' Selecting dynamically
Dim result1 = query.Select(function(row) columnToUse.ToDictionary(function(c) c, function(c) row(c)))
Dim result2 = query.Select(Function(row)
                                Dim exp As IDictionary(Of String, Object) = new ExpandoObject()
                                For Each column in columnToUse
                                    exp(column) = row(column)
                                Next
                                return exp
                            End Function)