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:

alt text

            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;

So, you've attempted and what was the result you got? Did it work? — Leniel Maccaferri
– Leniel Maccaferri, Commented Aug 27, 2010 at 1:59
this is unbelieavably frustrating, all I want to do is add a user, credentials and referral details. I could do this in SQL no problem but entity is crapping out no matter what I do! I can't even figure out how to add the user on one trip and then add the other stuff on another trip to the db. argh — Luke Belbina
– Luke Belbina, Commented Aug 27, 2010 at 7:24
it doesn't like the fact that my IDs are auto increment fields aka store generated. — Luke Belbina
– Luke Belbina, Commented Aug 27, 2010 at 7:33

Ladislav Mrnka · Accepted Answer · 2010-08-27 08:24:58Z

1

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.

edited Aug 27, 2010 at 8:24

answered Aug 27, 2010 at 7:36

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luke Belbina Over a year ago

attempted this a few times and it failed. i think i've narrowed down what the problem is.

Luke Belbina Over a year ago

phew, this was the problem. how frustrating that almost 3-4 hours were wasted based on this silly mistake by me! thanks.

Community · Accepted Answer · 2017-05-23 12:13:30Z

0

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.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Aug 27, 2010 at 2:06

Leniel Maccaferri

103k48 gold badges381 silver badges495 bronze badges

3 Comments

Luke Belbina Over a year ago

i think theres a better way to do this in EF4

Leniel Maccaferri Over a year ago

I added a new link that points to a MSDN page. There you can find great links that will lead you in the right direction for EF4...

Luke Belbina Over a year ago

read thru all this stuff multiple times and still no luck. my OP has been updated to narrow down the problem to credentials

Dave Swersky · Accepted Answer · 2010-08-27 02:27:20Z

0

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.

answered Aug 27, 2010 at 2:27

Dave Swersky

34.8k9 gold badges83 silver badges120 bronze badges

2 Comments

Luke Belbina Over a year ago

yeah, but I can't set them explicitly because the id is generated by the sql db (its an auto incrememnt column)

Luke Belbina Over a year ago

I updated my original post which uncovers the error is somewhere in credentials.

Collectives™ on Stack Overflow

Entity DB Adding New User and Other Related Data to DB

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related