Filling a DataSet or a DataTable from a LINQ query result set

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 a 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 a 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 could I put the result set of a LINQ query into a DataSet or a DataTable?
Alternatively, can the LINQ query be serializable so that I can expose it as an ASMX web service?

score 99 · Accepted Answer · edited May 04 '12 at 09:01

99

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?

edited May 04 '12 at 09:01

Mathew Thompson

55,877
15
127
148

answered Aug 15 '08 at 16:27

Jon Galloway

52,327
25
125
193

30

To everyone wondering why CopyToDataTable() doesn't work on their machine: This function is not part of .NET 3.5 SP1 nor will it be of .NET 4.0; it has been restricted to IEnumerable and does not work for IEnumerable -- http://bit.ly/dL0G5 – motto Feb 02 '10 at 22:00

score 33 · Answer 2 · edited Dec 17 '13 at 14:39

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<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?)

score 25 · Answer 3 · answered Aug 15 '08 at 16:42

25

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 Aug 15 '08 at 16:42

Lars Mæhlum

6,074
3
28
32

score 19 · Answer 4 · edited Oct 18 '08 at 18:26

19

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

edited Oct 18 '08 at 18:26

Kilhoffer

32,375
22
97
124

answered Aug 01 '08 at 14:10

Dave Ward

59,815
13
117
134

score 15 · Answer 5 · edited Oct 14 '12 at 12:13

15

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

edited Oct 14 '12 at 12:13

Paul Fleming

24,238
8
76
113

answered Aug 08 '08 at 12:17

Brian Childress

1,140
9
13

score 6 · Answer 6 · edited Jul 22 '20 at 06:45

6

If you use IEnumerable as the return type, it will return your query variable directly.

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>();

edited Jul 22 '20 at 06:45

Tanjim Ahmed Khan

650
1
9
21

answered Apr 11 '18 at 11:55

Vijay S

282
2
7
15

score 3 · Answer 7 · answered Jun 20 '19 at 20:14

For the sake of completeness, these solutions do not work for EF Core (at least not for EF Core 2.2). Casting to IEnumerable<DataRow>, as suggested in the other answers here, fails. Implementing this class and extension methods worked for me https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/implement-copytodatatable-where-type-not-a-datarow.

Why it's not built into EF Core, I have not idea.

Filling a DataSet or a DataTable from a LINQ query result set

7 Answers7

Linked