EF Core Many To Many Problem when adding new Data (Duplicate Key Error)

Question

I have two classes with a M:N Relation. For Example:

class User 
{
    int Id
    string Name

    ICollection<Addresses> Adresses
}

class Address 
{
    int Id
    string Text

    ICollection<User> Users
}

Everything went fine so far. EF created automatically a N:M table in my database. UserAddress with 2 columns (UserId and AddressId).

When I insert data manually with SQL - the output of EF Core is fine. I get all my data. But now I have a problem with inserting from my app:

I create a new User and want to add an existing address.

List<Address> ListOfAdresses = ... //(from DB)!
var y = ListOfAdresses.First();

var x = new User();
x.Addresses.Add(y);

and now when I want to add this to the DBContext and save I get an error.

He tries to create a new address... I get the error

Duplicate Key Error.

How can I prevent this? I don't want him to add a new address. I want him to take the existing address from the db.

score 1 · Answer 1 · answered Jan 04 '22 at 23:32

There could be few things you should be aware of since I cannot explicitly review if they are right:

Check if you are using same context which basically serves as bridge to DB and if you have multiple contexts you cold end up with similar errors (Entity Framework: Duplicate Records in Many-to-Many relationship)
Check if your addresses are tracked by ChangeTracker. If your child entity in relation is fetched AsNoTracking, it will do the work if you do updates only on parent entity in relation. But point at it will error will be when you try to update non-tracked entity, which is then interpreted as new, more precisely EntityState.Added because after fetching from DB it was not tracked. Long story short, check if your entity has modified state.

PS. Regarding joining table, it should be generated by ef conventions so it shouldnt do any problems (https://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx)

score 0 · Answer 2 · answered Jan 04 '22 at 22:32

0

Maybe, u need to check your entity relations. I think, u can try to use x.AddressesId instead of x.Addresses.Add(y).

answered Jan 04 '22 at 22:32

Sinan

9
2

Tim Tuite · Answer 3 · 2022-01-04T22:53:03.423

You are missing a table to define the connection between the address and the user. You need a third table UserAddress { int Id int UserId int AddressId }

Your tables should look like following for a many to many

User { int Id ICollection Address - this is a virtual property }

these records should be unique otherwise you are storing duplicates UserAddress { int UserId - the id of the user
int AddressId - the address you are connecting them too }

Address { int Id string Text }

When you call the save it should first persist the address then it will take the id of the newly stored address and the id of the user and save the link to the UserAddress table.

this is not true anymore. with ef core 5 you can do it without: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#many-to-many — Isitar, Jan 05 '22 at 06:44

score 0 · Answer 4 · answered Jan 04 '22 at 23:47

It is always a good idea not to depend on EF, but create the third table yourself

public  class UserAddress 
{
UserId 
AddressId
virtural User User
virtual Address Address
}

in this case you can always add new user and existing address

var userAddress= new UserAddress { User= new User {..}, AddressId=addressId};
context.UserAddresses.Add(userAddress);
context.SaveChanges();

or connect existing items

var userAddress= new UserAddress { UserId= userId, AddressId=addressId};

score 0 · Answer 5 · answered Jan 05 '22 at 06:37

to perform m:m relation you need to create a table in between, so next example may help you:

create m:m Model

public class UserAdress
{
        public int UserId { get; set; }
        public virtual User User { get; set; }        
        public int AddressId { get; set; }
        public virtual Address Address { get; set; }
}

link your Models to m:m table

public class User 
{
    public int Id { set; get;}
    public string Name { set; get;}
    public virtual ICollection<UserAdress> UserAdresses { set; get;}
}

public class Address 
{
    public int Id { set; get;}
    public string Text { set; get;}
    public virtual ICollection<UserAdress> UserAdresses { set; get;}
}

finally use Fluent API in your DbContext

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<UserAddresses>()
                .HasKey(pc => new { pc.UserId, pc.AddressId});

            modelBuilder.Entity<UserAddresses>()
                .HasOne(pc => pc.User)
                .WithMany(p => p.UserAddresses)
                .HasForeignKey(pc => pc.UserId);

            modelBuilder.Entity<UserAddresses>()
                .HasOne(pc => pc.Address)
                .WithMany(c => c.UserAddresses)
                .HasForeignKey(pc => pc.AddressId);
        }

this is not true anymore. with ef core 5 you can do it without: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#many-to-many — Isitar, Jan 05 '22 at 06:42
yes you are right ;) , depends if he use ef core 5, and this way still working perfectly — Obada Saada, Jan 05 '22 at 07:03

EF Core Many To Many Problem when adding new Data (Duplicate Key Error)

5 Answers5