How to update all rows of a table with an aggregate value referenced by foreign key on an another table in Entity Framework

Question

I have a users table and my User entity has a Rating column. A User has a navigation property called IncomingRatings, which is a one-to-many relationship of list of Rates from a table called rates. A rating can be positive or negative, and is represented by the IsPositive boolean.

I want to find all the ratings of that user, and store it in the Rating column of my User table. Consider the following sample data:

RaterID | RatedID | IsPositive
1         2         1
4         1         0
5         2         1
2         3         0
3         2         1
7         2         0
6         1         1

In this case, for example, I want my user #2's (referenced by ID 2) Rating field to become 3, as there are 3 positive ratings for her.

After the updates, the users table should be like this:

UserID | Rating
1        1
2        3
3        0
4        0
5        0
6        0
7        0

Because I need this statement to run for 10000+ records, I can't use regular for loop in my ASP.NET website to update the records as it will save them one-by-one when I call SaveChanges. I've found EntityFramework.Extended which can update many records in a single call. Here is my two approaches:

db.Users.Where(u => true).Update(u => new User { Rating = u.IncomingRatings.Count(r => r.IsPositive) });
db.Users.Where(u => true).Update(u => new User { Rating = db.Rates.Where(r => r.RatedID == u.ID && r.IsPositive).Count() });

However, I am getting An aggregate may not appear in the set list of an UPDATE statement. error in both queries. How can I update all rows with an aggregate value in Entity Framework 6.1?

score 1 · Accepted Answer · answered May 31 '14 at 22:29

Looks like EntityFramework.Extended is only capable of building simple UPDATE statement like

UPDATE User SET Rating = 3 WHERE ...

and not (or not yet)

UPDATE User SET Rating = (SELECT Count(*) FROM ... )

Too bad...

If doing the updates by regular EF updates, but with one SaveChanges() call, is too slow you could consider executing raw SQL to do the job.

How to update all rows of a table with an aggregate value referenced by foreign key on an another table in Entity Framework

1 Answers1