bcallister (4) [Avatar] Offline
#1
my application allows users to write dynamic LINQ expressions inside a editor against an in-memory DataSet. here is an example user-defined expression:

from row in pDSet.Tables["XXX"].AsEnumerable()
select new
{
SiteID = row.Field<int>("SiteID"),
Gender = row.Field<string>("Gender")
}

when the user executes their query, i need to copy the results of the query into a new System.Data.DataTable instance.

i know that if i was writing the LINQ query during development, that i could do the following to accomplish this task:

DataTable pDTable = new DataTable("Results");
pDTable.Columns.Add("SiteID", typeof(int));
pDTable.Columns.Add("Gender", typeof(string));

foreach (var projection in query)
{
pDTable.Rows.Add(new object[] { projection.SiteID, projection.Gender });
}

however, since the LINQ query is generated dynamically by the end-user, i dont know the fields of their dynamic projection's anonymous type. yet, i still need to be able to copy their results into a new DataTable instance.

i know i could probably use reflection to discover the anonymous type and its fields, but this seems like an inefficient way to accomplish this task and i dont even know how to find the anonymous type since the runtime will generate a random name for it?!

does anyone know how i can accomplish this? your help is greatly appreciated.

thanks,
ben
fabrice.marguerie (224) [Avatar] Offline
#2
Re: help copying dynamic LINQ query results into a DataTable...
Hi Ben,

We demonstrate how to do this in chapter 14, which is about LINQ to DataSet. This chapter is available as a free PDF. You can find it on the book home page.

Fabrice
bcallister (4) [Avatar] Offline
#3
Re: help copying dynamic LINQ query results into a DataTable...
hi Fabrice-

first of all, let me say i loved your book 'LINQ in Action.' i read it cover-to-cover, which is unusual for me. it truely is a great book and thank you for writing it.

unfortunately, your book did not cover the task i needed to accomplish, as described in this thread. rather, it assumes you either have a typed DataSet OR you know the fields of the projected anonymous type - neither of which i have in this situation.

fortunately, i was able to find a solution to this issue today:

http://blogs.msdn.com/aconrad/archive/2007/09/07/science-project.aspx

as a suggestion, it might be useful for you to point your audience to this type of solution in the future. while it is certainly not the 'normal' situation, i think it will come up for lots of developers. it appears from the article that MS discovered this as well. and i definitely believe you will maintain a growing LINQ audience as devs begin to realize the powerful benefits of LINQ and discover your book.

again, thanks for your contribution to the LINQ community.

cheers!
Ben
fabrice.marguerie (224) [Avatar] Offline
#4
Re: help copying dynamic LINQ query results into a DataTable...
I'm pleased to read that you enjoyed the book!
But I'd like to insist smilie
In the bonus chapter 14 about LINQ to DataSet, we cover the exact need you describe. In fact, if you look on page 13 of this chapter, you'll be able to see that we give the exact URL you recommend, in a note that reads as follows:

The source code for the ToDataTable and LoadSequence query operators
is available at http://blogs.msdn.com/aconrad/archive/2007/09/07/
science-project.aspx.
Note that they’ve been renamed CopyToDataTable instead of
ToDataTable and LoadSequence, but we’ve decided to keep the original
names to avoid confusion with the existing CopyToDataTable methods
from System.Data.DataTableExtensions.


The source code for these query operators is available with the complete source code package.

You should download chapter 14 and give it a read. Since you're working with LINQ and DataSets, it can help you further, I think.

Fabrice
bcallister (4) [Avatar] Offline
#5
Re: help copying dynamic LINQ query results into a DataTable...
Fabrice-

thanks for the reply. i had already indeed downloaded, read, and re-read your chapter 14 looking for this solution.

i do see that you reference this same link (an oversight on my part).

what threw me off from your chapter 14 is that in your 'Listing 14.2,' you are *not* accomplishing the same task as in the mentioned link. rather, you are calling 'table.LoadDataRow()' with a new object[] that gets initialized explicitly with the fields of your anonymous type.

your very next statement (after the note) reads 'Early releases of LINQ provided two query operators to perform the same kind of operation - LoadSequence and ToDataTable - but they've been removed in later releases of LINQ, and aren't included in .NET 3.5. But they have been resurrected by Andrew Conrad from Microsoft.'

the bolded portion of this statement is what threw me off because, in my particular case, for some reason i was thinking that the query operator would perform a similar 'foreach' technique, which in the end it indeed does, but it does so after dynamically discovering (via Reflection) the anonymous type's field and property infos.

this was a mistake on my part. it wasnt clear to me how these query operators worked (reflection). an additional sentence to the affect of 'these query operators will dynamically, using reflection, discover the resulting DataTable's columns and row value's from your projected type's fields and properties...' would have put 2 and 2 together in my mind.

at this point, it is obvious to me that i should inferred this from your text because there isnt really any other way (that i can think of) that the query operators could possibly be implemented. but i just didnt see it and that listing and those few words threw me off.

my apologies and thanks again.
ben
fabrice.marguerie (224) [Avatar] Offline
#6
Re: help copying dynamic LINQ query results into a DataTable...
I see where the confusion comes from now. In the next edition, I'll try to add the sentence you suggest smilie