>>>So is there a reason you didn't create a new table an manually loop through the rows? You are trying to force LINQ to do something it wasn't designed to do (create a new DataTable with a different structure). If you need the results as a DataTable, it needs to have a structure defined so that DataRows can be created for it. If you don't want to do that, you could write a method that would convert an IEnumberable< object> to a DataTable. Or you could create a class to hold the results of your query and use that instead of a DataTable to pass the results to the other function.
>>
>>I thought I can use LINQ to do this transformation in memory without a need to loop through the rows. But looks like I would need to do it manually as suggested by Bill originally.
>>
>>Unless I will try to incorporate most of the code from our conversion methods here.
>>
>>The only reason I need a datatable is because our method to convert to our XML string accepts datatable. Since it loops through every row, it means I would need double looping. I'd rather try to figure out if I can somehow create something similar to use and do that loop directly myself.
>
>You do realize that the LINQ query would also be looping through the rows, right? The only thing you could do without looping would be to add DataColumns with expressions (http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx), but that wouldn't handle the proper or replicate functions without some very messy code.

Now I am wondering if I should have used needed transformation directly in SQL. Yesterday I was converting another method with similar code, so I just went ahead with direct SQL to get the data the way I wanted - this is much easier for me.

In this particular case I have stored procedure I can use (although VFP code doesn't use it and the stored procedure uses two extra joins to get few extra columns which are not used in this code) and that's why I wanted to do this transformation client side. Also, I suspect proper function should be quicker in C# than embedding it into SQL.

I am thinking I'll give up and simply send the direct SQL instead of using this stored procedure.