Multi sums into one anonymous type with linq?

Question

I have this simple datatable :

 type (string)   |       cnt (int)     
_____________________________________
aaa                         1
aaa                         2
aaa                         10
bbb                         1
bbb                         1

I want to produce 1 anonymous type like this :

{
sumAAA= 13 , sumBBB=2  //13=1+2+10....
}

something like : (psuedo code )

var obj= dt.AsEnumerable().Select(f=> new { sumAAA =f.sumOfCntOfAaa , sumBBB =f.sumOfCntOfBbb });

any help ?

edit , this will help you

   DataTable dt = new DataTable("myTable");
    dt.Columns.Add("cnt", typeof (int));
    dt.Columns.Add("type", typeof (string));
    DataRow row = dt.NewRow();
    row["cnt"] = 1;
    row["type"] = "aaa";
    dt.Rows.Add(row);
    row = dt.NewRow();
    row["cnt"] = 2;
    row["type"] = "aaa";
    dt.Rows.Add(row);
    row = dt.NewRow();
    row["cnt"] = 10;
    row["type"] = "aaa";
    dt.Rows.Add(row);
    row = dt.NewRow();
    row["cnt"] = 1;
    row["type"] = "bbb";
    dt.Rows.Add(row);
    row = dt.NewRow();
    row["cnt"] = 1;
    row["type"] = "bbb";
    dt.Rows.Add(row);

You want to turn rows to properties, essentially denormalizing, or transposing the schema of, the data? — Jodrell, Jul 25 '12 at 13:02
You can group the data as in Scorpi0's answer, but then you would have to dynamically create an anonymous type which would not be much good for coding against, see this question http://stackoverflow.com/q/4024754/659190. Unless you know what groups will be present in the data at compile time? — Jodrell, Jul 25 '12 at 13:10
@Jodrell I just want an object which contains both sums. I don't want to use any loops. — Royi Namir, Jul 25 '12 at 13:12
@Jodrell yes. the types suppose to be hard coded in the query. — Royi Namir, Jul 25 '12 at 13:17

score 7 · Accepted Answer · answered Jul 25 '12 at 13:20

7

Be dynamic:

dynamic result = new ExpandoObject();
dt.AsEnumerable()
  .GroupBy(r => r.Field<String>("type"))
  .ToList()
  .ForEach(g=> ((IDictionary<String, Object>)result)["sum" + g.Key.ToUpper()] = g.Sum(r=>r.Field<Int32>("cnt")));

Console.WriteLine(result.sumAAA);
Console.WriteLine(result.sumBBB);

Works for a arbitary number of different types, not only aaa and bbb.

Output:

13
2

answered Jul 25 '12 at 13:20

sloth

99,095
21
171
219

@Royi Namir, its what you orginally asked for but needlessly pushes all your type checking to runtime so potentially very "not nice". However, is a good answer to the question. Why not write this with javascript. – Jodrell Jul 25 '12 at 13:26
Isn't `r.Field("type")` is like `r["type"].ToString()`? – Royi Namir Jul 25 '12 at 13:31
@Royi Not exactly. `Field` casts the value to `String`, while `ToString()`, well, is a call to `ToString()`. `Field` It will also convert `DBNull` to `null`. – sloth Jul 25 '12 at 13:35
@BigYellowCactus How can I safely get a property from expando , but checking before if the property exists ? do if i try myExpando.NoitExists - it won't go bang ? – Royi Namir Jul 25 '12 at 14:30
1

@RoyiNamir Either use [RuntimeOps.ExpandoTryGetValue](http://msdn.microsoft.com/en-us/library/dd989438.aspx) or just cast the `ExpandoObject` to `IDictionay` and use either [IDictionay.Contains](http://msdn.microsoft.com/en-us/library/system.collections.idictionary.contains.aspx) or [IDictionay.Keys.Contains](http://msdn.microsoft.com/en-us/library/system.collections.idictionary.keys.aspx). – sloth Jul 25 '12 at 14:37
@BigYellowCactus I was just tesing it without dynamic by `Dictionary result = new Dictionary ();` and it works. So why did you use dynamic ? – Royi Namir Jul 25 '12 at 17:03
` Dictionary result = new Dictionary(); dt.AsEnumerable() .GroupBy(r => r.Field("type")) .ToList() .ForEach(g=> ((IDictionary)result)["sum" + g.Key.ToUpper()] = g.Sum(r=>r.Field("cnt"))); Console.WriteLine(result["sumAAA"] +" "+result["sumBBB"]);` – Royi Namir Jul 25 '12 at 17:14
Of course you could use a plain old Dictionary. But when using a dynamic ExpandoObject, you can write `result.sumAAA` instead of `result["sumAAA"]`. I thought that was what you wanted to achieve :-) – sloth Jul 25 '12 at 18:40

