I am trying to insert a new user into a database. The user has a relationship with a role. There is a many to many table (UserRole) to handle the fact that a user can have many roles and vis versa.

I create my user like so:

UserEntity user = new UserEntity();
user.Fname = "Joe";
user.Lname = "Blow";

UserRoleEntity userRoleEntity1 = new UserRoleEntity();
userRoleEntity.RoleId = 3;  // I know the department, but I can't set the UserId since it's an insert.

UserRoleEntity userRoleEntity1 = new UserRoleEntity();
userRoleEntity.RoleId = 4;

user.UserRoles.Add(userRoleEntity1);
user.UserRoles.Add(userRoleEntity2);

Now when I try to call adapter.SaveEntity I am getting an error. The User table has an Identity key and the m:n table doesn't have identity keys. The UserId and RoleId combined make the key of that table.

So when I call the following:

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
               updateComplete = adapter.SaveEntity(user, false, true);
            }

I get an error about: ORMQueryExecutionException: INSERT statement conflicted with COLUMN FOREIGN KEY

What am I doing wrong? This should work right?

Thanks, Ren

Which LLBLGen Pro runtime library version are you using?

Will it work if you try it the other way around:

UserEntity user = new UserEntity();
user.Fname = "Joe";
user.Lname = "Blow";

UserRoleEntity userRoleEntity1 = new UserRoleEntity();
userRoleEntity1.RoleId = 3;
userRoleEntity1.User = user;

UserRoleEntity userRoleEntity2 = new UserRoleEntity();
userRoleEntity2.RoleId = 4;
userRoleEntity2.User = user;

EntityCollection<UserRoleEntity> userRoles = EntityCollection<UserRoleEntity>();
userRoles.Add(userRoleEntity1);
userRoles.Add(userRoleEntity2);

adapter.SaveEntityCollection(userRoles);

Yeah sorry about the double post.

I am using 1.0.2005.1 final

Ideally I wouldn't want to do it the way you are showing. If I am adding a User, a user has roles. So it makes more sense to do it the way I have it.

Here is the actual error message:

An exception was caught during the execution of an action query: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'User_UserRole_FK1'. The conflict occurred in database 'PCDB', table 'User', column 'UserId'. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

How does LLBLGen get the new identity key (from the entry of the new User) and place it into an associative table? I have code in another place where I have a 1:n relationship and it works fine where I insert the parent (Sales Order) and the (items) and it places the new identity key into the many side correctly.

Thanks, Ren

Okay scratch this one. I found the issue.

For the many table (UserRole) in the designer the Relations tab had the fields Is Hidden set to true. This of course was causing the problem.

Everything is working correctly now.

Thanks, Ren