c# - EF Core: How to best get average value in a model of a related model

Question

I've got a Blazor Server App using the Entity Framework (EF Core).

I use a code first approach with two models, Entry and Target.

Each entry has a target. So a target can have more than one entry pointing to it.

The model for the Target looks like this:

public class Target
  {
    public string TargetId { get; set; }
    
    [Required]
    public string Name { get; set; }

    [InverseProperty("Target")]
    public List<Entry> Entries { get; set; }

    [NotMapped]
    public double AverageEntryRating => Entries != null ? Entries.Where(e => e.Rating > 0).Select(e => e.Rating).Average() : 0;
  }

An entry can have a rating, the model Entry looks like this:

public class Entry
  {
    public string EntryId { get; set; }
    
    public int Rating { get; set; }
    
    [Required]
    public string TargetId {get; set; }

    [ForeignKey("TargetId")]
    public Target Target { get; set; }    
  }

As you can see in my Target model, I would like to know for each Target, what the average rating for it is, based on the average of all entries that point to the Target - that's why there is this (NotMapped) property in the target:

public double AverageEntryRating => Entries != null ? Entries.Where(e => e.Rating > 0).Select(e => e.Rating).Average() : 0;

But this does (of course) not always work, as the Entries of the target are not guaranteed to be loaded at the time the property is accessed.

I tried to solve it differently, for example to have a method in my TargetService, where I can pass in a targetId and gives me the result:

 public double GetTargetMedianEntryRating(string targetId) {
        var median = _context.Entries
            .Where(e => e.TargetId == targetId && e.Rating > 0)
            .Select(e => e.Rating)
            .DefaultIfEmpty()
            .Average();
        return median;
    }

But when I list out my targets in a table and then in a cell want to display this value (passing in the current targetId of the foreach loop) I get a concurrency exception, as the database context is used in multiple threads (I guess one from looping through the rows/targets and one other from getting the average value)... so this leads me into new troubles.

Personally I would prefer to work with the AverageEntryRating property on the Target model, as it seems natural to me and it would also be convenient to access the value just like this.

But how would I make sure, that the entries are loaded, when I access this property. Or is this not a good approach because this would mean I would have to load Entries anyway for all the targets which would lead to performance degradation? If yes, what would be a good way to get to the average/median value?

Answer 1

There are a couple of options I could think of, and it depends on you situation what to do. There might be more alternatives, but at least I hope that this can give you some options you hadn't considered.

Have a BaseQuery extension method that always include all Entries

You could make sure of doing .Include(x => x.Entries) whenever you are querying for Target. You can even create an extension method of the database context called something like TargetBaseQuery() that includes all necessary relationship whenever you use it. Then you will be sure that the Entries List of each Target will be loaded when you access the property AverageEntryRating.

The downside will be a performance hit, since every time you load a Target you will need to load all its entries... and that's for every Target you query. However, if you need to get it working fast, this would be probably the easiest. The pragmatic approach would be to do this, measure the performance hit, and if it is too slow then try something else, instead of doing premature optimization. The risk of course would be that it might work fast now, but it might scale badly in the future. So it's up to you to decide.

Another thing to consider would be to not Include the Entries every single time, but only in those places where you know you need the average. It might however become a maintainability issue.

Have a different model and service method to calculate the TargetStats

You could create another class Model that stores the related data of a Target, but it's not persisted in the database. For example:

public class TargetStats
  {    
    public Target Target { get; set; }

    public double AverageEntryRating { get; set; }
  }

Then in your service you could have a method ish like this (haven't tested, so it might not work as is, but you get the idea):

public List<TargetStats> GetTargetStats() {
        var targetStats = _context.Target
            .Include(x => x.Entries)
            .Select(x => new TargetStats 
            {
              Target = x, 
              AverageEntryRatings = x.Entries.Where(e => e.Rating > 0).Select(e => e.Rating).Average(),
            })
            .ToList()

        return targetStats;
    }

The only advantage of this is that you don't have to degrade the performance of all Target related queries, but only of those that requires the average rating. But this query in particular might still be slow. What you could do to further tweak it, is write raw SQL instead of LINQ or have a view in the database that you can query.

Store and update the Target's average rating as a column

Probably the best you could do to keep the code clean and have good performance while reading, is to store the average as a column in the Target table. This will move the performance cost of the calculation to the saving/updating of a Target or its related Entries, but the readings will be super fast since the data is already available. If the readings happen way more often than the updates, then it's probably worth doing it.

You could take a look at EF Core docs on perfomance, since they talk a little bit about the different perfomance tunning alternatives.