19

I spend a few hours trying to translate simple SQL to lambda LINQ

SELECT ID, AVG(Score) FROM myTable
GROUP BY ID

Any idea?

Yuri
  • 2,820
  • 4
  • 28
  • 40

3 Answers3

32
from t in myTable
group t by new {
  t.ID
} into g
select new {
  Average = g.Average(p => p.Score),
  g.Key.ID
}

or Lambda

myTable.GroupBy(t => new  {ID = t.ID})
   .Select (g => new {
            Average = g.Average (p => p.Score), 
            ID = g.Key.ID 
         })
Leon Bambrick
  • 26,009
  • 9
  • 51
  • 75
spajce
  • 7,044
  • 5
  • 29
  • 44
2

The equivalent in Linq-to-Objects would be something like the below. 

var results = from row in myTable
              group row by row.Id into rows 
              select new 
              {
                  Id = rows.Key,
                  AverageScore = rows.Average(row => row.Score)
              };

It's only slightly different for an ORM like entity framework. Namely, you would need to go through the data context or an appropriate DbSet/ObjectSet.

Anthony Pegram
  • 123,721
  • 27
  • 225
  • 246
0
var _result =   from a in myTable
                group a by a.ID into g
                select new
                {
                    ID = g.Key.ID,
                    AverageResult = g.Average(x => x.Score)
                }
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    Note that `g.Key.ID` will not exist and will not compile. Only `g.Key` is needed in this snippet. – Anthony Pegram Mar 29 '13 at 03:34
  • @AnthonyPegram why it won't compile? I just tested it. – John Woo Mar 29 '13 at 03:39
  • This version? You should get an error message roughly stating "'int' does not contain a definition for 'ID' and no extension method 'ID' accepting..." (Substituting `int` for whatever the actual property type is for ID.) The point is that `g.Key` *is* the ID. It's not an object containing an ID property. – Anthony Pegram Mar 29 '13 at 03:41