Steve T
Steve T

Reputation: 41

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

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.

Upvotes: 4

Views: 2930

Answers (6)

Efrey
Efrey

Reputation: 21

Your code:

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

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

in the data access code:

dbContext.Users.Add(x);

//Just add addition code here before SaveChanges
dbContext.Entry(y).State = EntityState.Unchanged;

dbContext.SaveChanges();

When DbSet.Add method is invoked, Ef core set EntryState of y to Add by default. When SaveChanges method is invoked, Ef core will try to insert the y into db, this caused the duplicate key exception thrown.

Just mark entry state of y to Unchanged, Ef core will only add the relationship between x and y to UserAddress table, and avoid to insert y into Address table,

Upvotes: 2

Obada Saada
Obada Saada

Reputation: 78

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);
        }

Upvotes: 0

Serge
Serge

Reputation: 43959

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};

Upvotes: 0

Duje Šarić
Duje Šarić

Reputation: 39

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

  1. 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)
  2. 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)

Upvotes: 2

Tim Tuite
Tim Tuite

Reputation: 1

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.

Upvotes: 0

Sinan
Sinan

Reputation: 11

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

Upvotes: 0

Related Questions