Entity Framework - Composite Primary Key formed by 3 Foreign Keys

Question

i'm creating a relation between 4 tables: Provider, CostumerSite, DrmType and Drm.

Relation:

in the Drm table there is a composite key formed by the primary keys of the other 3 tables.. this is the Code:

public partial class Provider
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ProviderId { get; set; }

    [StringLength(128)]
    [Required]
    public string Title { get; set; }

    // one-to-many
    public virtual ICollection<Content> Contents { get; set; }

    public virtual Drm Drm { get; set; }

}

 public partial class CustomerSite
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CustomerSiteId { get; set; }

    [Required]
    [StringLength(128)]
    public string Name { get; set; }

    [Required]
    [StringLength(128)]
    public string Username { get; set; }

    [Required]
    [StringLength(128)]
    public string Password { get; set; }

    // many-to-many
    public virtual ICollection<Content> Contents { get; set; }

    // one-to-one
    public virtual Drm Drm { get; set; }
}

 public partial class DrmType
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int DrmTypeId { get; set; }

    [Required]
    [StringLength(128)]
    public string Name { get; set; }

    // one-to-one
    public virtual Drm Drm { get; set; }
}

public partial class Drm
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int DrmId { get; set; }

    // one-to-one relation
    [Key, Column(Order = 1), ForeignKey("CustomerSite")]
    public int CustomerSiteId { get; set; }

    // one-to-one relation
    [Key,  Column(Order = 2), ForeignKey("Provider")]
    public int ProviderId { get; set; }

    // one-to-one relation
    [Key,  Column(Order = 3), ForeignKey("DrmType")]
    public int DrmTypeId { get; set; }

    public virtual Provider Provider { get; set; }
    public virtual CustomerSite CustomerSite { get; set; }
    public virtual DrmType DrmType { get; set; }
}

Is this correct? After this, i'm using the onModel function:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Drm>()
        .HasKey(d => new { d.ProviderId, d.CustomerSiteId, d.DrmType });

    }

But in this way, when i launch the "Enable-Migrations -EnableAutomaticMigrations -Force" command it shows an error:

One or more validation errors were detected during model generation:

Drm_CustomerSite_Source: : Multiplicity is not valid in Role 'Drm_CustomerSite_Source' in relationship 'Drm_CustomerSite'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be ''. Drm_Provider_Source: : Multiplicity is not valid in Role 'Drm_Provider_Source' in relationship 'Drm_Provider'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be ''.

Can you explain how to solve this? Thanks

There is no 1:1 relation in reality, it is always 0-1:1, one side of relation has to be optional. — Akash Kava

Fabio Luz Fabio Luz · Accepted Answer · 2016-02-25T14:09:33

Based on your model, Drm should not have its own "id". So, it should look like this:

public partial class Drm
{
    //remove this property
    //[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    //public int DrmId { get; set; }

    // one-to-one relation
    [Key, Column(Order = 1), ForeignKey("CustomerSite")]
    public int CustomerSiteId { get; set; }

    // one-to-one relation
    [Key,  Column(Order = 2), ForeignKey("Provider")]
    public int ProviderId { get; set; }

    // one-to-one relation
    [Key,  Column(Order = 3), ForeignKey("DrmType")]
    public int DrmTypeId { get; set; }

    public virtual Provider Provider { get; set; }
    public virtual CustomerSite CustomerSite { get; set; }
    public virtual DrmType DrmType { get; set; }
}

Since you provided the [Key] attribute at the class declaration, you do not need to declare HasKey in the model binding. So, remove this line:

modelBuilder.Entity<Drm>()
    .HasKey(d => new { d.ProviderId, d.CustomerSiteId, d.DrmType });

EDIT

Provider, CustomerSite and DrmType must have a collection of Drm.

public partial class Provider
{
    //...
    //public virtual Drm Drm { get; set; }
    public virtual ICollection<Drm> Drms { get; set; }
}

public partial class CustomerSite
{
    //...
    //public virtual Drm Drm { get; set; }
    public virtual ICollection<Drm> Drms { get; set; }
}

public partial class DrmType
{
    //public virtual Drm Drm { get; set; }        
    public virtual ICollection<Drm> Drms { get; set; }
}

Hope this helps!

Entity Framework - Composite Primary Key formed by 3 Foreign Keys

2 Answers