SQL Server Add foreign key to existing table using EF core

42fyovps  于 2023-06-21  发布在  其他
关注(0)|答案(1)|浏览(103)

I have HomeSetting table with duplicate HomeIds and I want to remove it by setting the foreign key as well.

public class Home
{
    [Key]
    public int Id { get; set; }

    [Required]
    [Column(TypeName = "NVARCHAR(200)")]
    public string HomeId { get; set; }

    [Required]
    [Column(TypeName = "NVARCHAR(200)")]
    public string HomeName { get; set; } 
}

public class HomeSetting
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column(TypeName = "NVARCHAR(200)")]
    public string HomeId { get; set; }

    [Column(TypeName = "NVARCHAR(MAX)")]
    public string Settings { get; set; }
}

I added [ForeignKey("Home")] attribute to HomeSetting's table HomeId column but it doesn't work. How do i mange this using ef core migration ?

[ForeignKey("Home")]
public string HomeId { get; set; }
s8vozzvw

s8vozzvw1#

You must use key for foreign key and cant use property for foreign key.
In this moment we have two way:

  1. Use HomeId as a key
  2. Use both of them for key

actually first way is simple I write second way:

  1. Create classes
public class Home
{
   [Key]
   public int Id { get; set; }

   [Required]
   [Column(TypeName = "NVARCHAR(200)")]
   public string HomeId { get; set; }

   [Required]
   [Column(TypeName = "NVARCHAR(200)")]
   public string HomeName { get; set; }

   public HomeSetting HomeSetting { get; set; }
}

public class HomeSetting
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Column(TypeName = "NVARCHAR(200)")]
    public string HomeId { get; set; }

    public int HomeIntId { get; set; }

    [Column(TypeName = "NVARCHAR(MAX)")]
    public string Settings { get; set; }

    public Home Home { get; set; }
}
  1. Create mapping class
public class HomeMap : IEntityTypeConfiguration<Home>
{
        public void Configure(EntityTypeBuilder<Home> builder)
        {
            builder.HasKey(x => new { x.Id, x.HomeId});

            builder.HasOne(x => x.HomeSetting)
                .WithOne(x => x.Home)
                .HasForeignKey<HomeSetting>(x => new {x.HomeIntId, x.HomeId });
       }
}
  1. Override OnModelCreateing method in DBContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      base.OnModelCreating(modelBuilder);
      modelBuilder.ApplyConfiguration(new HomeMap());
  }

相关问题