How do I fill a DataSet or a DataTable from a LINQ query resultset ?

Question

How do you expose a LINQ query as an ASMX web service? Usually, from the business tier, I can return a typed DataSet or DataTable which can be serialized for transport over ASMX.

How can I do the same for a LINQ query? Is there a way to populate a typed DataSet or DataTable via a LINQ query?:

public static MyDataTable CallMySproc()    
{    
string conn = ...;

MyDatabaseDataContext db = new MyDatabaseDataContext(conn);    
MyDataTable dt = new MyDataTable();

// execute a sproc via LINQ
var query = from dr in db.MySproc().AsEnumerable
select dr;

// copy LINQ query resultset into a DataTable -this does not work !    
dt = query.CopyToDataTable();

return dt;
}

How can I get the resultset of a LINQ query into a DataSet or DataTable? Alternatively, is the LINQ query serializeable so that I can expose it as an ASMX web service?

Answer 1

If you use a return type of IEnumerable, you can return your query variable directly.

Answer 2

Could you create a class object and return a list(T) of the query?

Answer 3

As mentioned in the question, IEnumerable has a CopyToDataTable method:

IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
    select order;

// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();

Why won't that work for you?

Answer 4

Make a set of Data Transfer Objects, a couple of mappers, and return that via the .asmx.
You should never expose the database objects directly, as a change in the procedure schema will propagate to the web service consumer without you noticing it.

Answer 5

Why would you want to do this? Datasets are heavyweight objects with an enormous payload when you serialize them (I'd guess this is true also for DataTables). Lars' suggestion is the way to go

Answer 6

To perform this query against a DataContext class, you'll need to do the following:

MyDataContext db = new MyDataContext();
IEnumerable<DataRow> query = (from order in db.Orders.AsEnumerable()
                              select new
                              {
                                 order.Property,
                                 order.Property2
                              }) as IEnumerable<DataRow>;
return query.CopyToDataTable<DataRow>();

Without the "as IEnumerable;" you will se the following compilation error:

Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)