I use C#, not VB.Net, so please forgive me for answering using samples in C#, but I hope I can provide some help nevertheless.
Your second example above seems to be 90% of the way to a working solution, with two things (possibly only one, because I don't know what conveniences VB.Net provides for Linq to DataTables) missing that are preventing you from getting a DataTable from Linq.
I created a test app and attempted to create a Linq query like the ones you model above. I discovered that I had to use
from xxx in tableName.AsEnumerable() ...
(where the call to AsEnumerable() was the crucial insight) to get Linq to accept a DataTable name in the
from
clause. If your Linq query would not compile, this might be your solution to that part of the problem.
The second thing I experimented with was getting Linq to return a DataTable - because, as you say, Linq by itself returns an IEnumerable of an anonymous type. This is where the ObjectShredder comes in. I copied the ObjectShredder example code verbatim, and it worked. To use the ObjectShredder, you convert the IEnumerable returned by your Linq query into a DataTable with the following:
DataTable result = queryResult.CopyToDataTable();
.
Here's my test method:
private static DataTable JoinDataTablesWithLinq()
{
var query = from inv in _tblInvoice.AsEnumerable()
join item in _tblLineItem.AsEnumerable()
on inv["InvoiceId"] equals item["InvoiceId"]
select new
{
CustomerName = inv["CustomerName"],
ItemName = item["ItemName"],
Quantity = item["Quantity"]
};
return query.CopyToDataTable();
}
Here are some useful links:
ObjectShredder (which you probably already have):
http://msdn.microsoft.com/en-us/library/bb669096.aspx[
^]
Using Linq with DataTables:
http://dotnetarchitect.wordpress.com/2009/03/18/using-linq-to-manipulate-data-in-datasetdatatable/[
^]