Adding new child records to a parent table in entity framework 6

Question

I have 3 tables:

Account: Id, Name

User: Id, AccountId, Name

UserDetail: Id, UserId, Phone

Entitites:

public partial class Account
{
    public Account()
    {
        this.Users = new HashSet<User>();
    }

    public int Id{get;set;}
    public string Name{get;set;}
    public virtual ICollection<User> Users{get;set;}
}

public partial class UserDetail
{
    public int Id{get;set;}
    public string Phone {get;set;}
    public virual User User {get;set;}
}

public partial class User
{
    public User()
    {
        this.Accounts = new HashSet<Account>();
    }

    public int Id{get;set;}
    public virtual ICollection<Account> Accounts {get;set;}
    public virtual UserDetail UserDetail{get;set;}
}

As you can see, Account to User is an one to many relationship so my Account entity has Users DbSet. Whereas User to UserDetail is a one-to-one relationship.

I am currently trying to insert a user and userdetail record to an existing account. So my code looks like this:

var newUserDetail = new UserDetail();
newUserDetail.Phone = 014109842;

var newUser = new User();
newUser.Name = "John Smith";
newUser.UserDetail = newUserDetail;

var currentAccount = _dbContext.Accounts.First(a => a.Id == 100);
currentAccount.User.Add(newUser);
_dbContext.SaveChanges();

I'm getting the following error: "A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'Id'."

Thoughts?

You'll get more help if provide your class definitions for Account, User and UserDetail. — Sirhc, Oct 09 '14 at 00:04

score 1 · Accepted Answer · edited May 23 '17 at 12:32

A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: "Id"

I researched this error in more detail and came across this post: A dependent property in a ReferentialConstraint is mapped to a store-generated column

Basically, it says that the UserDetail.Id (which is dependent property) has a referential constraint and is also a store generated column i.e. autoincrement.

The solution was to either remove the autoincrement property or add a different FK on either User to UserDetail or on UserDetail to User.

Lessons learnt: 1. Definitely have identity columns with name of the class/table. For example, if you can, name your identity (PK) column with the name of table as a prepend. So in my question, I should really rename all the Id columns to AccountId, UserId, UserDetailId.

Why?

The innerexception that EF had would have given me an obvious clue to look at the mapping on UserDetailId. Without this, I kept wondering which mapping was the problematic one.

Sirhc · Answer 2 · 2014-10-09T15:08:36.637

0

Try this.

var newUserDetail = new UserDetail();
newUserDetail.Phone = 014109842;
_dbContext.UserDetail.Add(newUserDetail);

var newUser = new User();
newUser.Name = "John Smith";
_dbContext.User.Add(newUser);

_dbContext.saveChanges(); // save changes so the db can generate ID's

newUser.UserDetail = newUserDetail;

var currentAccount = _dbContext.Accounts.First(a => a.Id == 100);
currentAccount.User.Add(newUser);
_dbContext.SaveChanges();

edited Oct 09 '14 at 15:08

answered Oct 09 '14 at 09:25

Sirhc

518
7
13

I should have mentioned that this is a DB first EF model, sorry! I cannot make changes to the model. The account table does not have a foreign key to User table. The user table has an AccountId mapped to Id of Account table. The EF model generator created the mappings. – amythn04 Oct 09 '14 at 10:22
Right, that's what i get for not asking. Updated my answer. – Sirhc Oct 09 '14 at 12:07
That thought did cross my mind but I was wondering if I could get away without using another UserService class in Account controller (if you know what I mean). Also, I'm using unit of work pattern which would break if I do this. – amythn04 Oct 09 '14 at 15:06
Assuming the code you have there is a unit of work, i don't see the problem with it. You could just wrap it in a transaction `using (var transaction = context.Database.BeginTransaction()) { try { <> transaction.Commit(); } catch (Exception) { transaction.Rollback(); } } ` – Sirhc Oct 10 '14 at 09:01
Found the problem! The relationship mapping between User and UserDetail was the problem. UserDetail.Id is an autoincrement identity column and UserDetail.Id was mapped to User.Id. When saving the User, UserDetail. – amythn04 Oct 11 '14 at 13:44

Adding new child records to a parent table in entity framework 6

2 Answers2

Linked