Cannot insert duplicate key in object 'dbo.User'.\r\nThe statement has been terminated

Question

I have a user table. there are references to this table from other tables for fields such as CreatedBy.

Problem is, when I am inserting a row of another table (say 'x'), it tries to insert a new user into the user table.

What it should be doing is insert a row into table 'x' with CreatedBy as the existing user.

Using Entity Framework 4. Anyone faced a problem like that before?

Answer 1

You can insert an entity together with related entities or you can insert an entity without the related entities, just referencing existing ones. It depends on the code you write.

Example 1:

User user = GetUserFromSomewhere();
using (var context = new MyContext())
{
    Order order = new Order();
    order.CreatedBy = user;

    context.Orders.AddObject(order);
    // will put both order and related entity user into Added state
    // because user is not attached to the context

    context.SaveChanges();
    // creates new order and new user and sets the relationship between them
}

Example 2:

using (var context = new MyContext())
{
    User user = context.Users.SingleOrDefault(u => u.Id == 1);
    // query attaches this user to this context
    // user is in state Unchanged now

    Order order = new Order();
    order.CreatedBy = user;

    context.Orders.AddObject(order);
    // will put the order into Added state but doesn't touch the
    // state of already attached related entities -> user remains
    // in state Unchanged

    context.SaveChanges();
    // creates new order with reference to user, but doesn't create new user
}

Example 3:

User user = GetUserFromSomewhere();
using (var context = new MyContext())
{
    context.Users.Attach(user);
    // we attach explicitely to the context telling EF thereby
    // that we know that this user exists in the DB
    // user is in state Unchanged now

    Order order = new Order();
    order.CreatedBy = user;

    context.Orders.AddObject(order);
    // will put the order into Added state but doesn't touch the
    // state of already attached related entities -> user remains
    // in state Unchanged

    context.SaveChanges();
    // creates new order with reference to user, but doesn't create new user
}

Edit

Example 4:

int userId = GetUserIdFromSomewhere();
using (var context = new MyContext())
{
    var user = new User { Id = userId };
    // we create a stub user entity with the correct primary key
    // It's not necessary to set other properties
    // to only set the relationship to the order

    context.Users.Attach(user);
    // we attach explicitely to the context telling EF thereby
    // that we know that this user exists in the DB
    // user is in state Unchanged now

    Order order = new Order();
    order.CreatedBy = user;

    context.Orders.AddObject(order);
    // will put the order into Added state but doesn't touch the
    // state of already attached related entities -> user remains
    // in state Unchanged

    context.SaveChanges();
    // creates new order with reference to user, but doesn't create new user
}