Entity Framework adding record twice

Question

This is my first post on stack so sorry for any inconvenience or my bad English. I'm having a problem with Entity Framework SaveChanges() adding new User record twice into the database table.

I've created a UserFormViewModel for posting data based on user inputs from modal window form.

This is my UserFormViewModel:

    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Username { get; set; }

    [Required]
    [StringLength(20)]
    [DataType(DataType.Password)]
    public string Password { get; set; }

    [Required]
    [StringLength(50)]
    public string Name { get; set; }

    [Required]
    [StringLength(50)]
    public string Surname { get; set; }

    [Required]
    [StringLength(255)]
    [EmailAddress]
    public string Email { get; set; }

    public byte UserRoleId { get; set; }
    public Address Address { get; set; }

    public IEnumerable<UserRole> UserRoles { get; set; }

All inputs from the view are mapped correctly to AddOrEdit action method parameter, so problem starts when saving the data into the database via SaveChanges() method. It's adding record twice into the table.

I've tried adding User separately and it adds that User twice.
Then I've tried adding the Address separately and it adds Address twice.
When saving User and Address in one dbContext instance or in two separated dbContext instances, User and Address are again added twice.

Also tried database first approach but results are the same.

I'm assuming it has to do with relationships between the tables but can't point to the right cause.

In Address model class there are standard properties for street address:

 Id, StreetName, StreetNumber, Zip, City

and two foreign keys:

    public int? UserId { get; set; }
    public int? ShopId { get; set; }

    public virtual User User { get; set; }
    public virtual Shop Shop { get; set; }

So User and Shop share Address table for their addresses.

Another way around when User and Shop are set to reference Address table all works fine.

But I want to understand why this is not working and in the end to make Address be able to cascade on User or Shop delete instead of using LINQ to remove Address.

Here is the action method AddOrEdit for a User and its Address:

private readonly FurnitureStoreDbContext _context;
    
public UsersController()
{
    _context = new FurnitureStoreDbContext();
}

protected override void Dispose(bool disposing)
{
    _context.Dispose();
}

[HttpPost]
public ActionResult AddOrEdit(UserFormViewModel userForm)
{
    if (userForm.Id == 0)
    {
        var newUser = new User
                          {
                              Username = userForm.Username,
                              Password = userForm.Password,
                              Name = userForm.Name,
                              Surname = userForm.Surname,
                              Email = userForm.Email,
                              UserRoleId = userForm.UserRoleId
                          };

        _context.tblUsers.Add(newUser);

        _context.SaveChanges();

        var newAddress = new Address
            {
                StreetName = userForm.Address.StreetName,
                StreetNumber = userForm.Address.StreetNumber,
                ZipCode = userForm.Address.ZipCode,
                City = userForm.Address.City,
                UserId = newUser.Id
            };

        _context.tblStreetAddresses.Add(newAddress);
        _context.SaveChanges();

        return Json(new { success = true, message = "Saved" }, JsonRequestBehavior.AllowGet);
    }
    else
    {
        var updatedUser = new User
            {
                Username = userForm.Username,
                Password = userForm.Password,
                Name = userForm.Name,
                Surname = userForm.Surname,
                Email = userForm.Email,
                UserRoleId = userForm.UserRoleId
            };

        var updatedAddress = userForm.Address;

        _context.Entry(updatedUser).State = EntityState.Modified;
        _context.SaveChanges();

        _context.Entry(updatedAddress).State = EntityState.Modified;
        _context.SaveChanges();

        return Json(new { success = true, message = "Updated" }, JsonRequestBehavior.AllowGet);
    }
}

Here is a debug log for adding only User - without adding Address.

Violation of UNIQUE KEY constraint 'UQ__Users__536C85E479927847' exception is happening because I've set Username field to be unique and context is trying to add User record twice.

Step into: Stepping over property 'FurnitureStore.Models.User.get_Addresses'. To step into properties or operators, go to Tools->Options->Debugging and uncheck 'Step over properties and operators (Managed only)'.
Step into: Stepping over property 'FurnitureStore.Models.User.get_Addresses'. To step into properties or operators, go to Tools->Options->Debugging and uncheck 'Step over properties and operators (Managed only)'.
Opened connection at 3/27/2021 8:55:09 PM +01:00
Opened connection at 3/27/2021 8:55:09 PM +01:00
Started transaction at 3/27/2021 8:55:09 PM +01:00
Started transaction at 3/27/2021 8:55:09 PM +01:00
INSERT [dbo].[Users]([Username], [Password], [Name], [Surname], [Email], [UserRoleId])
VALUES (@0, @1, @2, @3, @4, @5)
SELECT [Id]
FROM [dbo].[Users]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()INSERT [dbo].[Users]([Username], [Password], [Name], [Surname], [Email], [UserRoleId])
VALUES (@0, @1, @2, @3, @4, @5)
SELECT [Id]
FROM [dbo].[Users]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

