Below is my model:
public class TMUrl { //many other properties //only property with type Keyword public List<Keyword> Keywords{get;set;} } public class Keyword { //many other properties //only property with type TMUrl public List<TMUrl> Urls{get;set;} }
So clearly, both the entities have many-to-many relationship. I chose fluent api to tell the entity-framework about this relationship i.e.
modelBuilder.Entity<TMUrl> .HasMany(s => s.Keywords) .WithMany(s => s.URLs).Map(s => { s.MapLeftKey("KeywordId"); s.MapRightKey("UrlId"); s.ToTable("KeywordUrlMapping"); });
but when I do
url.Keywords.Add(dbKey); //where url is object of TMUrl, //dbKey is an existing/new object of Keyword db.SaveChanges();
I get exception
An error occurred while saving entities that do not expose foreign key properties for their relationships....
InnerException:
The INSERT statement conflicted with the FOREIGN KEY constraint "KeywordMaster_Keyword". The conflict occurred in database "DbName", table "dbo.KeywordMaster", column 'Id'.The statement has been terminated.
but when I add Configuration from the otherside aswell, everything works fine. i.e.
modelBuilder.Entity<KeyWord> .HasMany(s => s.URLs) .WithMany(s => s.Keywords) .Map(s => { s.MapLeftKey("KeywordId"); s.MapRightKey("UrlId"); s.ToTable("KeywordUrlMapping"); });
Why?. Why I've to add configuration from both the entities, where I've read here and many other places, configuration for one of the entities should do.
What is the case, when I should add configuration for both of the entities involved in the relationship?
I need to understand this. Why. Please help.
To configure many-to-many relationship Using Data Annotations, you need to create the Join Table in the model. The Join Table BookCategory will have properties for the primary key of both the table. It will have two navigational properties one each for Book and Category class.
“HasMany” and “WithMany” method is used to define one-to-many or many-to-many relation in entity framework. We can configure one-to-many relationships between People and PeopleAddress using Fluent API by the following code in the model class: protected override void OnModelCreating(DbModelBuildermodelBuilder) {
You can create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.
Keep using EF6 if the data access code is stable and not likely to evolve or need new features. Port to EF Core if the data access code is evolving or if the app needs new features only available in EF Core. Porting to EF Core is also often done for performance.
The terms Left
and Right
in MapLeftKey
and MapRightKey
in the many-to-many mapping with Fluent API can be misunderstood and I guess your problem is caused by this misunderstanding.
One might think that it means they describe the columns that are "left" and "right" in the many-to-many join table. That's actually the case if you let EF Code-First create the database and join table based on your Fluent mapping.
But it's not necessarily the case when you create a mapping to an existing database.
To illustrate this with the prototypic many-to-many example of a User
-Role
model assume you have an existing database with a Users
, Roles
and RoleUsers
table:
Now, you want to map this table schema to a simple model:
public class User { public User() { Roles = new List<Role>(); } public int UserId { get; set; } public string UserName { get; set; } public ICollection<Role> Roles { get; set; } } public class Role { public int RoleId { get; set; } public string RoleName { get; set; } }
And you add the Fluent mapping for the Users
entity (you must do it this way, because by convention the model above would be one-to-many and you can't start from the Role
entity side because it has no Users
collection):
modelBuilder.Entity<User>() .HasMany(u => u.Roles) .WithMany() .Map(m => { m.MapLeftKey("RoleId"); // because it is the "left" column, isn't it? m.MapRightKey("UserId"); // because it is the "right" column, isn't it? m.ToTable("RoleUsers"); });
This mapping is wrong and if you try to put "Anna" into role "Marketing"...
var anna = ctx.Users.Find(1); var marketing = ctx.Roles.Find(2); anna.Roles.Add(marketing); ctx.SaveChanges();
...SaveChanges
will throw exactly the exception you are having. The reason becomes clear when you capture the SQL command that is sent with SaveChanges
:
exec sp_executesql N'insert [dbo].[RoleUsers]([RoleId], [UserId]) values (@0, @1) ',N'@0 int,@1 int',@0=1,@1=2
So, EF wants to insert here a row into the join table RoleUsers
with a RoleId
of 1
and a UserId
of 2
which is causing the foreign key constraint violation because there is no user with UserId
2
in the Users
table.
In other words, the mapping above has configured the column RoleId
as the foreign key to table Users
and the column UserId
as the foreign key to table Roles
. In order to correct the mapping we have to use the "left" column name in the join table in MapRightKey
and the "right" column in MapLeftKey
:
m.MapLeftKey("UserId"); m.MapRightKey("RoleId");
Actually looking at Intellisense the description makes it clearer what "Left" and "Right" really mean:
MapLeftKey
Configures the name of the column(s) for the left foreign key. The left foreign key represents the navigation property specified in the HasMany call.
MapRightKey
Configures the name of the column(s) for the right foreign key. The right foreign key represents the navigation property specified in the WithMany call.
So, "Left" and "Right" refer to the order in which the entities appear in the Fluent mapping, not to the column order in the join table. The order in the table actually doesn't matter, you can change it without breaking anything because the INSERT
sent by EF is an "extended" INSERT
that also contains the column names and not only the values.
Perhaps MapFirstEntityKey
and MapSecondEntityKey
would have been a less misleading choice of those method names - or maybe MapSourceEntityKey
and MapTargetEntityKey
.
This was a long post about two words.
If my guess is right that it has anything to do with your problem at all then I would say that your first mapping is incorrect and that you only need the second and correct mapping.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With