Fluent API for entity with two foreign keys to the same table and shadow property primary keys












0














I have a class hierarchy that I want to store in a database using EF Core migrations, but there are various complicating factors that make me unsure of what to do.



Here's the class hierarchy:



public sealed class Character
{
private List<Relationship> relationships = new List<Relationship>();

public string Name { get; set; }
public ReadOnlyCollection<Relationship> Relationships { get; }

public Character()
{
Relationships = new ReadOnlyCollection<Relationship>(this.relationships);
}
}


public class Relationship
{
public Character FirstCharacter { get; set; }
public Character SecondCharacter { get; set; }
public string Name { get; set; }
}


So a character can have various relationships, which have a reference to two characters. Internal logic in the class makes sure that all Relationships in foo.Relationships always have a reference to foo in either Relationship.FirstCharacter or Relationship.SecondCharacter.



One complication is that I can't modify the class hierarchy, so I need to use the fluent API only. Because Character has no Id property, those are created via Shadow Properties as follows:



modelBuilder.Entity<Character>.Property<Guid>("Id")
.ValueGeneratedOnAdd()
.HasConversion<string>()
.HasAnnotation("Key", 0);


Now, I need to somehow express that Character has many Relationships, and that Relationship has one FirstCharacter pointing to Character, and one SecondCharacter pointing to Character as well. I cannot figure out how to do this. I currently have the following:



modelBuilder.Entity<Character>()
.HasMany(c => c.Relationships)
.WithOne(r => r.FirstCharacter);


EF complains that Relationship has no primary key, so I define one as follows:



modelBuilder.Entity<Relationship>()
.HasKey("FirstCharacterId", "SecondCharacterId");


("FirstCharacterId" and "SecondCharacterId" are not properties that exist, but those get generated in the Migration as foreign keys, so they do exist at that point.)



Oddly, when I create a migration using this, EF generates a foreign key for both FirstCharacter AND SecondCharacter, despite WithOne() only pointing to FirstCharacter:



