CODEWITHSUNDEEP
c#linqdatatable
user222427
user222427

Reputation:

Group by and then select based on those group values

Basically I'm doing some work to fill a datatable. Then once I had that datatable, I'm going my results. Then What I want to do is select from my original datatable based off those results. The code I have below is failing so any help would be fantastic. The second part i'm curious of, is how to I return a collection to iterate for the select

Thanks!

DataTable invoicesDataTable = null;
try
{
    invoicesDataTable = GetInvoiceIds();
}
catch (Exception ex)
{

}

//new work here
var uniqueCountryCustomer = 
    invoicesDataTable
    .AsEnumerable()
    .GroupBy(row => new
        {
            Department = (string)row["Department"], 
            Attorney = (string)row["MatterNumber"], 
            MatterNo = (string)row["CUSTOMERNAME"]
        });

string depart = string.Empty;
string attorn = string.Empty;
string MatNo = string.Empty;
for (int i = 0; i < uniqueCountryCustomer.ToArray().Length; i++)
{
    var results =
        from myRow in invoicesDataTable.AsEnumerable()
        where myRow.Field<string>("Department") == uniqueCountryCustomer.ToArray()[i].ToString() 
            && myRow.Field<string>("MatterNumber") == uniqueCountryCustomer.ToArray()[i].ToString() 
            && myRow.Field<string>("CUSTOMERNAME") == uniqueCountryCustomer.ToArray()[i].ToString()
        select myRow;
    //now do work on each of these rows from the group
}

Upvotes: 0

Views: 538

Answers (3)

Robert S.
Robert S.

Reputation: 2042

I think it is this code that fails:

var results =
    from myRow in invoicesDataTable.AsEnumerable()
    where myRow.Field<string>("Department") == uniqueCountryCustomer.ToArray()[i].ToString() 
        && myRow.Field<string>("MatterNumber") == uniqueCountryCustomer.ToArray()[i].ToString() 
        && myRow.Field<string>("CUSTOMERNAME") == uniqueCountryCustomer.ToArray()[i].ToString()
    select myRow;

You compare each field with the same value which can't be right.

Maybe you can replace ToString() with Department and so on? Those values you used in your GroupBy expression?!

Upvotes: 0

Robert McKee
Robert McKee

Reputation: 21487

The GroupBy has three parts: 1) Select the key 2) Select the values 3) How to project key/values

Fairly good examples here: https://msdn.microsoft.com/en-us/library/vstudio/bb534493(v=vs.100).aspx

var uniqueCountryCustomer = 
    invoicesDataTable
    .AsEnumerable()
    .GroupBy(Key => new
    {
        Department = (string)row["Department"], 
        Attorney = (string)row["MatterNumber"], 
        MatterNo = (string)row["CUSTOMERNAME"]
    },Vals => new {
        something1 = ...,
        something2 = ...
    },(Key,Vals)=>new {
        Key=Key,
        Sum=vals.Select(v=>v.something1).Sum(),
        //If you need these
        Max=vals.Select(v=>v.something1).Max(),
        Min=vals.Select(v=>v.something1).Min(),
        Vals=Vals
    });

foreach(var customer in uniqueCountryCustomer)
{
     ... Do stuff ...
     Console.Writeline("Customer {0} owes us a total of {1}",customer.Key.MatterNo,customer.Sum);
     foreach(var things in customer)
     {
        Console.Writeline("...Bill:{0}",var.something1);
     }
}

Upvotes: 1

Jason Boyd
Jason Boyd

Reputation: 7029

I don't work with datatables... ever, really. But can you not do this:

DataTable invoicesDataTable = null;
try
{
    invoicesDataTable = GetInvoiceIds();
}
catch (Exception ex)
{
}

//new work here
var uniqueCountryCustomer = 
    invoicesDataTable
    .AsEnumerable()
    .GroupBy(row => new
    {
        Department = (string)row["Department"], 
        Attorney = (string)row["MatterNumber"], 
        MatterNo = (string)row["CUSTOMERNAME"]
    });

foreach(var customerGroup in uniqueCountryCustomer)
{
    foreach(var row in customerGroup)
    {
        //now do work on each of these rows from the group
    }
}

Is there some reason you need to get the row from the table again after the grouping?

Upvotes: 1

Related Questions