Foreign key with composite key in EF Core

ef-code-first entity-framework entity-framework-core

Question

I have the following classes:

    public class ProductAttribute 
    {
        public Guid ProductId { get; set; }

        public Guid AttributeId { get; set; }

        public List<ProductAttributeValue> Values { get; set; }

        public object[] GetKeys()
        {
            return new object[] {ProductId, AttributeId};
        }
    }

    public class Product
    {
        public Guid Id { get; set; }

        public string Name { get; set; }
    }

    public class Attribute
    {
        public Guid Id { get; set; }

        public string Name { get; set; }
    }

    public class ProductAttributeValue
    {
        public Guid Id { get; set; }

        public string Name { get; set; }
    }

In origin case Product and Attribute are AggregateRoot so I want skip navigate that by property references. Value is a simple entity but I need that as list reference in my ProductAttribute class as you see that class have composite key. But I want a required relationship with cascade delete between ProductAttribute and ProductAttributeValue.

This project is external module, so my fluent API configurations are extension which called in target app DbContext OnModelCreating. I should config every properties and references else didn't work.

        builder.Entity<ProductAttribute>(b =>
        {
            b.ToTable("ProductAttributes");

            b.HasKey(x => new {x.ProductId, x.AttributeId});

            //I should config ProductAttributeValue one-to-many manually here
        }

        builder.Entity<Product>(b =>
        {
            b.ToTable("Products");

            b.HasKey(x => x.Id);
        }

        builder.Entity<Attribute>(b =>
        {
            b.ToTable("Attributes");

            b.HasKey(x => x.Id);
        }

        builder.Entity<ProductAttributeValue>(b =>
        {
            b.ToTable("ProductAttributeValues");

            b.HasKey(x => x.Id);

            //I should config ProductAttribute many-to-one manually here
        }

How can you configure your Fluent API for ProductAttribute entity to passing this scenario?

1
3
1/30/2019 3:35:22 PM

Accepted Answer

In order to configure the desired relationship as required and cascade delete, you can use the following inside the ProductAttribute entity configuration block:

b.HasMany(e => e.Values)
    .WithOne()
    .IsRequired();

IsRequired is enough because by convention cascade delete is on for required and off for optional relationships. Of course you can add .OnDelete(DeleteBehavior.Cascade) if you want - it will be redundant, but won't hurt.

Please note that the relationships should be configured in a single place. So do it either in ProductAttribute or ProductAttributeValue, but never in both (error prone, may cause unexpected conflicting or overriding configuration issues).

For completeness, here is how you can configure the same inside ProductAttributeValue configuration (requires explicitly providing the HasOne generic type argument due to the lack of navigation property):

b.HasOne<ProductAttribute>()
    .WithMany(e => e.Values)
    .IsRequired();
2
1/30/2019 3:53:30 PM

Popular Answer

Write your ProductAttribute configuration as follows:

modelBuilder.Entity<ProductAttribute>(b =>
{
    b.ToTable("ProductAttributes");

    b.HasKey(x => new {x.ProductId, x.AttributeId});

    b.HasMany(pa => pa.Values).WithOne().IsRequired();
});

But there is a concern of readability. This will add the columns ProductAttributeProductId and ProductAttributeAttributeId as composite foreign key to the table ProductAttributeValues for the shadow property. If you want to make composite foreign key in table ProductAttributeValues more readable then you can update your model ProductAttributeValue model class as follows:

public class ProductAttributeValue
{
    public Guid Id { get; set; }

    public Guid ProductId { get; set; }

    public Guid AttributeId { get; set; }

    public string Name { get; set; }
}

Then update the ProductAttribute configuration as follows:

modelBuilder.Entity<ProductAttribute>(b =>
{
    b.ToTable("ProductAttributes");

    b.HasKey(x => new {x.ProductId, x.AttributeId});

    b.HasMany(pa => pa.Values).WithOne().HasForeignKey(pa => new {pa.ProductId, pa.AttributeId});
});

Now composite foreign key in table ProductAttributeValues will be generated as ProductId and AttributeId.

Thank you.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow