Select last value in a month for all given IDs using LINQ

Question

^{This question is the second part of another question of mine, but this time focused on LINQ-to-SQL.}

I have 2 tables, one containing meter IDs, and another containing measurements for some of the meters in the first table. This is the table structure:

MeterConfig:

• MeterID (int)
• MeterNumber (char[16])
• Type (char[25])

Readings:

• MeterID (int)
• Date (datetime)
• Value (numeric(18,6))

I need to get the last reading (and its date) from a given period for each meter, as well as the meter number. The date a meter was last read can differ from one to the other. Some meters might not have any readings in that period; in that case, they must be ignored.

I managed to do this, but I'm not exactly thrilled by how I did it. Is there a more efficient way of doing it, without having to do almost the same subquery twice?

(from cfg in MeterConfigs
    join r in Readings on cfg.MeterID equals r.MeterID
where
    r.Date >= startDate && r.Date <= endDate
    select new
    {
        Number = cfg.MeterNumber,
        ReadingDate = 
        (
            from r in Readings
            where cfg.MeterID == r.MeterID && r.Date >= startDate && r.Date <= endDate
            orderby r.Date descending
            select r.Date
        ).Take(1),
        Value = 
        (
            from r in Readings
            where cfg.MeterID == r.MeterID && r.Date >= startDate && r.Date <= endDate
            orderby r.Date descending
            select r.Value
        ).Take(1)   
})
.GroupBy(x => x.Number)
.Select(x => x.First());

Answer 1

Here is one possible solution:

MeterConfigs
    .Join(
        Readings.Where(x => x.Date >= startDate && x.Date <= endDate),
        x => x.MeterID,
        x => x.MeterID,
        (o,i) => new { MeterNumber = o.MeterNumber, Date = i.Date, Value = i.Value }
    ).GroupBy(x => x.MeterNumber)
    .Select(x => {
        var lastReading = x.OrderByDescending(y => y.Date).FirstOrDefault();
        return new {
                Number = x.Key,
                ReadingDate = lastReading.Date,
                Value = lastReading.Value
            };
        }
    )

If you have linqpad installed you can compare the results of this query to your current solution with some dummy data I created:

var MeterConfigs = new [] {
new { MeterID = 1, MeterNumber = "123", Type = "foo" },
new { MeterID = 2, MeterNumber = "456", Type = "bar" },
new { MeterID = 3, MeterNumber = "789", Type = "foo" },
new { MeterID = 4, MeterNumber = "101", Type = "bar" },
};

var Readings = new [] {
new { MeterID = 1, Date = new DateTime(2010, 10, 21), Value = 12.3 },
new { MeterID = 1, Date = new DateTime(2010, 10, 20), Value = 4.3 },
new { MeterID = 1, Date = new DateTime(2010, 10, 19), Value = 56.2 },
new { MeterID = 1, Date = new DateTime(2010, 10, 5), Value = 1.4 },
new { MeterID = 2, Date = new DateTime(2010, 10, 20), Value = 8.2 },
new { MeterID = 3, Date = new DateTime(2010, 10, 21), Value = 34.7 },
new { MeterID = 3, Date = new DateTime(2010, 10, 20), Value = 2.9 },
};

var startDate = new DateTime(2010, 10, 1);
var endDate = new DateTime(2010, 10, 21);

MeterConfigs
    .Join(
        Readings.Where(x => x.Date >= startDate && x.Date <= endDate),
        x => x.MeterID,
        x => x.MeterID,
        (o,i) => new { MeterNumber = o.MeterNumber, Date = i.Date, Value = i.Value }
    ).GroupBy(x => x.MeterNumber)
    .Select(x => {
        var lastReading = x.OrderByDescending(y => y.Date).FirstOrDefault();
        return new {
                Number = x.Key,
                ReadingDate = lastReading.Date,
                Value = lastReading.Value
            };
        }
    ).Dump();

(from cfg in MeterConfigs
    join r in Readings on cfg.MeterID equals r.MeterID
where
    r.Date >= startDate && r.Date <= endDate
    select new
    {
        Number = cfg.MeterNumber,
        ReadingDate = 
        (
            from r2 in Readings
            where cfg.MeterID == r2.MeterID && r2.Date >= startDate && r2.Date <= endDate
            orderby r2.Date descending
            select r2.Date
        ).Take(1),
        Value = 
        (
            from r2 in Readings
            where cfg.MeterID == r2.MeterID && r2.Date >= startDate && r2.Date <= endDate
            orderby r2.Date descending
            select r2.Value
        ).Take(1)   
})
.GroupBy(x => x.Number)
.Select(x => x.First()).Dump();

Returns:

Number ReadingDate             Value 
123    10/21/2010 12:00:00 AM  12.3
456    10/20/2010 12:00:00 AM  8.2
789    10/21/2010 12:00:00 AM  34.7

Versus:

Number ReadingDate                       Value 
123    IEnumerable<DateTime> (1 item)    IEnumerable<Double> (1 item)
       10/21/2010 12:00:00 AM            12.3
456    IEnumerable<DateTime> (1 item)    IEnumerable<Double> (1 item)
       10/20/2010 12:00:00 AM            8.2
789    IEnumerable<DateTime> (1 item)    IEnumerable<Double> (1 item)
       10/21/2010 12:00:00 AM            34.7