Mapping foreign key to non primary surrogate key column in EF code first

Question

Mapping foreign key to non primary surrogate key column in EF code first

c# ef-code-first entity-framework entity-framework-6 foreign-keys

Question

public class A    
{   
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int Aid { get; set; }    

    public virtual ICollection<B> B { get; set; }    
}


public class B
{    
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]     
    public virtual int Bid { get; set; }

    [Key]
    [Column(Order = 0)]
    [Required]           
    Public virtual string BName {get ; set}

    [Key]
    [Column(Order = 1)]
    [Required]      
    public virtual int Aid { get; set; }

    [ForeignKey("Aid")]
    public virtual  A A { get; set; }

    public virtual ICollection<C> C { get; set; }    
}


public class C
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]     
    public virtual int Cid { get; set; }

    [Key]
    [Column(Order = 0)]
    [Required]    
    Public virtual string CName {get ; set}    

    [Key]
    [Column(Order = 1)]
    [Required]          
    public virtual int Bid { get; set; }

     [ForeignKey("Bid")]
     public virtual  B B { get; set; } 
}

relationship between B and C is troubling me .I dont want to include BName as foreign key in Class C

Error : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical

I understand the error but I want to point to C class only by Bid ,how I can achieve it without disturbing the relationship between A and B.

user5419119

View more on Stack Overflow

Eugene Podskal · Accepted Answer · 5/23/2017 11:54:01 AM

As far as I understand it is not possible to do it in EF code first with either attributes or fluent api:

But I can propose you to remodel your solution a bit - don't create a primary key on B(BName, Aid) - make it an unique index - Unique Key constraints for multiple columns in Entity Framework.

public class B
{    
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]     
    public virtual int Bid { get; set; }

    [Index("IX_B_Name_Aid", 1, IsUnique = true)]
    [Required]           
    Public virtual string BName {get ; set}

    [Index("IX_B_Name_Aid", 2, IsUnique = true)]
    [Required]      
    public virtual int Aid { get; set; }

    [ForeignKey("Aid")]
    public virtual  A A { get; set; }

    public virtual ICollection<C> C { get; set; }    
}


public class C
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]     
    public virtual int Cid { get; set; }

    [Key]
    [Column(Order = 0)]
    [Required]    
    Public virtual string CName {get ; set}    

    [Key]
    [Column(Order = 1)]
    [Required]          
    public virtual int Bid { get; set; }

    [ForeignKey("Bid")]
    public virtual  B B { get; set; } 
}

The index will give you the same performance benefits for querying as the primary key (though with a bit of additional overhead to support the primary surrogate index over the Bid column).

Also, recommended reading - Surrogate vs. natural/business keys.

Mapping foreign key to non primary surrogate key column in EF code first

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related