-- @0: 'Username 1' (Type = String, Size = 50)
-- @0: 'Username 1' (Type = String, Size = 50)
-- @1: 'q1w2e3r4' (Type = String, Size = 20)
-- @2: 'Name 1' (Type = String, Size = 50)
-- @3: 'Surname 1' (Type = String, Size = 50)
-- @4: '[email protected]' (Type = String, Size = 255)
-- @5: '2' (Type = Byte, Size = 1)
-- Executing at 3/27/2021 8:55:10 PM +01:00
-- @1: 'q1w2e3r4' (Type = String, Size = 20)
-- @2: 'Name 1' (Type = String, Size = 50)
-- @3: 'Surname 1' (Type = String, Size = 50)
-- @4: '[email protected]' (Type = String, Size = 255)
-- Completed in 7 ms with result: SqlDataReader

-- @5: '2' (Type = Byte, Size = 1)
-- Executing at 3/27/2021 8:55:10 PM +01:00
Committed transaction at 3/27/2021 8:55:10 PM +01:00
-- Failed in 26 ms with error: Violation of UNIQUE KEY constraint 'UQ__Users__536C85E479927847'. Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (Username 1).
The statement has been terminated.

Closed connection at 3/27/2021 8:55:10 PM +01:00
Closed connection at 3/27/2021 8:55:10 PM +01:00
Exception thrown: 'System.Data.Entity.Infrastructure.DbUpdateException' in EntityFramework.dll
An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code
An error occurred while updating the entries. See the inner exception for details.

The thread 0x42cc has exited with code 0 (0x0).

Any help is appreciated.

Thanks :)

That code looks fine. You're probably running the controller twice, or already have that user in your database. And you've got a bug on the "update" branch in that you don't set the user's ID on the entity before trying to update. — David Browne - Microsoft
– David Browne - Microsoft, Commented Mar 27, 2021 at 20:56
updatedUser doesn't have an Id value set (while the database table clearly has the field). That should cause trouble updating, but no a re-insert, the way you coded it. — Gert Arnold
– Gert Arnold, Commented Mar 27, 2021 at 21:25

Steve Py · Accepted Answer · 2021-03-28 04:08:24Z

I would recommend splitting adding and updating into separate actions, as it's easy to configure the appropriate link in the UI via Razor etc.

In any case your issue is with your update logic. When working with EF DbContexts you should seek to retrieve the entity when updating. For example:

var existingUser = _context.Users
    .Include(x => x.Address)
    .Single(x => x.UserId == userForm.Id);

From here, copy across the fields from your UserForm that you allow to be edited. For instance if they can change their name, surname, and email:

existingUser.Name = userForm.Name;
existingUser.Surname = userForm.Surname;
existingUser.Email = userForm.Email;

The benefit of this approach is that EF will only generate an UPDATE statement for the fields that actually change, and only if at least one actually changes. Using the Update() method or setting an entity's state to Modified will result in an UPDATE statement for all columns, even if nothing actually changed.

The next issue will be with references. When using view models, entities should never be passed to the view. Code like this is generally problematic:

var updatedAddress = userForm.Address;
_context.Entry(updatedUser).State = EntityState.Modified;

This makes a lot of dangerous assumptions that the userForm.Address points to a valid record, and that the DbContext isn't already tracking an entity with that ID.

In the previous example statement that fetched the user from the DbContext, I eager loaded the address. This way instead of passing an Address entity in your UserForm view model, pass a POCO view model for the address details and you can use that to edit or insert an address as needed:

if (existingUser.Address != null)
{
    existingUser.Address.AddressLine1 = userForm.Addres.AddressLine1;
    // ...
}
else
{
    var address = new Address
    {
        AddressLine1 = userForm.Addres.AddressLine1;
        // ...
    };
    existingUser.Address = address;
}

It's quite important to fetch references when dealing with many-to-many or many-to-one relationships like when associating Users to Shops. From the user perspective when associating to a shop, the Shop entity should already exist, so fetching it from the DbContext by ID serves as a validation that the ShopId is valid, and also avoids situations where you end up unintentionally inserting duplicate rows.

Lastly, only call SaveChanges() once. This ensures that changes made to the various related entities only happen all together or not at all.

It is possible to update entities by using methods like Update and Attach /w EntityState however this method is quite error prone and can lead to intermittent / situational runtime errors in cases where the DbContext could be already tracking an entity when you use something like Attach or Update on a new instance with the same ID. As mentioned it also leads to less efficient UPDATE SQL statements and can leave your system vulnerable to unintended tampering.

It was a stupid mistake sorry. I forgot to type return false in onSubmit(form) function, so form was submitted twice. All the time I was focusing on C# code. Anyway your suggestions improved my code and prevented future errors so thank you very much for your time and effort everyone :)

Dharman · Accepted Answer · 2021-07-12 14:47:30Z

0

I just thought I'd add my personal experience and fix. I was having an issue with all of my calls being posted twice but only in firefox. The issue turned out to be the way firefox and iis communicate. The way I solved was by disabling HTTP/2 in the https binding settings inside IIS. I only discovered the solution after a day looking through code for a blank href or src somewhere.

edited Jul 12, 2021 at 14:47

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jul 12, 2021 at 14:41

dev

3464 silver badges6 bronze badges

Collectives™ on Stack Overflow

Entity Framework adding record twice

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related