Entity DB Adding New User and Other Related Data to DB

Question

Hey. Nooby question but new with Entity.

I am trying to create a new user object and some details in some additional tables which have a foreign key of the newly created user id.

I've attempted to do this in one round trip. Do I have to add the user to the database first and then go back, set the userid's on the other objects and add them. Here is the Entity Model and code to elaborate:

            using (var context = new ServicesEntities())
        {
            newUser = new UsersTable();

            newUser.Address = user.UserDetails.Address;
            newUser.City = user.UserDetails.City;
            newUser.Country = user.UserDetails.Country;
            newUser.Email = user.Email.EmailString;
            newUser.FirstName = user.UserDetails.FirstName;
            newUser.LastName = user.UserDetails.LastName;
            newUser.State = user.UserDetails.State;
            newUser.Zip = user.UserDetails.Zip;

            context.UsersTables.AddObject(newUser);
            context.SaveChanges();
        }

        using (var context = new ServicesEntities())
        {                               
            var referralDetails = new UserReferrals();
            referralDetails.CreatedThruServiceId = 1; // todo don't make this an absolute 1
            referralDetails.ReferralEmail = user.ReferralDetails.ReferralEmail;
            referralDetails.TwoPlusTwoHandle = user.ReferralDetails.TwoPlusTwoHandle;
            referralDetails.UserId = newUser.UserId;               

            context.UserReferrals.AddObject(referralDetails);

            context.SaveChanges();           // THIS WORKS FINE!    
        }

        using (var context = new ServicesEntities())
        {
            var credentials = new UserCredentials();
            credentials.CreatedOn = DateTime.Now;
            credentials.EmailValidated = false;
            //credentials.EmailValidatedOn = null;
            credentials.FailedLoginAttempts = 0;
            credentials.IsLockedOut = false;
            //credentials.LastValidLogin = null;
            credentials.Password = user.Password.PasswordString;
            credentials.PermissionId = 1; // todo don't make this an absolute 1 = user
            credentials.SuccessfulLoginAttempts = 0;
            credentials.UserId = newUser.UserId;        ;

            context.UserCredentials.AddObject(credentials);

            context.SaveChanges(); // THIS ONE CRAPS OUT!
        }

When I run this I get the following Exception when I run SaveChanges():

{"A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'UserId'."}

Note: Updated this with some slightly different code based on an example in a book.

Note2: I've narrowed down the problem to be in the adding of credentials.

Note3: Fixed this, I accidentally had AUTO-INCREMENT set on my UserCredentials userid. If anyone ares here is working code:

public POCO.User AddNewUserToDb(User user)
        {
           if (IsDuplicateUser(user.Email.EmailString))
            {
                throw new DuplicateNameException("This email is already taken.");
            }

            UsersTable newUser;

            using (var context = new ServicesEntities())
            {
                newUser = new UsersTable();

                newUser.Address = user.UserDetails.Address;
                newUser.City = user.UserDetails.City;
                newUser.Country = user.UserDetails.Country;
                newUser.Email = user.Email.EmailString;
                newUser.FirstName = user.UserDetails.FirstName;
                newUser.LastName = user.UserDetails.LastName;
                newUser.State = user.UserDetails.State;
                newUser.Zip = user.UserDetails.Zip;

                var referralDetails = new UserReferrals();
                referralDetails.CreatedThruServiceId = 1; // todo don't make this an absolute 1
                referralDetails.ReferralEmail = user.ReferralDetails.ReferralEmail;
                referralDetails.TwoPlusTwoHandle = user.ReferralDetails.TwoPlusTwoHandle;
                //referralDetails.UserId = newUser.UserId;

                var credentials = new UserCredentials();
                credentials.CreatedOn = DateTime.Now;
                credentials.EmailValidated = false;
                //credentials.EmailValidatedOn = null;
                credentials.FailedLoginAttempts = 0;
                credentials.IsLockedOut = false;
                //credentials.LastValidLogin = null;
                credentials.Password = user.Password.PasswordString;
                credentials.PermissionId = 1; // todo don't make this an absolute 1 = user
                credentials.SuccessfulLoginAttempts = 0;
                //credentials.UserId = newUser.UserId; ;

                newUser.Credentials = credentials;
                newUser.ReferralDetails = referralDetails;

                context.UsersTables.AddObject(newUser);
                context.SaveChanges();
            }

            user.UserId = newUser.UserId;

            return user;     

Answer 1

Take a look at these links:

Using the entity framework to add existing entities to a collection on a newly created entity.

How to create foreign key relationships with the Entity Framework?

Entity Framework - insert new object with a collection of existing objects

The key links for EF4:

Fortunately, in EF4 we can directly update the relationship due to the Foreign Key Association which allows us to keep the foreign key property inside the entity classes. For detail, please see http://blogs.msdn.com/adonet/archive/2009/11/06/foreign-key-relationships-in-the-entity-framework.aspx.

Besides, we have another great feature Self Tracing Entities, which makes the n-tier pattern much easier in EF, http://blogs.msdn.com/adonet/archive/2009/11/15/updated-feature-ctp-walkthrough-self-tracking-entities-for-the-entity-framework.aspx.

Answer 2

EF4 will allow you to include the foreign key values as scalar properties of your entities. Make sure the "Include foreign keys" checkbox is checked when you create your EDM.

Looking at your model, it looks like you've already done that. Just set the foreign key values explicitly, no need to do a round trip.

Answer 3

Try to add related records directly to UserTable record:

newUser.Credentials.Add(credentials);
newUser.ReferralDetails.Add(referralDetails);

Do not set any Id. It will be set during saving automatically.

Edit: Btw. make sure that UserId column in UserCredentials table is not set as auto generated in database.