Filling a DataSet or DataTable from a LINQ query result set

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?

Add Comment
5 Answer(s)

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

Answered on July 11, 2016.
Add Comment

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.

Answered on July 11, 2016.
Add Comment

Create a class object and return a list(T) of the query.

Answered on July 11, 2016.
Add Comment

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?

Answered on July 11, 2016.
Add Comment

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
    as IEnumerable<DataRow>;
return query.CopyToDataTable<DataRow>();

Without the as IEnumerable<DataRow>; you will see 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?)

Answered on July 11, 2016.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.