migrationBuilder.CreateTable(
name: "Relationship",
columns: table => new
{
FirstCharacterId = table.Column<string>(nullable: false),
SecondCharacterId = table.Column<string>(nullable: false),
Name = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Relationship", x => new { x.FirstCharacterId, x.SecondCharacterId });
table.ForeignKey(
name: "FK_Relationship_Characters_FirstCharacterId",
column: x => x.FirstCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Relationship_Characters_SecondCharacterId",
column: x => x.SecondCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});


I don't understand why it also created the SecondCharacter foreign key when I only define one for FirstCharacter. Also. I'm not sure if this is the proper way to do what I want, because I apparently don't really understand what is going on in the .HasMany().WithOne calls. Any ideas?










share|improve this question


















  • 1




    Every navigation property is mapped to exactly one relationship. Your internal logic doesn't matter - it cannot be expressed in standard relational database model, and EF Core currently supports only physical FK relationships. So 2 Character reference navigation properties in Relationship will create 2 FKs to Character and cannot be mapped to a single collection in Character. You need 2 collections, or if you map just the one, the second relationship still will exist, but w/o navigation. See Relationships
    – Ivan Stoev
    Nov 21 '18 at 12:22
















0














I have a class hierarchy that I want to store in a database using EF Core migrations, but there are various complicating factors that make me unsure of what to do.



Here's the class hierarchy:



public sealed class Character
{
private List<Relationship> relationships = new List<Relationship>();

public string Name { get; set; }
public ReadOnlyCollection<Relationship> Relationships { get; }

public Character()
{
Relationships = new ReadOnlyCollection<Relationship>(this.relationships);
}
}


public class Relationship
{
public Character FirstCharacter { get; set; }
public Character SecondCharacter { get; set; }
public string Name { get; set; }
}


So a character can have various relationships, which have a reference to two characters. Internal logic in the class makes sure that all Relationships in foo.Relationships always have a reference to foo in either Relationship.FirstCharacter or Relationship.SecondCharacter.



One complication is that I can't modify the class hierarchy, so I need to use the fluent API only. Because Character has no Id property, those are created via Shadow Properties as follows:



modelBuilder.Entity<Character>.Property<Guid>("Id")
.ValueGeneratedOnAdd()
.HasConversion<string>()
.HasAnnotation("Key", 0);


Now, I need to somehow express that Character has many Relationships, and that Relationship has one FirstCharacter pointing to Character, and one SecondCharacter pointing to Character as well. I cannot figure out how to do this. I currently have the following:



modelBuilder.Entity<Character>()
.HasMany(c => c.Relationships)
.WithOne(r => r.FirstCharacter);


EF complains that Relationship has no primary key, so I define one as follows:



modelBuilder.Entity<Relationship>()
.HasKey("FirstCharacterId", "SecondCharacterId");


("FirstCharacterId" and "SecondCharacterId" are not properties that exist, but those get generated in the Migration as foreign keys, so they do exist at that point.)



Oddly, when I create a migration using this, EF generates a foreign key for both FirstCharacter AND SecondCharacter, despite WithOne() only pointing to FirstCharacter:



migrationBuilder.CreateTable(
name: "Relationship",
columns: table => new
{
FirstCharacterId = table.Column<string>(nullable: false),
SecondCharacterId = table.Column<string>(nullable: false),
Name = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Relationship", x => new { x.FirstCharacterId, x.SecondCharacterId });
table.ForeignKey(
name: "FK_Relationship_Characters_FirstCharacterId",
column: x => x.FirstCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Relationship_Characters_SecondCharacterId",
column: x => x.SecondCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});


I don't understand why it also created the SecondCharacter foreign key when I only define one for FirstCharacter. Also. I'm not sure if this is the proper way to do what I want, because I apparently don't really understand what is going on in the .HasMany().WithOne calls. Any ideas?










share|improve this question


















  • 1




    Every navigation property is mapped to exactly one relationship. Your internal logic doesn't matter - it cannot be expressed in standard relational database model, and EF Core currently supports only physical FK relationships. So 2 Character reference navigation properties in Relationship will create 2 FKs to Character and cannot be mapped to a single collection in Character. You need 2 collections, or if you map just the one, the second relationship still will exist, but w/o navigation. See Relationships
    – Ivan Stoev
    Nov 21 '18 at 12:22














0












0








0







I have a class hierarchy that I want to store in a database using EF Core migrations, but there are various complicating factors that make me unsure of what to do.



Here's the class hierarchy:



public sealed class Character
{
private List<Relationship> relationships = new List<Relationship>();

public string Name { get; set; }
public ReadOnlyCollection<Relationship> Relationships { get; }

public Character()
{
Relationships = new ReadOnlyCollection<Relationship>(this.relationships);
}
}


public class Relationship
{
public Character FirstCharacter { get; set; }
public Character SecondCharacter { get; set; }
public string Name { get; set; }
}


So a character can have various relationships, which have a reference to two characters. Internal logic in the class makes sure that all Relationships in foo.Relationships always have a reference to foo in either Relationship.FirstCharacter or Relationship.SecondCharacter.



One complication is that I can't modify the class hierarchy, so I need to use the fluent API only. Because Character has no Id property, those are created via Shadow Properties as follows:



modelBuilder.Entity<Character>.Property<Guid>("Id")
.ValueGeneratedOnAdd()
.HasConversion<string>()
.HasAnnotation("Key", 0);


Now, I need to somehow express that Character has many Relationships, and that Relationship has one FirstCharacter pointing to Character, and one SecondCharacter pointing to Character as well. I cannot figure out how to do this. I currently have the following:



modelBuilder.Entity<Character>()
.HasMany(c => c.Relationships)
.WithOne(r => r.FirstCharacter);


EF complains that Relationship has no primary key, so I define one as follows:



modelBuilder.Entity<Relationship>()
.HasKey("FirstCharacterId", "SecondCharacterId");


("FirstCharacterId" and "SecondCharacterId" are not properties that exist, but those get generated in the Migration as foreign keys, so they do exist at that point.)



Oddly, when I create a migration using this, EF generates a foreign key for both FirstCharacter AND SecondCharacter, despite WithOne() only pointing to FirstCharacter:



migrationBuilder.CreateTable(
name: "Relationship",
columns: table => new
{
FirstCharacterId = table.Column<string>(nullable: false),
SecondCharacterId = table.Column<string>(nullable: false),
Name = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Relationship", x => new { x.FirstCharacterId, x.SecondCharacterId });
table.ForeignKey(
name: "FK_Relationship_Characters_FirstCharacterId",
column: x => x.FirstCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Relationship_Characters_SecondCharacterId",
column: x => x.SecondCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});


I don't understand why it also created the SecondCharacter foreign key when I only define one for FirstCharacter. Also. I'm not sure if this is the proper way to do what I want, because I apparently don't really understand what is going on in the .HasMany().WithOne calls. Any ideas?










share|improve this question













I have a class hierarchy that I want to store in a database using EF Core migrations, but there are various complicating factors that make me unsure of what to do.



Here's the class hierarchy:



public sealed class Character
{
private List<Relationship> relationships = new List<Relationship>();

public string Name { get; set; }
public ReadOnlyCollection<Relationship> Relationships { get; }

public Character()
{
Relationships = new ReadOnlyCollection<Relationship>(this.relationships);
}
}


public class Relationship
{
public Character FirstCharacter { get; set; }
public Character SecondCharacter { get; set; }
public string Name { get; set; }
}


So a character can have various relationships, which have a reference to two characters. Internal logic in the class makes sure that all Relationships in foo.Relationships always have a reference to foo in either Relationship.FirstCharacter or Relationship.SecondCharacter.



One complication is that I can't modify the class hierarchy, so I need to use the fluent API only. Because Character has no Id property, those are created via Shadow Properties as follows:



modelBuilder.Entity<Character>.Property<Guid>("Id")
.ValueGeneratedOnAdd()
.HasConversion<string>()
.HasAnnotation("Key", 0);


Now, I need to somehow express that Character has many Relationships, and that Relationship has one FirstCharacter pointing to Character, and one SecondCharacter pointing to Character as well. I cannot figure out how to do this. I currently have the following:



modelBuilder.Entity<Character>()
.HasMany(c => c.Relationships)
.WithOne(r => r.FirstCharacter);


EF complains that Relationship has no primary key, so I define one as follows:



modelBuilder.Entity<Relationship>()
.HasKey("FirstCharacterId", "SecondCharacterId");


("FirstCharacterId" and "SecondCharacterId" are not properties that exist, but those get generated in the Migration as foreign keys, so they do exist at that point.)



Oddly, when I create a migration using this, EF generates a foreign key for both FirstCharacter AND SecondCharacter, despite WithOne() only pointing to FirstCharacter:



migrationBuilder.CreateTable(
name: "Relationship",
columns: table => new
{
FirstCharacterId = table.Column<string>(nullable: false),
SecondCharacterId = table.Column<string>(nullable: false),
Name = table.Column<string>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Relationship", x => new { x.FirstCharacterId, x.SecondCharacterId });
table.ForeignKey(
name: "FK_Relationship_Characters_FirstCharacterId",
column: x => x.FirstCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Relationship_Characters_SecondCharacterId",
column: x => x.SecondCharacterId,
principalTable: "Characters",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});


I don't understand why it also created the SecondCharacter foreign key when I only define one for FirstCharacter. Also. I'm not sure if this is the proper way to do what I want, because I apparently don't really understand what is going on in the .HasMany().WithOne calls. Any ideas?







c# entity-framework entity-framework-core ef-migrations ef-fluent-api






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 21 '18 at 11:33









Bas

1,2641329




1,2641329








  • 1




    Every navigation property is mapped to exactly one relationship. Your internal logic doesn't matter - it cannot be expressed in standard relational database model, and EF Core currently supports only physical FK relationships. So 2 Character reference navigation properties in Relationship will create 2 FKs to Character and cannot be mapped to a single collection in Character. You need 2 collections, or if you map just the one, the second relationship still will exist, but w/o navigation. See Relationships
    – Ivan Stoev
    Nov 21 '18 at 12:22














  • 1




    Every navigation property is mapped to exactly one relationship. Your internal logic doesn't matter - it cannot be expressed in standard relational database model, and EF Core currently supports only physical FK relationships. So 2 Character reference navigation properties in Relationship will create 2 FKs to Character and cannot be mapped to a single collection in Character. You need 2 collections, or if you map just the one, the second relationship still will exist, but w/o navigation. See Relationships
    – Ivan Stoev
    Nov 21 '18 at 12:22








1




1




Every navigation property is mapped to exactly one relationship. Your internal logic doesn't matter - it cannot be expressed in standard relational database model, and EF Core currently supports only physical FK relationships. So 2 Character reference navigation properties in Relationship will create 2 FKs to Character and cannot be mapped to a single collection in Character. You need 2 collections, or if you map just the one, the second relationship still will exist, but w/o navigation. See Relationships
– Ivan Stoev
Nov 21 '18 at 12:22




Every navigation property is mapped to exactly one relationship. Your internal logic doesn't matter - it cannot be expressed in standard relational database model, and EF Core currently supports only physical FK relationships. So 2 Character reference navigation properties in Relationship will create 2 FKs to Character and cannot be mapped to a single collection in Character. You need 2 collections, or if you map just the one, the second relationship still will exist, but w/o navigation. See Relationships
– Ivan Stoev
Nov 21 '18 at 12:22

















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53411206%2ffluent-api-for-entity-with-two-foreign-keys-to-the-same-table-and-shadow-propert%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53411206%2ffluent-api-for-entity-with-two-foreign-keys-to-the-same-table-and-shadow-propert%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'