c# - Filling a DataSet or DataTable from a LINQ query result set


Translate

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


Всички отговори
  • Translate

    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?


  • Sam Lee
    Translate

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


  • Translate

    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.


  • Translate

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


  • Translate

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


  • Translate

    If you use the return type of IEnumerable.It helps 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>();
    

  • Translate

    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://docs.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.