Temporary variables in LINQ select statements for EF Core

c# entity-framework-core linq

Question

I am having trouble writing a slightly complicated Select statement.

My EF objects look like this:

public partial class SensorEvent
{
    public SensorEvent()
    {
       SensorData = new HashSet<SensorData>();
    }

    public int Id { get; set; }
    public int SensorId { get; set; }
    public int RecordTime { get; set; }

    public Sensor Sensor { get; set; }
    public ICollection<SensorData> SensorData{ get; set; }
}

public partial class SensorData
{
    public int Id { get; set; }
    public int SensorEventId { get; set; }
    public string DataType { get; set; }
    public string Description { get; set; }

    public SensorEvent SensorEvent { get; set; }
}

public partial class Sensor
{
    public Sensor()
    {
        SensorEvent = new HashSet<SensorEvent>();
        SensorPlacement = new HashSet<SensorPlacement>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<SensorEvent> SensorEvent{ get; set; }
    public ICollection<SensorPlacement> SensorPlacement{ get; set; }
}

public partial class Room
{
    public Sensor()
    {
        SensorPlacement = new HashSet<SensorPlacement>();
    }

    public int Id { get; set; }
    public int FloorId { get; set; }
    public string Name { get; set; }

    public Floor Floor { get; set; }
    public ICollection<SensorPlacement> SensorPlacement{ get; set; }
}

public partial class Floor
{
    public Floor()
    {
        Room = new HashSet<Room>();
    }

    public int Id { get; set; }
    public int BuildingId { get; set; }
    public string Name { get; set; }

    public Building Building { get; set; }
    public ICollection<Room> Room{ get; set; }
}

public partial class Building
{
    public Building()
    {
        Floor = new HashSet<Floor>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Floor> Floor{ get; set; }
}

public partial class SensorPlacement
{
    public int Id { get; set; }
    public int SensorId { get; set; }
    public int RoomId { get; set; }
    public DateTime From { get; set; }
    public DateTime To { get; set; }

    public Sensor Sensor { get; set; }
    public Room Room { get; set; }
}

Let me explain a bit. The main data being inserted to the db is SensorEvent. SensorEvent is essentially a list of SensorData objects sent from a sensor, along with which sensor sent it and when it was recorded. So far pretty simple, if I want to present all the SensorEvents and which sensor they came from, I can return the following domain objects

public class DomainSensorEvent
{
    public int Id { get; set; }
    public int SensorName { get; set; }
    public int RecordTime { get; set; }

    public IList<DomainSensorData> SensorData{ get; set; }
}

public partial class DomainSensorData
{
    public int Id { get; set; }
    public string DataType { get; set; }
    public string Description { get; set; }
}

And I can get it like this (Ignoring the Where, Take, OrderBy, and Skip parts for the sake of simplicity).

public List<DomainSensorEvent> GetDomainSensorEvents()
{
    return DbContext.SensorEvent.Select(dse => new DomainSensorEvent
    {
        Id = dse.Id,
        SensorName = dse.Sensor.Name,
        RecordTime = dse.RecordTime,
        SensorData = dse.SensorData.Select(sd => new DomainSensorData
        {
            Id = sd.Id,
            DataType = sd.DataType,
            Description = sd.Description
        }).ToList()
    }).ToList();
}

Pretty straight forward so far, the problem arises when I want to include where a sensor was placed (Which Room, which Floor and Building) when this data was recorded. See, I don't have this information at insert time. The sensor can be moved around, so the SensorPlacement table might be updated after a SensorEvent from that sensor in that time interval has been inserted. Meaning instead of linking a SensorEvents to both a Room and Sensor, the Room is resolved at query time. Yes, this means a query could return the wrong Room, if SensorPlacement has not been updated, but it will eventually be correct. Complicating matters further is of course that a SensorEvent might not have occured in a Room, so I also need to check for null.

The new domain model and query (the part I need help with) looks like this.

public class DomainSensorEvent
{
    public int Id { get; set; }
    public int SensorName { get; set; }
    public int Room { get; set; }
    public int Floor { get; set; }
    public int Building { get; set; }
    public int RecordTime { get; set; }

