CODEWITHSUNDEEP
c#.net-coreentity-framework-core
tzman
tzman

Reputation: 174

How to use HasComputedColumnSql across relationship in Entity Framework Core?

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?

Upvotes: 2

Views: 1295

Answers (1)

Nouman
Nouman

Reputation: 619

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);
  }
}

Upvotes: 2

Related Questions