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

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.

score 26 · Answer 1 · answered May 15 '19 at 23:05

This question is tagged for EF6. But if you find this searching for EF Core. Can use Alternate Keys.

From link:

Alternate keys can be used as the target of a relationship.

class MyContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Blog)
            .WithMany(b => b.Posts)
            .HasForeignKey(p => p.BlogUrl)
            .HasPrincipalKey(b => b.Url);
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public string BlogUrl { get; set; }
    public Blog Blog { get; set; }
}

score 8 · Answer 2 · edited May 23 '17 at 11:54

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

2 Answers2

Linked