CODEWITHSUNDEEP
c#visual-studioentity-framework
Najeeb
Najeeb

Reputation: 313

Entity Framework: How do we map a composite key in a third entity class?

I am trying to figure out how to make this work.

I have three entity classes like so:

public class Manufacturer {
    public Manufacturer () {
        MyProducts = new HashSet<Product> ();
    }

    public string ManufacturerName { get; set; }

    public ICollection<Product> MyProducts { get; set; }
}

public class Product {
    public Product () {
        MyPromotions = new HashSet<Promotion> ();
    }

    public string ProductName { get; set; }

    public string ManufacturerName { get; set; }
    public Manufacturer Manufacturer { get; set; }

    public ICollection<Promotion> MyPromotions { get; set; }
}

public class Promotion {
    public Promotion () {
    }

    public int PromotionId { get; set; }

    public string ManufacturerName { get; set; }
    public Manufacturer Manufacturer { get; set; }

    public string ProductName { get; set; }
    public Product Product { get; set; }
}

And their configurations are like so:

public class ManufacturerConfiguration : EntityTypeConfiguration<Manufacturer> {
    public ManufacturerConfiguration () {
        ToTable ("tbl_Manufacturers");

        HasKey (mfr => mfr.ManufacturerName)
            .Property (t => t.ManufacturerName)
            .HasMaxLength (64)
            .HasColumnName ("key_ManufacturerName")
            .HasDatabaseGeneratedOption (DatabaseGeneratedOption.None);

        HasMany (m => m.MyProducts)
            .WithRequired (p => p.Manufacturer)
            .HasForeignKey (p => p.ManufacturerName)
            .WillCascadeOnDelete (false);
    }
}

public class ProductConfiguration : EntityTypeConfiguration<Product> {
    public ProductConfiguration () {
        ToTable ("tbl_Products");

        HasKey (p => new { p.ProductName, p.ManufacturerName});

        Property (t => t.ProductName)
            .HasMaxLength (64)
            .HasColumnName ("key_ProductName")
            .IsRequired ();

        Property (t => t.ManufacturerName)
            .HasMaxLength (64)
            .HasColumnName ("fkey_ManufacturerName")
            .IsRequired ();
    }
}

public class PromotionConfiguration : EntityTypeConfiguration<Promotion> {
    public PromotionConfiguration () {
        ToTable ("tbl_Promotions");

        HasKey (promotion => promotion.PromotionId)
            .Property (promotion => promotion.PromotionId)
            //.HasMaxLength (64)
            .HasColumnName ("key_PromotionName")
            .HasDatabaseGeneratedOption (DatabaseGeneratedOption.None);
    }
}

The Manufacturer entity is a standalone one, ie., it has no foreign keys. The Product entity refers to the Manufacturer class, and its name field, ProductName, must be qualified with the Manufacturer name to avoid name collisions. Consequently, I have defined its key as a composite of both the ManufacturerName and its own ProductName. Works well so far.

The problem is that I need to reference this in the Promotion entity class (assuming that a single promotion promotes only one product). I have tried several ways in which to create a foreign key reference to the product; however, that won't work as Product's key itself is a composite. And EF won't allow me to specify only Product's key (Error: "The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical".)

I have tried several approaches to define a composite foreign key (with both components of the foreign key belonging to different entities), but with no success.

Upvotes: 1

Views: 124

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205599

All you need to configure {ProductName, ManufacturerName} in Promotion as FK to Product.

You could do that in either ProductConfiguration:

HasMany(prod => prod.MyPromotions)
    .WithRequired(prom => prom.Product)
    .HasForeignKey(prom => new { prom.ProductName, prom.ManufacturerName });

or PromotionConfiguration:

HasRequired(prom => prom.Product)
    .WithMany(prod => prod.MyPromotions)
    .HasForeignKey(prom => new { prom.ProductName, prom.ManufacturerName });

(but please don't do that in both - one reason I don't like the EntityTypeConfiguration based setup is the need to determine where to put the relationship configuration, since it logically applies to two entity types).

Additionally, I would suggest removing the Manufacturer navigation property from Promotion. While you can configure a separate FK to Manufacturer through ManufacturerName, it's redundant and is causing multiple cascade paths - the same information could be obtained through Promotion.Product.Manufacturer.

Upvotes: 2

Related Questions