none
LINQ RRS feed

  • Question

  •  

    DataTable retVal = new DataTable();

                retVal.Columns.Add("id", typeof(int));

                retVal.Columns.Add("Item", typeof(string));

                retVal.Columns.Add("UOM", typeof(string));

                retVal.Columns.Add("Qty", typeof(decimal));

                retVal.Columns.Add("Cost", typeof(decimal));

                retVal.Columns.Add("Total Each", typeof(decimal));

                retVal.TableName = "J";

       DataTable dt = new DataTable();

                var query = from c in retVal.AsEnumerable()

                            where c.Field<int>("id") > 0

    //need help

                            select c;

                dt = query.CopyToDataTable();

    retVal Data

    id

    item

    uom

    qty

    cost

    Total Each

    1

    Mulch

    Each

    5

    100

    20

    2

    Mulch

    Each

    5

    100

    20

    3

    Rock

    Ton

    9

    900

    100

    Query needed

    item

    uom

    qty

    cost

    Total Each

    Mulch

    Each

    10

    100

    20

    Rock

    Ton

    9

    900

    100

    C# .net app I just cant get this query , I know slq but havent used linq on a datatable

    Sunday, August 18, 2013 9:00 PM

Answers

  • Try this:

     
    DataTable retVal = new DataTable();

    retVal.Columns.Add("Item", typeof(string));
    retVal.Columns.Add("UOM", typeof(string));
    retVal.Columns.Add("Qty", typeof(decimal));
    retVal.Columns.Add("Cost", typeof(decimal));
    retVal.Columns.Add("Total Each", typeof(decimal));
    retVal.TableName = "J";

    DataTable dt = retVal.Clone();

    retVal.Columns.Add("id", typeof(int));

    DataRow dr = retVal.NewRow();
    dr["id"] = 1;
    dr["Item"] = "Mulch";
    dr["UOM"] = "Each";
    dr["Qty"] = 5.0;
    dr["Cost"] = 100.00;
    dr["Total Each"] = 20;

    DataRow dr2 = retVal.NewRow();
    dr2["id"] = 2;
    dr2["Item"] = "Mulch";
    dr2["UOM"] = "Each";
    dr2["Qty"] = 5.0;
    dr2["Cost"] = 100.00;
    dr2["Total Each"] = 20;

    DataRow dr3 = retVal.NewRow();
    dr3["id"] = 3;
    dr3["Item"] = "Rock";
    dr3["UOM"] = "Ton";
    dr3["Qty"] = 9.0;
    dr3["Cost"] = 900.00;
    dr3["Total Each"] = 100;

    retVal.Rows.Add(dr);
    retVal.Rows.Add(dr2);
    retVal.Rows.Add(dr3);


    var query = from G1 in retVal.AsEnumerable() 

    where G1.Field<int>("id") > 0                  

    group G1 by new {                       

    Item = G1.Field<string>("Item"),                       

    UOM = G1.Field<string>("UOM"),                       

    Cost = G1.Field<decimal>("Cost"),                       

    TotalEach = G1.Field<decimal>("Total Each")                   

    } into G                   

    select new {                       

    Item = G.Key.Item,                       

    UOM = G.Key.UOM,                       

    Qty = G.Sum(x => x.Field<decimal>("Qty")),                       

    Cost = G.Key.Cost,                       

    TotalEach = G.Key.TotalEach                   

    };

    foreach (var item in query)
    {
    dt.Rows.Add(item.Item, item.UOM, item.Qty, item.Cost, item.TotalEach);
    }



    • Edited by AbhishekJain86 Monday, August 19, 2013 7:04 AM
    • Proposed as answer by Damon Bu - MSFT Tuesday, August 20, 2013 9:51 AM
    • Marked as answer by watruba1 Tuesday, August 20, 2013 11:08 PM
    Monday, August 19, 2013 6:53 AM

All replies

  • Try this:

     
    DataTable retVal = new DataTable();

    retVal.Columns.Add("Item", typeof(string));
    retVal.Columns.Add("UOM", typeof(string));
    retVal.Columns.Add("Qty", typeof(decimal));
    retVal.Columns.Add("Cost", typeof(decimal));
    retVal.Columns.Add("Total Each", typeof(decimal));
    retVal.TableName = "J";

    DataTable dt = retVal.Clone();

    retVal.Columns.Add("id", typeof(int));

    DataRow dr = retVal.NewRow();
    dr["id"] = 1;
    dr["Item"] = "Mulch";
    dr["UOM"] = "Each";
    dr["Qty"] = 5.0;
    dr["Cost"] = 100.00;
    dr["Total Each"] = 20;

    DataRow dr2 = retVal.NewRow();
    dr2["id"] = 2;
    dr2["Item"] = "Mulch";
    dr2["UOM"] = "Each";
    dr2["Qty"] = 5.0;
    dr2["Cost"] = 100.00;
    dr2["Total Each"] = 20;

    DataRow dr3 = retVal.NewRow();
    dr3["id"] = 3;
    dr3["Item"] = "Rock";
    dr3["UOM"] = "Ton";
    dr3["Qty"] = 9.0;
    dr3["Cost"] = 900.00;
    dr3["Total Each"] = 100;

    retVal.Rows.Add(dr);
    retVal.Rows.Add(dr2);
    retVal.Rows.Add(dr3);


    var query = from G1 in retVal.AsEnumerable() 

    where G1.Field<int>("id") > 0                  

    group G1 by new {                       

    Item = G1.Field<string>("Item"),                       

    UOM = G1.Field<string>("UOM"),                       

    Cost = G1.Field<decimal>("Cost"),                       

    TotalEach = G1.Field<decimal>("Total Each")                   

    } into G                   

    select new {                       

    Item = G.Key.Item,                       

    UOM = G.Key.UOM,                       

    Qty = G.Sum(x => x.Field<decimal>("Qty")),                       

    Cost = G.Key.Cost,                       

    TotalEach = G.Key.TotalEach                   

    };

    foreach (var item in query)
    {
    dt.Rows.Add(item.Item, item.UOM, item.Qty, item.Cost, item.TotalEach);
    }



    • Edited by AbhishekJain86 Monday, August 19, 2013 7:04 AM
    • Proposed as answer by Damon Bu - MSFT Tuesday, August 20, 2013 9:51 AM
    • Marked as answer by watruba1 Tuesday, August 20, 2013 11:08 PM
    Monday, August 19, 2013 6:53 AM
  • Thanks, that works
    Tuesday, August 20, 2013 11:09 PM