Entity Framework - Inserting records in child table when parent record already exists

Question

Good afternoon-

I am developing a Silverlight application using LINQ to Entity Framework for the data access layer. My n-tier database model includes about 30 or so tables, with a number of multi-leveled parent-child relationships throughout. Recently began writing my data services and unit tests and have discovered a problem with the insert of a new record into a child table when the parent table record is already defined. Here's an abridged version of my parent and child tables:

PARENT TABLE:

Users
    UserID (PK)
    Email
    Password
    etc...

CHILD TABLE:

UserProfiles
    UserProfileID (PK)
    Various profile info...
    UserID (FK -- Users table)

So at this point, I have the Entity Framework has already been used to create my various classes for the ORM, and I am looking to create data services for the CRUD operations on my entities. The following is sample code for my method to insert a new User (which has no parent table references):

    public User CreateUser(User newUser)
    {
        using (var _context = new ProjectDatabase())
        {
            newUser.CreatedDate = DateTime.Now;
            newUser.ModifiedDate = DateTime.Now;
            _context.AddToUsers(newUser);
            _context.SaveChanges();
        }

        return newUser;
    }

This works just fine. I have written unit tests for it, no problem. Now, on the other hand, consider the following data service method that I have written to insert a new UserProfile object into the database. To begin with, I have a very similar data service:

    public UserProfile CreateProfile(UserProfile newUserProfile)
    {
        using (var _context = new ProjectDatabase())
        {
            newUserProfile.CreatedDate = DateTime.Now;
            newUserProfile.ModifiedDate = DateTime.Now;
            _context.AddToUserProfiles(newUserProfile);
            _context.SaveChanges();
        }

        return newUserProfile;
    }

This code, just like the other, compiles without problem. However, my unit test consistently fails. Here is the code for my unit test:

    [TestMethod]
    public void UserProfileCrudTest()
    {
        IProfileDataService _dataService = new ProfileDataService();

        // Pre-seeded data on a State for foreign key
        State _myState;
        using (var _context = new ProjectDatabase())
        {
            _myState = _context.States.First();
        }

        // Creating temporary User for association with new UserProfile
        User _myUser = new User()
                  {
                      Email = "test",
                      Password = "test",
                      IsActive = true,
                      NameFirst = "test",
                      NameLast = "test"
                  };

        _myUser = _dataService.CreateUser(_myUser);
        Assert.IsNotNull(_myUser);

        // Attempt to create new UserProfile, assigning foreign key
        _myUserProfile = new UserProfile()
                 { 
                     Address = "123 Main", 
                     City = "Anywhere", 
                     State = _myState, 
                     PostalCode = "63021", 
                     UserID = _myUser.UserID 
                 };

        // This call to CreateProfile throws an exception and the test fails!!
        _myUserProfile = _dataService.CreateProfile(_myUserProfile);
        Assert.IsNotNull(_myUserProfile);

        // Remaining test code... never gets this far... 
    }

So at this point, my unit test throws the following exception:

Test method MyProject.DataServices.Tests.SecurityTests.UserProfileCrudTest threw exception: System.InvalidOperationException: The EntityKey property can only be set when the current value of the property is null.

As I understand it from my research so far, this somehow is tying back to the navigation property that associates the new UserProfile object with the User.UserProfiles collection on the parent table/object. But I'm not sure what steps are needed to fix the problem. Do I somehow need to attach the parent to the ObjectContext? Any assistance would be GREATLY appreciated!

Thanks, Jeff S.

Answer 1

You don't need to do all that stuff. With EF 4.0 your code could be as simple as this:

public UserProfile Create(UserProfile userProfile) {
    using (var context = new ProjectDatabase()) {                     
        //add the new graph to the context:
        context.UserProfiles.AddObject(userProfile);
        context.SaveChanges();
    }
    return userProfile;
}

public void UserProfileCrudTest() {    
    User _myUser = new User() {
        Email = "test",
        ...
    };
    UserProfile _myUserProfile = new UserProfile() {
        Address = "123 Main",
        ...
    };

    // join the new UserProfile to the User
    _myUserProfile.User = _myUser;
    UserProfile userProfile = Create(_myUserProfile);    
}

Explanation:

What you did is well make sense in typical data access scenarios, where you would have to first insert the new User, retrieve its UserID, and then use that to insert the new UserProfile. However, SaveChanges does all of this for you when it sees that both are new and that they are related. It also uses the model’s mappings to figure out which is the dependent entity (in this case, UserProfile) and needs the foreign key (UserID). With this information, it executes the database inserts in the correct order.
Adding a new UserProfile to an existing User:
If you want to add a new UserProfile to an existing User and you already have the User object you wish to add the profile to, you can just relate them by writing:

userProfile.User = yourUserObject
// OR
yourUserObject.UserProfiles.add(userProfile)

and then call the SavingChanges().

If you only have the UserID, you can set it just like UserProfile.UserID = userID. BTW, I am aware that this is what you originally tried to do and you got an exception, so here is what you need to do:

1. Update your Model from the database to make sure that your model is fully sync with your DB.

2. Step through your code and make sure that you are setting a legitimate UserID to UserProfile object (something that really exist in User Table) - you can even try to hard code it.

3. Run it again and if you still are getting exception, then please post your stack trace since I ran your code and I did not get any, so your exception might be coming from a completely different issue.