ICollection in domain model performance

Question

I have following database context which I use with Entitry Framework

public class MainContext: DbContext
{
    public MainContext()
        : base("name=MainContext")
    { }

    public virtual DbSet<Device> Devices { get; set; }
    public virtual DbSet<Point> Points { get; set; }
}

With following Domain model

public class Point
{
    [Key]
    public int Id { get; set; }

    public string Info { get; set; }
    public DateTime FixTime { get; set; }

    public int DeviceId { get; set; }
    public virtual Device Device { get; set; }
}

public class Device
{
    [Key]
    public int Id { get; set; }

    public int SomeValue { get; set; }

    public virtual ICollection<Point> Points { get; set; }

    public bool IsActive()
    {
        Point lastPoint = Points.LastOrDefault();
        if (lastPoint == null) 
        { 
            return false; 
        }
        else
        {
            var diff = DateTime.Now - lastPoint.FixTime;

            if (diff.TotalSeconds > 10 )
            {
                return false;
            } 
            else
            {
                return true;
            }
        }
    }
}

I'm facing huge performance problem calling IsActive() method in the Device class. As far as I can see that it because of calling Points.LastOrDefault() queries all available database records for the Device instead of the only one. I understand that it's because of using ICollection in my class but that is Entity Framework demand. Is there's any way to query the only record in such situation or it's just me putting the method in a wrong place?

score 1 · Accepted Answer · edited May 23 '17 at 12:22

Is there's any way to query the only record in such situation or it's just me putting the method in a wrong place?

If you ask me, the latter. You know better than Entity Framework what exactly you want to query: as soon as you access the lazy-loaded navigation collection property Points, it'll load the entire collection for that device.

Also, LastOrDefault() makes little sense in a database environment where reversing a sort is relatively cheap.

Also, since you specify no order, the order isn't guaranteed, so this code is guaranteed to break some day (LastOrDefault() returning a different record).

That being said, I don't like entity models that execute queries, at least not in the case of an ORM and especially Entity Framework, so I'd move that logic into a separate class. Call it a PointGetter or give it a name.

In there, you can do the query:

public class PointGetter
{
    public Point GetLastPoint(DbContext dbContext, Device device)
    {
        var lastPointForDevice = dbContext.Points
                                          .Where(p => p.Device == device)
                                          .OrderByDescending(p => p.FixTime)
                                          .FirstOrDefault();
        return lastPointForDevice;
    }
}

Well to solve my problem, finally I've made something like this but I feel that it's not the best way it should be done. — hitry, Jun 02 '16 at 12:24

score 0 · Answer 2 · answered Jun 02 '16 at 12:03

0

Why don't you try an .OrderBy or OrderByDescending and then FirstOrDefault. Provided that you have the appropriate indices in your DB this should be quick enough and only bring you back one record.

answered Jun 02 '16 at 12:03

kagelos

423
8
19

That was the first what I did, used OrderByDescending and FirstOrDefault. Also I've tryed to use Take(1) before calling FirstOrDefault. Query that EF generates anyway calls for all records/ – hitry Jun 02 '16 at 12:18

ICollection in domain model performance

2 Answers2