Configuration of entities to save summed values in database

Question

Hi,
I have two entity classes (Order, OrderItem) and one Value Object class (Money).

Simplified code:

public class Order  
{  
    private Order()  
    {  
    }  
  
    public int OrderId { get; private set; }  
      
    public List<OrderItem> OrderItems = new ();  
	  
    public Money TotalGrossAmount  
    {  
        get => new Money(OrderItems.Sum(x => x.TotalNetAmount.Amount), "USD");  
        private set { }  
    }  
      
    // ...rest of code  
}  
  
public class OrderItem  
{  
    private OrderItem()  
    {  
    }  
	  
    public int OrderItemId { get; private set; }  
    public Money TotalNetAmount { get; private set; }  
      
    // ...rest of code  
}  
  
public class Money  
{  
    public Money(decimal amount, string currency)  
    {  
        Amount = amount;  
        Currency = currency;  
    }  
  
    public decimal Amount { get; private set; }  
    public string Currency { get; private set; }  
}  

For efficiency purposes, I'd like to have the summed amount of the order items in the database in the Order table.

The Order entity is configured like this:

public class OrderConfiguration : IEntityTypeConfiguration<Order>  
{  
    public void Configure(EntityTypeBuilder<Order> builder)  
    {  
        builder.ToTable(nameof(Order), DbSchemas.Default);  
        builder.HasKey(x => x.OrderId);  
  
        // ...rest of code  
          
        builder.OwnsOne(x => x.TotalGrossAmount, navigationBuilder =>  
        {  
            navigationBuilder.Property(p => p.Amount);  
            navigationBuilder.Property(p => p.Currency);  
        });  
    }  
}  

Migration creates the correct table structure with columns: TotalGrossAmount_Amount, TotalGrossAmount_Currency

But for this configuration, the application throws an exception:

The value of 'Order.TotalGrossAmount#Money.OrderId' is unknown when attempting to save changes. This is because the property is also part of a foreign key for which the principal entity in the relationship is not known.

How should I configure Order entities correctly?

Answer 1

@lukaszjazwiec ,

I create a simple demo as below, which works on my side, entity framework version is ef core 6.0.

namespace EFGetStarted  
{  
    public class BloggingContext : DbContext  
    {  
        public DbSet<Order> Orders { get; set; }  
        public DbSet<OrderItem> OrderItems { get; set; }  
  
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
        {  
            optionsBuilder.UseSqlServer(  
                @"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True");  
        }  
        protected override void OnModelCreating(ModelBuilder modelBuilder)  
        {  
            modelBuilder.ApplyConfigurationsFromAssembly(typeof(OrderConfiguration).Assembly);  
        }  
    }  
  
    public class Order  
    {  
        public int OrderId { get; private set; }  
  
        public List<OrderItem> OrderItems = new();  
  
         
        public Money TotalGrossAmount  
        {  
            get => new Money(OrderItems.Sum(x => x.TotalNetAmount.Amount), "USD");  
            private set { }  
        }  
    }  
  
    public class OrderItem  
    {  
        private OrderItem()  
        {  
            //TotalNetAmount = 0m;  
        }  
  
        public int OrderItemId { get; private set; }  
        public Money TotalNetAmount { get; private set; }  
  
          
    }  
  
    public class OrderConfiguration : IEntityTypeConfiguration<Order>  
    {  
        public void Configure(EntityTypeBuilder<Order> builder)  
        {  
            builder.ToTable(nameof(Order), "dbo");  
            builder.HasKey(x => x.OrderId);  
  
            // ...rest of code  
  
            builder.OwnsOne(x => x.TotalGrossAmount, navigationBuilder =>  
            {  
                navigationBuilder.Property(p => p.Amount).IsRequired();  
                navigationBuilder.Property(p => p.Currency).IsRequired();  
            });  
  
            builder.Navigation(o => o.TotalGrossAmount)  
            .IsRequired();  
        }  
    }  
  
    public class OrderItemConfiguration : IEntityTypeConfiguration<OrderItem>  
    {  
        public void Configure(EntityTypeBuilder<OrderItem> builder)  
        {  
            
  
            builder.OwnsOne(x => x.TotalNetAmount, navigationBuilder =>  
            {  
                navigationBuilder.Property(p => p.Amount).IsRequired();  
                navigationBuilder.Property(p => p.Currency).IsRequired();  
            });  
  
            builder.Navigation(o => o.TotalNetAmount)  
            .IsRequired();  
        }  
    }  
  
    public class Money  
    {  
        public Money(decimal amount, string currency)  
        {  
            Amount = amount;  
            Currency = currency;  
        }  
  
        public decimal Amount { get; private set; }  
        public string Currency { get; private set; }  
    }  
}  

Best regards,
Zhanglong