locked
CopyToDataTable Problem RRS feed

  • Question

  • I am struggling with trying to query an untyped dataset and then put the results into another empty dataset.

    The CopyToDataTable method is not available at the point of assigning query2 to a datatable. It is underlined in red and hovering produces this information:

    Exceptions:

    System.ArgumentNullException

    System.InvalidOperationException

    Error:

    The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable<T>(system.Collections.Generic.IEnumerable<T>)'. There is no impliciit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

    I am trying to group the data and then sum some of the fields for the group. Using foreach to print out the results of query2 produces correct results. I just can't seem to figure out how to copy the grouped and summed data into a datatable for further display and use.

    Below is the code as it is today:

    private void testLinq()
    
       {
    
        DataTable OrderSummary = ds.Tables[0];
    
        var query =
    
         from orders in OrderSummary.AsEnumerable()//table from untyped dataset passed to an external application and is returned filled at run time only
    
         group orders by new
    
         {
    
          shipto = orders.Field<string>("ship_to") + "-" + orders.Field<int>("oeh_alt_addr"),
    
          regn = orders.Field<string>("oeh_region"),
    
          shiptoname = orders.Field<string>("sht_name"),
    
          shipdate = orders.Field<DateTime>("oeh_shp_date"),
    
          shipvia = orders.Field<string>("shp_via"),
    
          hubcode = orders.Field<string>("hubcode"),
    
          shiptype = orders.Field<string>("BoxRackUnit"),
    
          shipadress = orders.Field<string>("sht_address"),
    
          shipcity = orders.Field<string>("sht_city"),
    
          shipzip = orders.Field<string>("sht_zip"),
    
          shipstate = orders.Field<string>("sht_state"),
    
          weightlimit = orders.Field<int>("sht_weight_limit"),
    
          lengthlimit = orders.Field<int>("sht_length_limit"),
    
          liftreq = orders.Field<string>("sht_require_lift"),
    
          lat = orders.Field<decimal>("sht_lat"),
    
          lon = orders.Field<decimal>("sht_long"),
    
          instructions = orders.Field<string>("sht_instr")      
    
         } into st
    
         select st;
    
        
    
        var query2 = 
    
         from st in query.AsEnumerable()
    
         select new 
    
         {
    
          shipto = st.Key.shipto,
    
          totUnits = st.Sum(orders => orders.Field<int>("oeh_units")),
    
          totBoxes = st.Sum(orders => orders.Field<decimal>("oeh_boxes")),
    
          totRacks = st.Sum(orders => orders.Field<decimal>("oeh_racks")),
    
          shiptoname = st.Key.shiptoname,
    
          rgn = st.Key.regn,
    
           shipdate = st.Key.shipdate,
    
          shipvia = st.Key.shipvia,
    
          hubcode = st.Key.hubcode,
    
          shiptype = st.Key.shiptype,
    
          shipaddress = st.Key.shipadress,
    
          shipcity = st.Key.shipcity,
    
          shipzip = st.Key.shipzip,
    
          shipstate = st.Key.shipstate,
    
          weightlimit = st.Key.weightlimit,
    
          lengthlimit = st.Key.lengthlimit,
    
          liftreq = st.Key.liftreq,
    
          lat = st.Key.lat,
    
          lon = st.Key.lon,
    
          instructions = st.Key.instructions
    
         };
    
    
    
        DataTable dt = new DataTable();
    
        dt = query2.CopyToDataTable();//want to copy results of query2 to a datatable for later consumption
    
    
    
        //things print correctly
    
        foreach (var ordernum in query2)
    
        {
    
         Debug.Print(ordernum.shipto);
    
         Debug.Print(ordernum.shiptoname);
    
         Debug.Print(ordernum.rgn.ToString());
    
         Debug.Print(ordernum.totUnits.ToString());
    
         Debug.Print(ordernum.totBoxes.ToString());
    
         Debug.Print(ordernum.totRacks.ToString());
    
         Debug.Print(ordernum.shipdate.ToString());
    
         Debug.Print(ordernum.shipvia.ToString());
    
         Debug.Print(ordernum.hubcode.ToString());
    
         Debug.Print(ordernum.shiptype.ToString());
    
         Debug.Print(ordernum.shipaddress.ToString());
    
         Debug.Print(ordernum.shipcity.ToString());
    
         Debug.Print(ordernum.shipzip.ToString());
    
         Debug.Print(ordernum.shipstate.ToString());
    
         Debug.Print(ordernum.weightlimit.ToString());
    
         Debug.Print(ordernum.lengthlimit.ToString());
    
         Debug.Print(ordernum.liftreq.ToString());
    
         Debug.Print(ordernum.lat.ToString());
    
         Debug.Print(ordernum.lon.ToString());
    
         Debug.Print(ordernum.instructions.ToString());
    
         Debug.Print("");
    
        }
    
       }
    
    

     Any help, direction, or advice is appreciated!!!

    Wednesday, October 6, 2010 6:28 PM

Answers

  • Hello,

    Since you use anonymous types, I would recommend you Call ToList() to store it in memory for later use. For example, in your case,

    var dt = query2.ToList();

    What's more, you could explicitly specify the type, for example,

    var dt = query2.ToList<MyNewType>();

    and then pass this variable to a method as parameter or for later use. You may need to create a new type here.

    Can this meet your need?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:52 AM
    Tuesday, October 12, 2010 8:29 AM