Richard · Answer 2 · 2014-06-03T08:22:40.703

4

In an assembly all anonymous types with the same properties with the same type and order are the same type. So it is possible to create a new instance of the same anonymous type:

Version One

var totals = new {
    SumAs = 0,
    SumBs = 0
};

var result = dt.AsEnumerable().Select(dr => {
    totals = new {
        SumAs = totals.SumAs + (dr["type"].Equals("aaa") ? (int)dr["cnt"] : 0),
        SumBs = totals.SumBs + (dr["type"].Equals("bbb") ? (int)dr["cnt"] : 0)
    };
    return totals;
}).Last();

But with aggregate one can do better...

Version Two

var result2 = dt.AsEnumerable().Aggregate(new { SumAs = 0, SumBs = 0 }, (total, dr) => {
    return new {
        SumAs = total.SumAs + (dr["type"].Equals("aaa") ? (int)dr["cnt"] : 0),
        SumBs = total.SumBs + (dr["type"].Equals("bbb") ? (int)dr["cnt"] : 0)
    };
});

Update: Complete Sample of Using Version 2

(Tuples used because they are much easier to set up):

Paste into a default C# Console project:

using System;
using System.Linq;

class Program {
    static void Main(string[] args) {
        var input = Enumerable.Range(1, 10).Select(x => Tuple.Create(x, x*x));

        var res = input.Aggregate(new { Firsts = 0, Seconds = 0}, (total, val) => {
            return new {
                Firsts = total.Firsts + val.Item1,
                Seconds = total.Seconds + val.Item2
            };
        });

        Console.WriteLine("Firsts: {0}; Seconds: {1}", res.Firsts, res.Seconds);
    }
}

edited Jun 03 '14 at 08:22

answered Jul 25 '12 at 13:15

Richard

106,783
21
203
265

This version 2 looks nice but... it is simply can't compile! The reason is that the type checker fail to unify the two similar anonymous class... -1. – Earth Engine Jun 03 '14 at 05:03
@EarthEngine: Just tested here (using Tuples rather than a DataTable as input as easier to set up): no problem. The approach does depend on both initial and increment definitions of the anonymous type having both the same names and type in the same order (eg. if the content of the input is `double` ensure the initial value is created with doubles). I'll add my test code to the answer. – Richard Jun 03 '14 at 08:20
@Richard This seems to be an issue of version of C# compiler. In Visual studio 2010 this is an error, whilst in MonoDevelop 4.0.12 this is not (and it seems to assume that anonymous types with the same fields are the same). So you might need to update your answer. – Earth Engine Jun 03 '14 at 12:36
@EarthEngine: It could easily be version dependent, but without spending time working out what IDE, BCL or language feature that determines it I would rather focus on the current version (and perhaps previous) of tools. (Older versions: create and use a named struct, can be a private member of the containing class.) – Richard Jun 03 '14 at 13:36

Hans Z · Answer 3 · 2012-07-25T13:21:59.173

Is there any reason why you don't want to use 2 LINQ statements?

var obj = new {
    sumAAA = dt.Rows
        .Where(x => x["type"] == "aaa")
        .Sum(y => (int) y["cnt"]), 
    sumBBB = dt.Rows
        .Where(x => x["type"] == "bbb")
        .Sum(y => (int) y["cnt"])
};

Or a simple for loop?

int sAAA = 0; int sBBB = 0;

foreach (DataRow row in dt.Rows)
{
    if(row["type"] == "aaa")
        sAAA += (int) row["cnt"];
    else
        sBBB += (int) row["cnt"];
}

var obj = new { sumAAA = sAAA, sumBBB = sBBB };

EDIT: Oops... Anonymous objects are immutable, changed my answer accordingly

Cyril Gandon · Answer 4 · 2012-07-25T13:19:24.463

0

var list = dt.AsEnumerable()
          .GroupBy(row => row["type"])
          .Select(grp => new { Type = grp.Key, 
                             Sum = grp.Sum(c => (int)c["cnt"])
          });

This will give you a list of anonymous {type, sum}, that must be a good start!

edited Jul 25 '12 at 13:19

answered Jul 25 '12 at 13:02

Cyril Gandon

16,830
14
78
122

I want **one** anonymous type with both results . – Royi Namir Jul 25 '12 at 13:08

Multi sums into one anonymous type with linq?

4 Answers4