How to update a total count on parent entity when adding a new child collection entity?

I have UserPoints which is a collection on User which keeps track of all points awarded to a User. When I add a new UserPoint to User I want to automatically update TotalPoints on User, based on how many points are awarded (ex. 3, 5, 1, 6, etc).

QUESTION - Is it possible to update TotalPoints without modifying the value directly on User when I add a new UserPoint row?

Ex. TotalPoints on User should now be 4

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

var userPoint = new UserPoint {
    Points = 1,
    UserPointType = UserPointType.Priority,
    UserId = user.Id,
};
_unitOfWork.Repository<UserPoint>().Add(userPoint);

var userPoint = new UserPoint {
    Points = 3,
    UserPointType = UserPointType.Tip,
    UserId = user.Id,
};
_unitOfWork.Repository<UserPoint>().Add(userPoint);

// save 2 new child entities in db
await _unitOfWork.Complete();

<!-- end snippet -->

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

public class User : IdentityUser<int>
{
    public int TotalPoints { get; set; } // should be 4
    public ICollection<UserPoint> UserPoints { get; set; }
}

public class UserPoint : BaseEntity
{
    public UserPointType UserPointType { get; set; }
    public int Points { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
}

<!-- end snippet -->

If the User table has a regular TotalPoints field then this is a form of a denormalization in terms of the data structure. From a database perspective there is no way to enforce that TotalPoints reflects the sum of Points for UserPoints records as UserPoints may be added, removed, or altered.

One option for schema-first entity configurations would be to use a computed column for the TotalPoints with a Scalar function:

CREATE FUNCTION getTotalPointsForUser 
(
	@userId int
)
RETURNS int
AS
BEGIN
	DECLARE @sum INT

	SELECT @sum = SUM(Points) FROM dbo.UserPoints WHERE UserId = @userId

	RETURN @sum

END
GO

Then in the User table we declare TotalPoints as a Computed column and point it at the getTotalPointsForUser function:

TotalPoints AS dbo.getTotalPointsForUser(UserId)

Now in the entity for the User, we mark TotalPoints as a Computed column. This tells EF to expect a value to calculated on read:

public class User
{
    // ...

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public int TotalPoints { get; protected set; }
}

One consideration with this approach is that it can incur a performance cost as this computed column will be calculated each time the row is read. So depending on the total # of users and/or user point records this could start getting expensive to run.

Other options you can consider:

Computed values like this are often a presentation concern. Rather than relying on the data domain to report this calculated value, this could instead be done at read where necessary. For example if I have a UserViewModel where I want to just display some info from the User record and a sum of the total points for that user:

[Serializable]
public class UserViewModel
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public int TotalPoints { get; set; }
}

Now when I read my User(s):

var users = _context.Users
    .Select(x => new UserViewModel
    {
        UserId = x.UserId,
        Name = x.FirstName + " " + x.LastName,
        TotalPoints = x.UserPoints.Sum(up => up.Points)
    }).ToList();

This mapping can be configured in Automapper and be simplified to:

var users = _context.Users
    .ProjectTo<UserViewModel>(config).ToList();

... where "config" is a MapperConfiguration explaining to map the Name (if combined like the example) and TotalPoints from the UserPoints.

This option leaves the Total Points aspect of users and user points as a normalized option where the "flattening" only occurs when we want to pull the data in a way the view can consume.

The last option that can help guard de-normalization can work if your application is the only thing that should be touching the database. This uses a more DDD approach to regulate how user points are modified to ensure that changes are only done through the User.

This would introduce methods like:

public UserPoint AddPoints(int points)
{
     if (points < 0) throw new ArgumentOutOfRangeException(nameof(points));

     var userPoint = new UserPoint { Points = points };
}

public void RemoveUserPoints(int userPointId)
{
     var userPoint = UserPoints.Single(x => x.UserPointId == userPointId);
     TotalPoints -= userPoint.Points;
     UserPoints.Remove(userPoint);
}

public void ReplaceUserPoints(int userPointId, int points);
{
     if (points < 0) throw new ArgumentOutOfRangeException(nameof(points));

     var userPoint = UserPoints.Single(x => x.UserPointId == userPointId);
     TotalPoints -= userPoint.Points;
     userPoint.Points = points;
     TotalPoints += points;
}

... to the User entity, then guarding the "Points" value on the UserPoint entity with an internal setter to deter code altering UserPoint values and directing code to call User.AddPoints / RemoveUserPoints / ReplaceUserPoints:

This would also involve a unit of work to scope the DbContext and ensure that SaveChanges() is called after calling the action against the user to alter the points, and handle any exceptions raised.

This would be my least recommended option given it is the most work and does not guarantee that the de-normalized data doesn't still end up getting out of sync.

In any case that should give you some ideas on options to tackle that kind of problem.