Entity Framework 6 Email Checking causes 'Attaching an Entity failed' exception

Question

I have an issue that has been plaguing me for a few hours, I've been able to narrow it down to the exact code block, but I can't seem to make any further progress, I'm still fairly new to EF6 so I might not be 100% current on the best practices.

I have a User model;

public class User
{
    public Guid ID { get; set; }

    [DisplayName("Display Name")]
    [Required]
    public string DisplayName { get; set; }

    [DisplayName("Email Address")]
    [DataType(DataType.EmailAddress, ErrorMessage = "Please enter a valid email address")]
    [Required]
    public string EmailAddress { get; set; }

    public string Company { get; set; }

    [DisplayName("Internal ID")]
    public string InternalId { get; set; }

    [DisplayName("User Status")]
    public UserStatus Status { get; set; }

    [DisplayName("Access Request Notifications")]
    public bool SendNotifications { get; set; }

    [DisplayName("Admin")]
    public bool IsAdmin { get; set; }

    public virtual ICollection<Transaction> Submissions { get; set; }
}

Within my Views the users within the database will be enumerated and displayed to the user, this is all working wonderfully.

On my Create action on the User controller ([HTTP Post]), I am running the check below to see if the email address that is being passed already exists within the database and if it does, it returns a message back to the user informing them and prevents the user from being created.

    public ActionResult Create([Bind(Include = "DisplayName,EmailAddress,Company,InternalId,Status,SendNotifications,IsAdmin")] User user)
    {
        try
        {
            user.ID = Guid.NewGuid();

            if (ModelState.IsValid)
            {
                var existingUser = db.Users.FirstOrDefault(x => x.EmailAddress.Equals(user.EmailAddress, StringComparison.InvariantCultureIgnoreCase));

                if(existingUser == null)
                {
                    db.Users.Add(user);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                else
                {
                    StaticConfig.Trace.Trace(SFTraceEvents.DbFailedAddingUser1, string.Format("User with email address '{0}' already exists in database", user.EmailAddress));
                    ViewData.Add("DbError", string.Format("Creation failed. User with email address '{0}' already exists", user.EmailAddress));
                }
            }
        }
        catch (Exception ex)
        {
            StaticConfig.Trace.Trace(SFTraceEvents.DbFailedAddingUser1, ex);
            ViewData.Add("DbError", "Unable to create user, an internal error has occured. Please try again, if the problem persists, please contact your system administrator.");
        }

        return View(user);
    }

This process works fine, I'm not using the built in 'Find()' method since this only seems to search on the Primary key on an entity and I want to find on something other than the PK.

When I try and implement the same logic on my Edit method, I'm encountering the error below.

Exception: [InvalidOperationException : System.InvalidOperationException: Attaching an entity of type Models.User failed because another entity of the same type already has the same primary key value. This can happen when using the 'Attach' method or setting the state of an entity to 'Unchanged' or 'Modified' if any entities in the graph have conflicting key values. This may be because some entities are new and have not yet received database-generated key values. In this case use the 'Add' method or the 'Added' entity state to track the graph and then set the state of non-new entities to 'Unchanged' or 'Modified' as appropriate.

My Edit method code is currently the following:

    public ActionResult Edit([Bind(Include = "ID,DisplayName,EmailAddress,Company,InternalId,Status,SendNotifications,IsAdmin")] User user)
    {
        try
        {
            if (ModelState.IsValid)
            {
                var existingUser = db.Users.FirstOrDefault(x => x.EmailAddress.Equals(user.EmailAddress, StringComparison.InvariantCultureIgnoreCase));

                if(existingUser == null || existingUser.ID.Equals(user.ID))
                {
                    db.Entry(user).State = EntityState.Modified;
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                else
                {
                    StaticConfig.Trace.Trace(SFTraceEvents.DbFailedUpdatingUser2, user.DisplayName, string.Format("Email address '{0}' already exists in database", user.EmailAddress));
                    ViewData.Add("DbError", string.Format("Unable to save changes, email address '{0}' already exists", user.EmailAddress));
                }
            }
        }
        catch(Exception ex)
        {
            StaticConfig.Trace.Trace(SFTraceEvents.DbFailedUpdatingUser2, user.DisplayName, ex);
            ViewData.Add("DbError", "Unable to save changes, an internal error has occured. Please try again, if the problem persists, please contact your system administrator.");
        }
        return View(user);
    }

So I am checking to see if there is an existing user with the email address that has been entered on the edit page, I am then checking to see if the ID of the user being edited, matches that of the user found within the database, if so, then of course the same email address should be allowed since we are editing the user that this address belongs to. If however the IDs are different, then there is another user in the database using the email address that has been entered on the edit page.

If I remove the 'var existingUser' and the subsequent if() statement that carries out the ID checks then the edit goes through fine, but then I run the risk of having several users with the same email address on the system. When I put the check back in, then I get the exception above.

Anyone got any suggestions on what I might be doing wrong....is there a more efficient way I can check for entities that might already contain certain data?

Edit I have found that in EF6.1, it supports an 'Index' data annotation that seems to allow a unique property to be set within it as well. I need to have a look properly, but this might offer what I'm looking for.

EF6.1 Index Attribute

I also know I could do some raw SQL queries to resolve this, but if possible, I'd like to avoid this.

Answer 1

If you want to make sure that there are no duplicate email addresses in the database, including preventing a user from entering a new email address that is already in use, I would solve it this way:

try
{
    // Make sure that there is an existing user, since this is an edit
    var existingUser = db.Users.FirstOrDefault(x => x.ID.Equals(user.Id));
    if (existingUser == null)
        throw new ValidationException("User not found");

    // Validate that the email address is unique, but only if it has changed
    if (!existingUser.EmailAddress.Equals(user.EmailAddress) &&
        db.Users.Any(x => x.EmailAddress.Equals(user.EmailAddress, StringComparison.InvariantCultureIgnoreCase)))
        throw new Exception(string.Format("Email address '{0}' is already in use", user.EmailAddress));

    // Move data to existing entity
    db.Entry(existingUser).CurrentValues.SetValues(user);

    db.SaveChanges();
}
catch (Exception ex)
{
    StaticConfig.Trace.Trace(SFTraceEvents.DbFailedUpdatingUser2, user.DisplayName,
        string.Format(ex.Message));
    ViewData.Add("DbError", ex.Message);
}

This way of pulling the existing data and mapping properties over is the best way to solve updates, in my experience. You'll be able to do extensive validation involving both the new and the old data etc.

There are several different ways to handle errors. I prefer to throw exceptions and then act on them in the catch clause, but there are other ways too, of course.

Answer 2

The problem you have here is the expectation you have set. The ONLY time this function would run without error is if you are changing the e-mail address. If you are editing any other field, you have a consistency issue.

When you pass the user in to this function, Entity Framework isn't currently tracking that instance. You then go and do a search in the database, and find an existingUser from the database, which Entity Framework is now tracking. You then try to "attach" the first user instance to Entity Framework (via the .State change), but Entity Framework is already tracking the original, not modified version.

What you should do instead is merge the objects if there is an existing tracked item in the database, like so:

if(existingUser == null || existingUser.ID.Equals(user.ID))
{
    attachedUser = db.Entry(existingUser);
    attachedUser.CurrentValues.SetValues(user);
    db.SaveChanges();
    return RedirectToAction("Index");
}