Entity Framework - updating sum of related entity collection property values on parent entity

Question

How can I effectively update the parent EntityObject property which is there to expose only the sum of the related child entity collection property? I need an update in parent any time there is a change of property value in any of the child entity.

An example: I have a parent EntityObject "Company" and a collection of related child objects "Employee". These have association in EF between each other (one Company to collection of Employees). In Employee partial class I have added a custom calculated property "Salary" and in a Company partial class I have added a custom property "TotalSalaries".

Now, if any Employee Salary property is updated to a new value, I want to immediately update the Company object property TotalSalaries value.

Whenever the Employee property changes and if then I always run a full query inside the Company object like:

TotalSalaries = Me.Employees.Sum(Function(x) x.Salary)

...that looks like a highly inefficient thing to do, especially if all custom property values in Employee class are changed by looping for example (the above query is run over and over again).

Can the property update be reflected in the parent class more efficiently?

It may look inefficient, but in reality it may be lightning fast if everything runs in memory. I would try to optimize this only when it unacceptably delays whatever it affects. — Gert Arnold, May 29 '13 at 18:59
Ok, it may be fast but think it could be made even faster: Let's say I have 100 000 employees and one of the employee has a salary increase of 10% (original value 1000 -> 1100 for example). Can I somehow only pass this chage (+100) to the parent object and tell the object to "add this increase to your TotalSalaries" instead of running the query over 100 000 items where 99 999 are unchanged. Is this possible? — Nuts, May 30 '13 at 07:21
Second scenario could be that all of those 100 000 employees have simultaneous salary increase of 10%. Now this query is run over 100 000 times over all employees if it activates on property change -> but what we would need to do is only run it once after all individual salary values have been updated. How to do that? — Nuts, May 30 '13 at 07:23
Don't update `TotalSalaries`. Make it a calculated property (which I though it was in the first place). So it is only calculated when needed for display. That can be really fast. If necessary you can store and display the calculated value and set a boolean that recalculation is necessary when a salary has changed. — Gert Arnold, May 30 '13 at 07:44

score 0 · Accepted Answer · answered Jun 03 '13 at 10:59

I figured this out. On the Employee class, I can capture the original property value in a class-level PropertyChanging event:

Private Sub employee_PropertyChanging(ByVal sender As Object, ByVal e As PropertyChangingEventArgs) Handles Me.PropertyChanging    
    Dim propBeignChanged As String = e.PropertyName
    If propBeignChanged = "Salary" Then
        OriginalValue = CType(sender, Employee).Salary  'store the current value temporarily to a variable
    End If
End Sub

And then I can get the new value either in a class-level PropertyChanged event or the property-specific On[Property]Changed event, calculate the difference against the temporarily stored original value and pass the difference to the parent object.

Private Sub OnSalaryChanged()
    Dim diff as Double = Me.Salary - OriginalValue 
    'and finally pass diff to the parent object for updating its total...
End Sub

I believe this must be a much faster approach than querying the whole EntityCollection.

Entity Framework - updating sum of related entity collection property values on parent entity

1 Answers1