Fluent API for entity with two foreign keys to the same table and shadow property primary keys
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
add a comment |
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
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 2Character
reference navigation properties inRelationship
will create 2 FKs toCharacter
and cannot be mapped to a single collection inCharacter
. 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
add a comment |
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
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
c# entity-framework entity-framework-core ef-migrations ef-fluent-api
asked Nov 21 '18 at 11:33
BasBas
1,2901329
1,2901329
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 2Character
reference navigation properties inRelationship
will create 2 FKs toCharacter
and cannot be mapped to a single collection inCharacter
. 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
add a comment |
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 2Character
reference navigation properties inRelationship
will create 2 FKs toCharacter
and cannot be mapped to a single collection inCharacter
. 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
add a comment |
0
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 inRelationship
will create 2 FKs toCharacter
and cannot be mapped to a single collection inCharacter
. 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