views:

46

answers:

2

Hi

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!

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

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")));
danijels
Thanks so much for this. It passes the tests perfectly! Now to figure out how it works...
Dene
you're welcome :)
danijels
I actually had to add another check for the date. But I now understand it. Again - thanks a lot
Dene