LINQ Conditional Grouping

Question

I have a problem trying to figure out a LINQ query for the following.

The columns are MeterSerialNumber, Date, DeviceType (M or C), and then 48 reading value columns.

Some meters will have a corrector fitted. For these meters there will be both an M (DeviceType) row and a C row for the same date. I need just the C rows for these meters.

e.g.

I need a query to convert this:

MeterSerialNumber,Date,DeviceType,Reading1,Reading2,etc
8017680S,19/08/2010,M,12,23,etc 
4504761S,19/08/2010,M,12,23,etc
4504761S,19/08/2010,C,12,23,etc
08000963,19/08/2010,M,12,23,etc

To this:

MeterSerialNumber,Date,DeviceType,Reading1,Reading2,etc
8017680S,19/08/2010,M,12,23,etc
4504761S,19/08/2010,C,12,23,etc
08000963,19/08/2010,M,12,23,etc

I suspect I might need nested queries but just can't get my head round it!

do you have this as data structures or is it just in a CSV text file? — Isak Savo, Sep 24 '10 at 11:53
Possible duplicate of [LINQ Conditional Group](https://stackoverflow.com/questions/756827/linq-conditional-group) — tom redfern, May 23 '18 at 14:05

score 1 · Answer 1 · answered Sep 24 '10 at 12:07

1

var query = sourceData.GroupBy(
                x => new { x.MeterSerialNumber, x.Date },
                (k, g) => g.OrderBy(x => x.DeviceType == 'C' ? 0 : 1).First());

answered Sep 24 '10 at 12:07

LukeH

263,068
57
365
409

danijels · Accepted Answer · 2010-09-24T12:18:33.733

1

Or try this:

  var group = meters
    .Where(m => m.DeviceType == "M" && !meters.Any(m2 => m2.MeterSerialNumber == m.MeterSerialNumber && m2.DeviceType == "C"))
    .Union(meters
      .Where(m => m.DeviceType == "C" && meters.Any(m2 => m2.MeterSerialNumber == m.MeterSerialNumber && m2.DeviceType == "M")));

edited Sep 24 '10 at 12:18

answered Sep 24 '10 at 12:12

danijels

5,211
4
26
36

Thanks so much for this. It passes the tests perfectly! Now to figure out how it works... – Dene Sep 24 '10 at 12:20
I actually had to add another check for the date. But I now understand it. Again - thanks a lot – Dene Sep 24 '10 at 12:24

LINQ Conditional Grouping

2 Answers2