Linq query having group by

Question

RowNum IMAGEID     SCANEDATE                COUNT
1      10000131    2012-07-04 00:00:00.000  1
2      10002626    2012-08-03 00:00:00.000  1
3      10003348    2012-09-06 00:00:00.000  1
4      10003589    2012-09-15 00:00:00.000  8
5      10003590    2012-05-15 00:00:00.000  8
6      10003591    2012-04-15 00:00:00.000  8
7      10003592    2012-03-15 00:00:00.000  8
8      10003595    2012-02-15 00:00:00.000  8
9      10003596    2012-09-15 00:00:00.000  8
10     10003598    2012-09-15 00:00:00.000  8
11     10003599    2012-09-15 00:00:00.000  8

I have above datatable on which I need to apply linq query for getting result as follows

1) All images ids having same date should be listed in same cell(, seperated), and the count column should have the count of each date's respective no. of image ids

 Scan Date   Image ID  Count


      11/27/2007 1001529,1001530,1001531,1001532,1001533,1001534,1001537,1001538,1001539,1001540,1001542    11

      11/20/2008 1002501,1002502,1002503,1002504,1002505,1002506,1002507,1002508,1002509,1002510,1002511,1002512,1002513,1002514,1002515,1002516,1002517,1002518,1002519,1002520,    20

      7/5/2011   1015237,1015238    2

      7/6/2011   1015248,1015249,1015259,1015260,1015286,1015287,1015288,1015289,1015290,1015291,1015292,1015293,1015294,1015295,1015296,1015297,1015347,1015348,1015358,1015359,    32
      1015370,1015371,1015381,1015382,1015396,1015397,1015410,1015411,1015412,1015413,1015429,1015430

      7/7/2011   1015444,1015445    2

Please provide me the query for doing the above.

Sergey Berezovskiy · Accepted Answer · 2014-01-30T11:48:29.060

5

Group table rows by SCANEDATE field. Then project each group of rows by selecting scan date (key of group), joining all images ids into string, and getting count of rows in a group:

table.AsEnumerable()
     .GroupBy(r => r.Field<DateTime>("SCANEDATE"))
     .Select(g => new {
          ScanDate = g.Key,
          Ids = String.Join(",", g.Select(r => r.Field<int>("IIMAGEID"))),
          Count = g.Count()
      });

edited Jan 30 '14 at 11:48

answered Jan 30 '14 at 11:24

Sergey Berezovskiy

232,247
41
429
459

this is giving me g does not exist in the current context – user2783853 Jan 30 '14 at 11:45
@user2783853 sorry, there was typo in lambda goes to operator. It should be `g => new {..` instead of `g = new { ...` – Sergey Berezovskiy Jan 30 '14 at 11:48

score 1 · Answer 2 · answered Jan 30 '14 at 11:25

var results = from p in tabel
              group p.IMAGEID  by p.SCANEDATE into g
              select new { SCANEDATE = g.Key, IMAGEID = g.ToList() };

Or as a non-query expression:

var results = tabel.GroupBy(p => p.SCANEDATE , p => p.IMAGEID ,
                         (key, g) => new { SCANEDATE = key, IMAGEID = g.ToList() });

Basically the contents of the group (when view as an IEnumerable<T>) is a sequence of whatever values were in the projection (p.car in this case) present for the given key.

Linq query having group by

2 Answers2

Linked