How to create a descending time ordered join in EF

Question

I am trying to improve the performance of code and a set of EF queries. There are three tables involved. Devices is a list of devices with device specific properties. Monitors is a list of individual data elements that can be sampled for a device. MonitorSamples stores individual samples of each monitor for a given timestamp. I am trying to return a list of the latest data sample for each monitor for each device.

Below are the unoptimized methods for returning that data. I would like to push as much responsibility onto the database and not create so many back to back requests. If optimizing just one order where the query returns the latest MonitorSample for each device, that would cut down the number of requests, but if I could optimize this into a single db query, that would be best. I have considered constructing a view to represent that data, but if I can do it in linq or a standard query, that would be much better. Because of an index on the timestamp, the performance of returning a single MonitorSample is great, but when lots of them are requested, the performance starts to degrade quite a bit.

I am trying to figure out the best way to create a join that includes all the information I need and select it into a new object that I can work with. I am having a problem ordering the data by timestamp for each individual monitor that would be included in the join.

public class MonitorSampleData
{
    public string Name;
    public DateTime Time;
    public int Sequence;
    public string Type;
    public string Unit;
    public string Value;
}

public class DeviceData
{
    public string DeviceName;
    public string DeviceType;
    public ICollection<MonitorSampleData> Monitors;
}

public partial class Device
{
    public int Id;
    public string Name;
    public string Description;
    public string Location;
    public int? SampleRate;
    public bool? SynchronizedSampling;
    public virtual DeviceType Type;
    public virtual DeviceGroup Group;
    public virtual DeviceState State;
    public virtual ICollection<Monitor> Monitors;      
}

public partial class Monitor
{
    public int Id;
    public string Name;
    public bool Enabled;
    public int Sequence;
    public virtual Device Device;
    public virtual MonitorUnitType UnitType;
    public virtual MonitorDataType DataType;
    public virtual ICollection<MonitorSample> Samples;     
}

public partial class MonitorSample
{    
    public int Id;
    public System.DateTime Time;
    public string Value;
    public virtual Monitor Monitor;
}

public ICollection<DeviceData> GetLatestDeviceData()
{
    ICollection<DeviceData> data = new List<DeviceData>();
    using (var context = new ApplicationDbContext())
    {
        var devices = context.GetDevices();
        foreach (var device in devices)
        {
            var deviceData = new DeviceData();
            deviceData.DeviceName = device.Name;
            deviceData.DeviceType = device.Type.ShortName;
            foreach (var monitor in device.Monitors)
            {
                var sample = context.GetLatestDataByMonitor(monitor);

                if (sample != null)
                {
                    MonitorSampleData monitorData = new MonitorSampleData();
                    monitorData.Name = monitor.Name;
                    monitorData.Time = sample.Time;
                    monitorData.Sequence = monitor.Sequence;
                    monitorData.Type = monitor.DataType.Name;
                    monitorData.Unit = monitor.UnitType.Name;
                    monitorData.Value = sample.Value;
                    deviceData.Monitors.Add(monitorData);
                }
            }
            data.Add(deviceData);
        }
    }
    return data;
}

public MonitorSample GetLatestDataByMonitor(Monitor monitor)
{
    MonitorSample sample = null;
    if (monitor != null)
    {
        sample = (from s in MonitorSamples
                    where s.Monitor.Id == monitor.Id
                    orderby s.Time descending
                    select s).FirstOrDefault();
    }
    return sample;
}

Answer 1

Sounds like standard projection LINQ query.

Utilize the navigation properties, replace foreach with from, var sample = with let sample =, and don't use custom methods, but embed everything inside the query and class initializer expressions.

This way the result will be retrieved with a single database query.

E.g.

var data =
    (from device in context.Devices
     select new DeviceData
     {
         DeviceName = device.Name,
         DeviceType = device.Type.ShortName,
         Monitors = 
             (from monitor in device.Monitors
              let sample = (from s in monitor.Samples
                            orderby s.Time descending
                            select s).FirstOrDefault()
              select new MonitorSampleData
              {
                  Name = monitor.Name,
                  Time = sample.Time,
                  Sequence = monitor.Sequence,
                  Type = monitor.DataType.Name,
                  Unit = monitor.UnitType.Name,
                  Value = sample.Value
              }).ToList()
     }).ToList();

Instead of

let sample = (from s in monitor.Samples
              orderby s.Time descending
              select s).FirstOrDefault()

you could try the functionally equivalent construct

from sample in monitor.Samples
    .DefaultIfEmpty()
    .OrderByDescending(s => s.Time)
    .Take(1)

which might get better SQL translation.

Update: I've missed the if (sample != null) check in your original code. So the real LINQ equivalent would be the second construct with .DefaultIfEmpty() removed, which will force INNER JOIN:

from sample in monitor.Samples
    .OrderByDescending(s => s.Time)
    .Take(1)