LINQ and 2 datatables

Question

I have 2 datatables in a dataset. One table has a list called CostTypes. Just an Id and Description field.

The other datatable is the master table and has many records and one of the columns is the cost type. There will be cost types that are not reference in this datatable. There is another column in this databale called cost.

What I am trying to do is get a summary by cost type with a total of the cost. But I want ALL cost types listed any values not in the master table will be zero.

CostType table

Id, Description

1,Marketing

2,Sales

3,Production

4,Service

---

Master table

Id, Cost, CostTypeId

1,10,1

2,120,1

3,40,3

So I would like to see a result in a datable (if possible) so I can bind to datagridview

Marketing  130

Sales       0

Production  40

Service     0

Thanks for the help everyone, this is what I came up from the answers - Can anyone suggest any improvements???

Also how can I convert the result in query1 into a datable???

  var query1 =
        from rowCT in costTypes.AsEnumerable()
        from rowSTD in stdRates.AsEnumerable()
              .Where( d => d.Field<int?>( "CostTypeId" ) == rowCT.Field<int?>( "CostTypeId" ) )
              .DefaultIfEmpty()
        group new { row0 = rowCT, row1 = rowSTD }
        by rowCT.Field<string>( "Description" ) into g
        select new
        {
            g.Key,
            Cost = g.Sum( x => x.row1 == null ? 0 : x.row1.Field<decimal>( "Cost" ) ),
            TotalCost = g.Sum( x => x.row1 == null ? 0 : x.row1.Field<decimal>( "TotalCost" ) ),
            TotalHours = g.Sum( x => x.row1 == null ? 0 : x.row1.Field<decimal>( "TotalHours" ) ),
            TotalLabourCost = g.Sum( x => x.row1 == null ? 0 : x.row1.Field<decimal>( "TotalLabourCost" ) )
        }
        ;

Answer 1

I came up with a simpler bit of linq than others seemed to use. Thanks to Martin Liversage for the code to create the input data.

var costTypes = new DataTable("CostTypes");
costTypes.Columns.Add("Id", typeof(Int32));
costTypes.Columns.Add("Description", typeof(String));
costTypes.Rows.Add(1, "Marketing");
costTypes.Rows.Add(2, "Sales");
costTypes.Rows.Add(3, "Production");
costTypes.Rows.Add(4, "Service");

var costEntries = new DataTable("CostEntries");
costEntries.Columns.Add("Id", typeof(Int32));
costEntries.Columns.Add("Cost", typeof(Int32));
costEntries.Columns.Add("CostTypeId", typeof(Int32));
costEntries.Rows.Add(1, 10, 1);
costEntries.Rows.Add(2, 120, 1);
costEntries.Rows.Add(3, 40, 3);

var cte = costTypes.Rows.Cast<DataRow>();
var cee = costEntries.Rows.Cast<DataRow>();
var output = cte.Select(
ct => new {
    Description = ct["Description"], 
    Sum = cee.Where(ce=>ce["CostTypeId"].Equals(ct["Id"])).Sum(ce=>(int)ce["Cost"])
    }

);

This may lose efficiency on larger tables since each cost type will search the cost entry table whereas using grouping I suspect you only need one pass over the table. Personally I'd prefer the (to my mind) simpler looking code. It will depend on your use case though.

Answer 2

You can use the Sum extension method to compute the cost. It will return 0 if the collection is empty which is exactly what you want:

var costTypes = new DataTable("CostTypes");
costTypes.Columns.Add("Id", typeof(Int32));
costTypes.Columns.Add("Description", typeof(String));
costTypes.Rows.Add(1, "Marketing");
costTypes.Rows.Add(2, "Sales");
costTypes.Rows.Add(3, "Production");
costTypes.Rows.Add(4, "Service");

var costEntries = new DataTable("CostEntries");
costEntries.Columns.Add("Id", typeof(Int32));
costEntries.Columns.Add("Cost", typeof(Int32));
costEntries.Columns.Add("CostTypeId", typeof(Int32));
costEntries.Rows.Add(1, 10, 1);
costEntries.Rows.Add(2, 120, 1);
costEntries.Rows.Add(3, 40, 3);

var costs = costTypes
  .Rows
  .Cast<DataRow>()
  .Select(
    dr => new {
      Id = dr.Field<Int32>("Id"),
      Description = dr.Field<String>("Description")
    }
  )
  .Select(
    ct => new {
      ct.Description,
      TotalCost = costEntries
        .Rows
        .Cast<DataRow>()
        .Where(ce => ce.Field<Int32>("CostTypeId") == ct.Id)
        .Sum(ce => ce.Field<Int32>("Cost"))
    }
  );

The result is:

Description|TotalCost
-----------+---------
Marketing  |      130
Sales      |        0
Production |       40
Service    |        0

You can create a new DataSet quite simply:

var costsDataTable = new DataTable("Costs");
costsDataTable.Columns.Add("Description", typeof(String));
costsDataTable.Columns.Add("TotalCost", typeof(Int32));
foreach (var cost in costs)
  costsDataTable.Rows.Add(cost.Description, cost.TotalCost);

If the linear search performed by the Where in the code above is a concern you can improve the performance by creating a lookup table in advance:

var costEntriesLookup = costEntries
  .Rows
  .Cast<DataRow>()
  .Select(
    ce => new {
      Cost = ce.Field<Int32>("Cost"),
      CostTypeId = ce.Field<Int32>("CostTypeId")
    }
  )
  .ToLookup(ce => ce.CostTypeId, ce => ce.Cost);
var costs = costTypes
  .Rows
  .Cast<DataRow>()
  .Select(
    dr => new {
      Id = dr.Field<Int32>("Id"),
      Description = dr.Field<String>("Description")
    }
  )
  .Select(
    ct => new {
      ct.Description,
      TotalCost = costEntriesLookup.Contains(ct.Id)
        ? costEntriesLookup[ct.Id].Sum()
        : 0
    }
  );

Answer 3

Maybe something like this:

Test data:

DataTable dt=new DataTable();
dt.Columns.Add("Id",typeof(int));
dt.Columns.Add("Description",typeof(string));

dt.Rows.Add(1,"Marketing");
dt.Rows.Add(2,"Sales");
dt.Rows.Add(3,"Production");
dt.Rows.Add(4,"Service");

DataTable dt2=new DataTable();
dt2.Columns.Add("Id",typeof(int));
dt2.Columns.Add("Cost",typeof(int));
dt2.Columns.Add("CostTypeId",typeof(int));

dt2.Rows.Add(1,10,1);
dt2.Rows.Add(2,120,1);
dt2.Rows.Add(3,40,1);

Linq query

var query=(
            from row in dt.AsEnumerable()
            from row1 in dt2.AsEnumerable()
                  .Where (d =>d.Field<int>("Id")==row.Field<int>("Id") )
                  .DefaultIfEmpty()
            group new{row,row1} 
            by row.Field<string>("Description") into g 
            select new
            {
                g.Key,
                Cost=g.Sum (x =>x.row1==null?0:x.row1.Field<int>("Cost"))
            }
            );

Result

Key        Cost
Marketing  10
Sales      120
Production 40
Service    0