In Entity Framework can I create a computed column that calculates all the values from every row?

Question

I have 3 main columns (Points, TotalPoints, DateCreated) in a table using Entity Framework.

Scenario - Think of the "Fun Arcade" where you earn tokens for playing games and can then exchange those tokens for gifts. So your "Points" count or token count will go up and down as you use the application.

What I want - I'd like to see if Entity Framework can calculate the "TotalPoints" column, so that I don't have to do it every time a new "Point" entity is added to the table.

I know I can create a calculated column value that computes values from the same row (ex. first name col + last name col => full name column) But I want to sum up all values from the "Points" column for that user, so that the last row inserted (based on the DateCreated column) has the sum of all the points for that user.

ex. Point entity

points 1, totalPoints 1, dateCreated

points 3, totalPoints 4, dateCreated

points 2, totalPoints 6, dateCreated

points -1, totalPoints 5, dateCreated

points 2, totalPoints 7, dateCreated

FYI - I can also remove points, as seen above, if the user exchanges them for a gift.

QUESTION - Is this possible with fluent API and EF Core or do I have to manually calculate it every time I insert a new Point entry?

EDIT - I can do it manually and it seems to work ok. But I only have a few rows so far, so not sure about the performance with a few thousand rows for each user?

Here is what I have done.

When inserting a new "Point" entity I can calculate the sum of the users "Points" like this.

var totalPoints = userFromRepo.UserPoints.Sum(p => p.Points);
var point = new UserPoint() { Points = 1, TotalPoints = totalPoints+1, UserId = userFromRepo.Id, DateCreated = DateTime.UtcNow, UserPointType = UserPointType.Tip };
_unitOfWork.Repository<UserPoint>().Add(point);

and when I fetch the user data on log in, including the points, I do fetch based on the last "Point" inserted. This saves any time of doing some type of .SUM() or calculation each time a user logs in.

Ex. of getting users total points on log in

user.UserPoints.OrderByDescending(p => p.CreatedDate).LastOrDefault().TotalPoints)

Answer 1

From a DDD point of view you should consider User as your aggregate root which means that the user is responsible for the validity of all data it contains. Therefore I would suggest you not to add UserPoints directly to the database through a repository but instead add it through the User (it has a reference to them already anyway).

It could look somewhat like this:

public class User
{
    private readonly List<UserPoint> _userPoints = new List<UserPoint>();

    ...

    public int Id { get; private set; }

    public int TotalPoints { get; private set; }

    public IReadOnlyCollection UserPoints => _userPoints;

    public void AddPoints(int amount, UserPointType pointType)
    {
         TotalPoints = TotalPoints + amount;

         var point = new UserPoint() 
         { 
              Points = amount, 
              TotalPoints = this.TotalPoints,
              UserId = this.Id, 
              DateCreated = DateTime.UtcNow, 
              UserPointType = pointType 
         };

         _userPoints.Add(point);      
    }
}

Doing it this way you can have a single integer on the user object which always represents the current amount of points a user has and still have a history of all "Transactions" of points ever made for this user in case you need to display them in detail somewhere else. This will be very efficient to query if you just need the TotalPoints a user has at the moment and because you are only adding points for a user through the AddPoints method the property cannot get out of sync.

Another (and in my opinion even more important) advantage of this is, that you can have the user do some additional checks in the future e.g. have the User ensure that no amount of points may be added that leads to TotalPoints being less than 0 (because a user cannot spend points which he does't have).

When an application gets larger and there a multiple places in your code where points are added to a user (e.g. when he gains points and somewhere different place when he spends his points) it is very important to have a single method for doing so on the user directly which performs all the checks. Otherwise you will propably end up duplicating a lot of code and potentially having problems changing the behaviour at all neccessary places when an additional rule shall be added in the future.

Answer 2

Not quite sure why you're getting the DB to do the running sum, or storing it, as it can be easily calculated every time the data is displayed if you have some other needed info like "the date the points we're earned".. unless you have some use case where only data after some moment is displayed..

For the simple use case, all data is displayed:

var ps = context.Points.Where(p => p.UserId == blah).OrderBy(...)
var sum = 0;
foreach(var p in ps)
  Console.WriteLine($"{p.Points} {sum+=p.Points}");

For the partial use case, some data after date X is displayed and points from before seed the rolling sum:

var ps = context.Points.Where(p => p.UserId == blah && p.SomeDate >= blah2).OrderBy(...)
var sum = context.Points.Where(p => p.UserId == blah && p.SomeDate < blah2).Sum(p => p.Points);

foreach(var p in ps)
  Console.WriteLine($"{p.Points} {sum+=p.Points}");

If you want to use a view, perhaps:

CREATE VIEW PointsWithSum AS
  SELECT UserId, Points, SUM(Points) OVER(PARTITION BY UserId ORDER BY SomeDate)

Scaffold that and you'll get a read only db set/entity but you can wire it into other entities by adding nav properties to it so you can query like

context.PointsWithSum.Include(pws => pws.User)
  .Where(pws => pws.UserId == 123)

Or similar..

Note; code examples based on assumption you have a table Points (an integer) with columns UserId, Points and SomeDate, which EF mapped to an entity called Point with properties of User, Points and SomeDate

Answer 3

It should be possible using scalar valued user defined function.

configuration:

entity.Property(p => p.TotalScore)
    .HasComputedColumnSql("dbo.CalculateUserScore([Id])");

When it comes to performance, it for sure will be worse because it's hard to calculate query plan for function.

IMO, you should calculate this in your app code.

Answer 4

Okay you're using this:

user.UserPoints.OrderByDescending(p => p.CreatedDate).LastOrDefault().TotalPoints)

and it should really be this if you're auto incrementing row id:

user.UserPoints.LastOrDefault().TotalPoints

which you want to turn into an auto calculated column? WHY??? It's too much writing? You're having performance issues? There's no reasoning here... So you want it to look like this instead?:

user.UserPoints.TotalPoints

That's all you want??? Just make an extension method. It's not clear what you're trying to do.

Ok so maybe you are worried about pulling the last value of TotalPoints in the creation of the new row? Don't be, it's already loaded in memory.

QUESTION - Is this possible with fluent API and EF Core or do I have to manually calculate it every time I insert a new Point entry?

You're just making a ledger. New row. Points added, or negative points added, then add/remove the same number from TotalPoints each time. Grab the current value of TotalPoints and modify that for the new row. That's totally normal. Then add the row. Save. You're not really calculating here. You're just adding and removing rows from a ledger with a total balance, then returning the most recent balance.

You should not hit any performance issues at all doing this ever.

I'm not sure what you want, but I can write the extension methods for you to help make it so you write as little as possible?

Answer 5

I suggest using the not so explored Aggregate capability of Entity framework, like for instance in Your DbContext where i presume Your userId is a Guid

public UserPoint InsertPointsCount(Guid UserId, int points)
{
    var pointsSum =  Points
                       .Where(x => x.UserId == userId)
                       .Aggregate<UserPoint, int, int>(
                                    points,
                                    (sum, p) => sum + p.Points, 
                                    (sum) => sum
                                );
    return Points.Add(new UserPoint
    {
        Points = pointsSum,
        UserId = UserId,
        UserPointType = UserPointType.Tip
    }).Entity;
}

Answer 6

var totalPoints = userFromRepo.UserPoints.LastOrDefault();
var point = new UserPoint() { Points = 1, TotalPoints = totalPoints+1, UserId = userFromRepo.Id, DateCreated = DateTime.UtcNow, UserPointType = UserPointType.Tip };
_unitOfWork.Repository<UserPoint>().Add(point);