Linq To Sql why am I getting duplicate records?

Question

In the following code GetUserAction does return an instance of action but when the user instance is submitted to the database it creates an additional action row in the database instead of creating a relationship with the existing row that is returned? Why?

using (UserRepository repository = new UserRepository())
{
    var user = new user
    {
        user_created = DateTime.Now,
        user_email = email,
        user_password = GetHashedPassword(password)                            
    };

    // create an entry in the users history
    user.user_histories.Add(new user_history
    {
        user_history_date = DateTime.Now,
        action = GetUserAction("Registered")
    });                          

    // commit the new user to the database
    repository.InsertUser(user);
    repository.Save(); 
}

public static action GetUserAction(string userAction)
{
    action a = null;

    using (UserRepository repository = new UserRepository())
    {
        a = repository.SelectUserAction(userAction);

        // in the SO example I know a is not null so ignore the next 8 lines
        if (a == null)
        {
            a = new action
            {
                action_name = userAction
            };                    
        }
   }

   return a;
}

Answer 1

I can infer from your code that user_history and action are linked by a foreign key relationship.

In this case, although Linq2SQL gives you action as a field in user_history, if you need to link a new user_history with an existing action, you should return the action's primary key and set the appropriate field of the relationship in the user_history object.

Edit: if your action's primary key is an identity auto-generated column, you can check if it is new by comparing it to zero. New objects have their id's set to zero.

Or, you could just alter GetUserAction to insert the action in database if it is a new one. That way, you can guarantee it is always returning an action that already exists in the database.