CODEWITHSUNDEEP
c#sql-serverentity-frameworkcode-firstentity-framework-migrations
C1X
C1X

Reputation: 795

Entity Framework - Composite Primary Key formed by 3 Foreign Keys

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

Relation: enter image description here

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

Upvotes: 1

Views: 2191

Answers (2)

Fabio
Fabio

Reputation: 11990

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!

Upvotes: 1

ThrowingDwarf
ThrowingDwarf

Reputation: 162

The easiest way to solve this is by giving the DRM table its own ID and making the 3 fields Foreign keys. I do not know to what extend you are normalising but it is something often done to prevent unnecessary complex code. keep it clean, keep it simple

Upvotes: 0

Related Questions