0

I'm working on a serverside blazor project (.net 6) using Entity Framework with code first. I have two tables, let's say (in order to protect private data), we have the Tables Band and Bandsman. Originally, every band could have exactly one bandsman, a bandsman could be connected to more then one band though. It's an example, so please don't question this assumptive circumstances.

I created two classes:

[Table("Band")]
public partial class Band
{
    [Key]
    public int Id { get; set; }
    public string BandName { get; set; }
    public int? BandsmanId { get; set; }
    public virtual Bandsman Bandsman { get; set; }
}

[Table("Bandsman")]
public partial class Bandsman
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Band> Band { get; set; }
}

So far everything works fine. Entity Framework set the correct foreign key. But now I have to insert a second bandsman. Let's say, the first bandsman is a keyboarder, now I need a drummer as well. So I altered the existing classes:

[Table("Band")]
public partial class Band
{
    [Key]
    public int Id { get; set; }
    public string BandName { get; set; }
    public int? BandsmanId { get; set; }
    public int? DrummerId { get; set; }
    public virtual Bandsman Bandsman { get; set; }
    public virtual Bandsman Drummer { get; set; }
}

[Table("Bandsman")]
public partial class Bandsman
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Band> Band { get; set; }
    public virtual List<Band> Drummer { get; set; }
}

I know I have to tell Entity Framework now how to map the tables. So I added mapping instructions to the OnModelCreating-Method in DbContext:

builder.Entity<Band>().HasOne(a => a.Bandsman).WithMany().HasForeignKey(b => b.BandsmanId);
builder.Entity<Band>().HasOne(a => a.Drummer).WithMany().HasForeignKey(b => b.DrummerId);

This doesn't work. When I create the migrations I see that Entity Frameworks tries to create new Columns BandsmanId1 and BandsmanId2 to the Band-Table instead of using the Columns I defined.

So I tried to add the instructions the other way around, too, in addition to the previous ones:

builder.Entity<Bandsman>().HasMany<Band>(a => a.Band).WithOne().HasForeignKey(b => b.BandsmanId);
builder.Entity<Bandsman>().HasMany<Band>(a => a.Drummer).WithOne().HasForeignKey(b => b.DrummerId);

It's still the same, Entity Framework tries to add new columns and map the foreign keys to them.

I also tried to rename Band.BandsmanId to Band.KeyboarderId or rather add and map a new column with the new name (so existing data won't get lost), rename Band.Bandsman to Band.Keyboarder and Bandsman.Band to Bandsman.Keyboarder. With no effect, Entity Framework still seems incapable to use the colums I want it to use. I guess the instructions I added to OnModelCreating in DbContext are incorrect, but I'm not able to find out how to put it right. I found some examples here on stackoverflow and elsewhere, but I can't manage to convert one of this examples to my code. So I hope someone can help me to put the classes and instructions right.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
kinda-wired
  • 80
  • 8
  • You misunderstand what the term ["relation"](https://en.wikipedia.org/wiki/Relation_(database)) means in database theory. It _does not_ refer to a foreign-key constraint. – Dai Nov 14 '22 at 13:21
  • Thanks for that clarification. I edited this part of the question. It was a translation error, as English isn't my mother tongue. "Relation" is a correct translation for the word I would use in German most of the time, this question is one of the rare occasions where it isn't. – kinda-wired Nov 16 '22 at 20:39

1 Answers1

1

After posting my question, I found the solution in a post that was shown as possibly related: Entity Framework Code First - two Foreign Keys from same table I was close, my only mistake was not to name the virtual List-Property of the Bandsman-Class in the .HasMany()-Part of the instructions. So Entity Framework didn't now these properties were related to the foreign key columns in the band-table and tried to create the assumed-to-be-missing columns on its own. This way it works:

builder.Entity<Band>().HasOne(a => a.Bandsman).WithMany(b => b.Band).HasForeignKey(a => a.BandsmanId);
builder.Entity<Band>().HasOne(a => a.Drummer).WithMany(b => b.Drummer).HasForeignKey(a => a.DrummerId);
kinda-wired
  • 80
  • 8