Julie Lerman Blog - Converting a LINQ to Entities query to a DataTable

Friday, September 07, 2007

Converting a LINQ to Entities query to a DataTable

[Update at bottom of post]

I wrote this in response to a question on the ADO.NET Orcas forums and thought I would copy it here.

Note that Danny Simmons writes more information about Entity Framework and DataTables in the thread.

Here's my solution for now...

LINQ to Datasets does allow for conversions from queries to Datatables, but it doesn't seem to be able to deal with ObjectQueries. I then queried my objectquery to return an IQueryable and also an IEnumerable, but I couldn't get the datatable conversion to work. Look for CopyToDataTable in the VS2008 documentation for more info on that.

So since I couldn't get at it I rolled my own using Mike Taulty's previous example (not for ObjectQueries though) as a start.

This example uses the ObjectStateManager so I can dynamically get at the values. As long as I'm already using the ObjetStateManager, I also use it to get some metadata, even though I could have used the MetadataWorkspace for that.

Assuming we are starting with nwrows which is an ObjectQuery for a simple Linq to Entities query...

Dim dt As New DataTable

Dim currow As Integer = 0 'just a counter to keep track of my for each position

  For Each r In nwrows
  'get statemanager for current row
   Dim stateEntry = nwts.ObjectStateManager.GetObjectStateEntry(CType(r, Data.Objects.DataClasses.IEntityWithKey).EntityKey)

'this will only happen once, build the dataColumns, I'm not bothering with their names

   If dt.Columns.Count = 0 Then
    For i = 0 To stateEntry.CurrentValues.FieldCount - 1
     dt.Columns.Add()
    Next
   End If

'now we can add data, grabbing current values
Dim dtrow As DataRow = dt.NewRow

   For idata = 0 To stateEntry.CurrentValues.FieldCount - 1
    dtrow.Item(idata) = stateEntry.CurrentValues(idata)
   Next

dt.Rows.Add(dtrow)

currow += 1

[Update]

Note: It looks like Andrew Conrad, who is on the ADO.NET team, also got inspired today by the forum question as he has written a much more indepth (read "not quite the hack that I wrote") solution to the loss of the datatable conversion capability. In fact he explains why we can't use it with non-Linq to Dataset queries:

In the original Linq CTP and the first Orcas Beta, we included a DataSet specific Linq operator called CopyToDataTable<T> (It was called ToDataTable at one point also). For Beta 2 of Orcas, we ended up restricting this method to only work with DataRows (or some derived type) via a generic constraint on the method.

So, like a good soldier, he wrote up some sweet code to do the job. I was only pulling in the values. He's got tablenames and types and subtypes as well, with a completely different approach. Definitely check it out. I will probably just use this rather than my hack and try to consider my experimenting with StateObject a good use of my time yesterday! :-)