How to use HasComputedColumnSql across relationship in Entity Framework Core?

user_5475610 0
Viewed 360

I am trying to use computed properties to calculate an aggregated value in below example

public class Team 
{
    public string Name { get; set; }
    public List<Member> Members { get; set; }
    public double Contribution { get; set; }  // team's aggregated contribution in hours
}

public class Member 
{
    public string Name { get; set; }
    public Team Team { get; set; }
    public double IndividualContribution { get; set; } // member's contribution in hours
}

public class TeamContext : DBContext 
{
    public DbSet<Team> Teams { get; set; }
    public DbSet<Member> Members { get; set; }

    protected override void OnModelCreating(ModelBuilder Builder)  
    {
        Builder.Entity<Member>().HasOne(_=>_.Team).WithMany(_=>_.Members);
        Builder.Entity<Team>().Property(_=>_.Contribution).HasComputedColumnSql("SELECT SUM([M].[IndividualContribution]) FROM [Member] AS [M] WHERE [M].[TeamId] = [Id]");
    }
}

But when I try to apply the migration, I get this error:

ALTER TABLE [Member] ADD [Contribution] AS SELECT SUM([M].[IndividualContribution]) FROM [Member] AS [M] WHERE [M].[TeamId] = [Id];

Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'SELECT'.

What is the correct way to do this?

entity-framework-core .net-core c#
1 Answers

HasComputedColumnSql can't be configured using SELECT queries, or using other entities. You may use entity function such as following to update team contribution and call it when you want to update team contribution.

public class Team {
  public string Name { get; set; }
  public List < Member > Members { get; set; }
  public double Contribution { get; set; } // team's aggregated contribution in hours

  public void UpdateTeamContribution() {
    Contribution = Members.Sum(m =>m.IndividualContribution);
  }
}
user_8995270
Related