CODEWITHSUNDEEP
c#entity-framework
Alternatex
Alternatex

Reputation: 1552

Entity framework property computed from entries in another table

Let's say I have a movie renting application (DVD, Blueray or some other physical media) and after a user has returned the movie they can rate it. This is a simplified version of the tables:

Ratings
--------------------------------------------
| Id | UserId | MovieId | Rating (1 to 10) |
--------------------------------------------

Movies
------------------------------------------
| Id | Name | Computed rating (1 to 100) |
------------------------------------------

Is there a way to configure Entity Framework to calculate a movie's rating using the entries in the Ratings table? Because having a Rating field in the Movies table would be a bit redundant and I was wondering whether Entity framework can handle it for me without me having to do it every time I load the Movies.

I know nHibernate can associate formulas with properties.

Upvotes: 2

Views: 1833

Answers (1)

Keith Payne
Keith Payne

Reputation: 3082

Computing an aggregate value would be accomplished with an SQL trigger.

To create the trigger, you'll need to override the Seed method in an initializer (CreateDatabaseIfNotExists seems to fit the bill in your case).

For example, given the following models -

public class Movie
{
    public string Name { get; set; }
    public int SumOfAllRatings { get; set; }

    public virtual Collection<Rating> Ratings { get; set; }
}

public class Rating
{
    public virtual Movie Movie { get; set; }
    public int Rating { get; set; }

}

and DbContext -

public class MyDbContext : DbContext
{
    public MyDbContext() : base()
    {
        Database.SetInitializer<MyDbContext>(new MyInitializer());
    }

    public DbSet<Movie> Movies { get; set; }
    public DbSet<Rating> Ratings { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Movie>().Property(m => m.SumOfAllRatings)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
    }
}

This is the initializer that creates an appropriate trigger -

public class MyInitializer : CreateDatabaseIfNotExists<MyDbContext>
{
  string _sql = "create trigger T_Ratings_Change " +
       "on dbo.Ratings after insert, update as " +
       "update Movies set SumOfAllRatings = " +
       "(select SUM(Rating) from Ratings where MovieId = inserted.MovieId) " +
       "from Movies inner join inserted ON Movies.Id = inserted.MovieId";


  protected override void Seed(MyContext context)
  {
    context.Database.ExecuteSqlCommand(_sql);
  }
}

Upvotes: 2

Related Questions