I'm a bit confused about adding new items to collections that are defined using an m:n relationship.

Here is an example: I have a User entity/table, Role entity/table, UserRole m:n link table (with it's own primary key). All primary keys are GUIDs.

So when I look up a user, I can retrieve a Role collection by accessing the User.Role property. I can also User.Role.Add(Role r) to add a new role to my user.

If I do this, then iterate through the Role collection for my user, the new role is in the list, but when I do User.Save() no record is generated in the UserRole table, nor when I do User.Role.SaveMulti().

I have maybe presumed too much, but if I can navigate to Role in this way from User for lookup, I'd expect to be able to add/update in the same way too...

Is the only way to add the Role by making an entry specifically in the UserRole collection (which of course exists owing to the data model)?

Cheers

MishMash wrote:

I'm a bit confused about adding new items to collections that are defined using an m:n relationship.

Here is an example: I have a User entity/table, Role entity/table, UserRole m:n link table (with it's own primary key). All primary keys are GUIDs.

So when I look up a user, I can retrieve a Role collection by accessing the User.Role property. I can also User.Role.Add(Role r) to add a new role to my user.

If I do this, then iterate through the Role collection for my user, the new role is in the list, but when I do User.Save() no record is generated in the UserRole table, nor when I do User.Role.SaveMulti().

This is correct. The reason for this is is that LLBLGen Pro doesn't support recursive saves. Look at this example: Department: DepartmentID * | DepartmentName Employee: EmployeeID * | EmployeeName | DayOfBirth DepartmentEmployee: DepartmentID * | EmployeeID * | DateStarted

Now, Department and Employee have an m:n relationship. However doing this: Employee.Departments.Add(newDepartment); will never be able to be saved, because 'DateStarted' will not have a value. (the result of the objectified relationship)

Similar example: Order - Product. Have an m:n relationship via Order Line.

This way, you have to do the saving yourself, so you can set the proper values for the fields in the intermedate table, in your case UserRole.

We could have decided to allow you to update recursively in some occasions and not in other occasions. This however would also confuse developers and would break code when an intermediate table suddenly would get a non-PK field, like the 'DateStarted' field in my example: code which would save the related entity automatically would not work anymore since it requires a value for DateStarted. Now you have the mechanism in place in your code, you know what's going on.

I have maybe presumed too much, but if I can navigate to Role in this way from User for lookup, I'd expect to be able to add/update in the same way too...

You can walk the relations because the data is in the database. Fetching data is something else than writing it: as I've tried to illustrate above in my silly example is that writing data can be hard to do in an automatic scenario, but reading will always be the same.

Is the only way to add the Role by making an entry specifically in the UserRole collection (which of course exists owing to the data model)?

Correct. That's also the reason why the intermediate table is made available as an entity. M:n relations between entities is always seen as the same kind of relation, no matter what the intermediate table is: a table with just the PK fields, or also non-PK fields (like 'Order' in the m:n relation between Customer and Employee)

OK - thats what it looked like. I can see the reasoning, though I think this is a little confusing as to the 'correct' way to subsequently access the collections.

No matter!

Great product. Keep it up.

Cheers,

Is this still true now? (nearly two years later!)

TrevorJ wrote:

Is this still true now? (nearly two years later!)

What is still true? Updating m:n relations isn't possible? that's still the case as that's not possible by default: what if the m:n relation is objectified? .

Recursive saves were added early 2004.

So what am I doing wrong here? I have a similar User/Role/UserRole configuration. User, Role,and UserRole have Id integer fields as their PKs and Identity columns. UserRole has FK fields UserId and RoleId which point to their respective tables.

Here is my test code:

            UserEntity user = new UserEntity();
            user.FirstName = "Fred";
            user.LastName = "Flintstone";
            user.Email = @"fred@flintstone.com";
            user.FullName = user.FirstName + " " + user.LastName;
            user.Save();

            RoleEntity role = new RoleEntity();
            role.Name = "Administrator";
            role.Save();

            RoleUserEntity roleuser = new RoleUserEntity();
            roleuser.User = user;
            roleuser.Role = role;
            roleuser.Save(true);

This throws an exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'RoleId', table 'ACC_EMDB.dbo.RoleUser'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

What am I missing? I understand the exception, but I would expect that the RoleUserEntity instance would get RoleId and UserId set as appropriate from their related typed entities.

Nevermind.

I apparently didn't completely refresh my generated code. I refreshed completely and made sure that I had identity columns set properly on everything. All is well now.

I assume that there's no cascading delete capability on the intermediate table entries?

i.e. if I define a FK restriction on RoleUser I have to delete the RoleUser entries before I can delete a User or Role?

briansp wrote:

I assume that there's no cascading delete capability on the intermediate table entries?

i.e. if I define a FK restriction on RoleUser I have to delete the RoleUser entries before I can delete a User or Role?

Recursive or Cascading deletes aren't implemented. I'd suggest wrapping the deletes in a Unit Of Work to improve efficiency.