    public IList<DomainSensorData> SensorData{ get; set; }
}


public List<DomainSensorEvent> GetDomainSensorEvents()
{
    return DbContext.SensorEvent.Select(dse => new DomainSensorEvent
    {
        Id = dse.Id,
        SensorName = dse.Sensor.Name,
        Room = dse.Sensor.SensorPlacement.Where(sp => dse.RecordTime > sp.From && (sp.To == null || dse.RecordTime < sp.To)).FirstOrDefault() != null ? 
               dse.Sensor.SensorPlacement.Where(sp => dse.RecordTime > sp.From && (sp.To == null || dse.RecordTime < sp.To)).FirstOrDefault().Room.Name : null,

        Floor = dse.Sensor.SensorPlacement.Where(sp => dse.RecordTime > sp.From && (sp.To == null || dse.RecordTime < sp.To)).FirstOrDefault() != null ? 
                dse.Sensor.SensorPlacement.Where(sp => dse.RecordTime > sp.From && (sp.To == null || dse.RecordTime < sp.To)).FirstOrDefault().Room.Floor.Name : null,

        Building = dse.Sensor.SensorPlacement.Where(sp => dse.RecordTime > sp.From && (sp.To == null || dse.RecordTime < sp.To)).FirstOrDefault() != null ? 
                   dse.Sensor.SensorPlacement.Where(sp => dse.RecordTime > sp.From && (sp.To == null || dse.RecordTime < sp.To)).FirstOrDefault().Room.Floor.Building.Name : null,
        RecordTime = dse.RecordTime,
        SensorData = dse.SensorData.Select(sd => new DomainSensorData
        {
            Id = sd.Id,
            DataType = sd.DataType,
            Description = sd.Description
        }).ToList()
    }).ToList();
}

The most obvious problem is of course the same statement repeated 6 times (making an already slow query even worse). I have tried to solve it by storing it as a temporary variable, but apparently you can only have a single statement in a Select query that is translated to SQL. I have tried running an extra Select statment before this one, returning an anonomous type with SensorEvent and a corresponding SensorPlacement, but this breaks navigation properties. Using Joins also makes using navigation properties difficult.

Am I looking at this from the wrong angle? Is there some syntax I am missing, or do I need to do this another way? I know the best solution would be to be able to know SensorPlacement at insert time, but that is not currently possible.

1
0
7/22/2019 1:18:35 PM

Popular Answer

You have a few questions here, so I don't know that this can really be answered but a few quick thoughts:

Use your navigation properties sooner, and put your where clause on the initial select:

public List<DomainSensorEvent> GetDomainSensorEvents()
{
    return DbContext.SensorEvent
    .Include("Sensor")
    .Include("SensorData")
    .Include("Sensor.SensorPlacement")
    .Where(w => w.Sensor.SensorPlacement.Where(sp => w.RecordTime > sp.From 
        && (sp.To == null || w.RecordTime < sp.To)))
    .ToList();        
}

This should (assuming the fks and Navigation properties are actually correct) give you a list of your Entities with your List navigations that are in the .Includes, that match on the where Clause (I don't think you'd want it as is though this way).

I'd then look at AutoMapper or something similar to convert it to your domain / view models.

 var results = GetDomainSensorEvents();
 Mapper.Map<DomainSensorEvent>(results);

Since you know some properties and properties of properties are null in the database, you can handle them in the mapping or even the .AfterMap() as required.

I'd also reconsider your naming strategy. DomainEntityName is descriptive, but it sort of ruins Intellisense when every class starts with the same word. It also reminds me of why you don't want to use Hungarian Notation.

On the Where Clause: This should work for One to One hierarchical properties (Navigation properties with one child:

    .Where(w => w.Sensor.SensorPlacement.Where(sp => w.RecordTime > sp.From 
    && (sp.To == null || w.RecordTime < sp.To)))

But for getting the parent with a one to many you'd need slightly different logic:

    .Where(w => w.Sensor.SensorPlacement.Where(sp => sp.Any(w.RecordTime > sp.From 
    && (sp.To == null || w.RecordTime < sp.To))))

This should get you any parent where the included List (In your case List ) has Any matching properties.

Lastly, this was a shot at reducing code I obviously can't reproduce and run locally. Hopefully this helps but it isn't going to be the exact solution.

0
7/23/2019 11:53:15 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow