Hi,

Having some problems figuring out how to get a one to many relationship to update. Typical scenario here: User and Roles.

I can get this to work by updating the User_Roles relation and just adding values there. This works, but it's kinda ugly because you're just dealing with the IDs. Ideally I'd like to be able to update the M->N relationship instead.

        
            // Create a test user
            busUser userRepository = new busUser();
            
            UserEntity user = userRepository.NewEntity();

            user.UsrId = GUID_USERID;
            user.UsrUserName = "TestUser";
            user.UsrPassword = "secret";
            user.UsrGrpId = Guid.NewGuid();


            var roles = userRepository.Context.Role.Where(rol => rol.RolName == "Admin" || rol.RolName == "User");
            user.RoleCollectionViaUserRole.AllowEdit = true;
            user.RoleCollectionViaUserRole.AllowNew = true;
            user.RoleCollectionViaUserRole.AddRange(roles);
            
            
            //Guid rolId = userRepository.Context.Role.Where(rol => rol.RolName == "Admin").Select(rol => rol.RolId).FirstOrDefault();
            //user.UserRole.Add(new UserRoleEntity() { UroId = Guid.NewGuid(), UroRolId = rolId, UroUsrId = user.UsrId });
            //rolId = userRepository.Context.Role.Where(rol => rol.RolName == "User").Select(rol => rol.RolId).FirstOrDefault();
            //user.UserRole.Add(new UserRoleEntity() { UroId = Guid.NewGuid(), UroRolId = rolId, UroUsrId = user.UsrId });
            
            //userRepository.Save();
            user.Save();
            //user.RoleCollectionViaUserRole.SaveMulti();

The uncommented code is what I would like to do. Basically add roles to the RolesCollectionViaUser collection and have that update on a save of the user. However, that doesn't work. While the collection is properly set with the Role entities the update command only writes the UPDATE for the user entity. There are no errors it just doesn't do anything.

If I use the UserRole intermediate property (as in the commented code) then the update does work but I'm back to entering id values rather than entity assignments.

Do many - many updates work and if so what am I missing?

+++ Rick ---

M-N relationships are read only. Better if you work trough that entities directly.

Thanks David,

I had a feeling this was the case but didn't see it explicitly in the documentation (then again mighta missed it).

Thanks,

+++ Rick ---

For clarity: http://www.llblgen.com/documentation/2.6/Tutorials%20and%20examples/examples_howdoi.htm#createmnrelation

Thanks that makes sense.

But couple of things I don't quite get if I do:

IQueryable<UserRoleEntity> roles =
         from role in userRepository.Context.Role
         where role.RolName == "Admin" || role.RolName == "User"
         select new UserRoleEntity 
         { 
             User = user, 
             Role = role
         };

user.UserRole.AddRange(roles);

//userRepository.Save();
user.Save(true);

this code will fail because the Ids are not passed explicitly.

If I do:

IQueryable<UserRoleEntity> roles =
         from role in userRepository.Context.Role
         where role.RolName == "Admin" || role.RolName == "User"
         select new UserRoleEntity 
         { 
             UroRolId = role.RolId, 
             UroUsrId = user.UsrId 
         };

user.UserRole.AddRange(roles);

//userRepository.Save();
user.Save(true);

That works just fine. So it looks to me like the presence of the entity in this case has no effect at all - it's the Ids that make this work...

+++ Rick ---

Correct, in linq always set the fields instead of the related entities.

Though looking at the query, it should work, as setting an entity reference will sync the FK field with the PK value (if present). I'll look into it.

Ah, I see what's wrong. You fetch 'role' in the same query as where you pass it to the Role property of UserRole. This can't be done, as the projection is the area where entity materialization occurs, and therefore 'role' isn't something that exists at the time the UserRoleEntity instances are materialized, so as you'll see, the Role property is null after the query.

LLBLGen Pro uses two fetch pipelines, one for entities and one for all other projections. The one for entities is enhanced in that it can detect inheritance over multiple tables and therefore add additional relations etc. The custom projection one, doesn't do that and doesn't know how to fetch entities, and your query uses that pipeline, even though you materialize entity class instances. The entity fetch pipeline is not requiring 'new' in the projection, you simply select an entity you refer to in the query for example: var q = from c in metaData.Customer where c.Country=="USA" select c;

though: var q = from c in metaData.Customer where c.Country=="USA" select new { c.CustomerId };

or var q = from c in metaData.Customer where c.Country=="USA" select c.CustomerId;

don't fetch entities and therefore use the other pipeline, even though the materialized class is an entity class.

For example this also doesnt work: var q = from c in metaData.Customer where c.Country=="USA" select new {Foo = SomeInMemoryMethod(c)};

as 'c' is passed to an in-memory method, but 'c' is not a real entity object when the projection runs.

This might sound confusing, and actually it is, though it's very hard for us to solve this, as the linq provider converts to our query api and not to raw SQL which is fed to 1 pipeline, as entity fetching is much more difficult than simply projecting a resultset to a list of classes: additional features kick in when an entity is fetched for example, so the runtime has to know what's fetched: entities or some random other object. This is done in the API by either using for example GetMulti() on a collection (entity pipeline), or by fetching a projection or dynamic list (other pipeline).

Thanks Frans,

I guess not quite clear to me - is the projection killing the entity connection on the RoleEntity in my query, or the LINQ query in general?

IOW, if I were to run a Linq query to get just the Roles separately, then loop through and assign them to UserRoles as instances this should work, right?

Can't try this out this second...

+++ Rick ---

rstrahl wrote:

Thanks Frans,

I guess not quite clear to me - is the projection killing the entity connection on the RoleEntity in my query, or the LINQ query in general?

IOW, if I were to run a Linq query to get just the Roles separately, then loop through and assign them to UserRoles as instances this should work, right?

Can't try this out this second...

+++ Rick ---

Yes that will work just fine. Though in this query:

IQueryable<UserRoleEntity> roles =
         from role in userRepository.Context.Role
         where role.RolName == "Admin" || role.RolName == "User"
         select new UserRoleEntity
         {
             User = user,
             Role = role
         };

the 'role' element isn't an entity instance when the project 'new UserRoleEntity {} is executed, as that requires an entity fetch which hasn't happened there. It's caused more or less by how Linq is set up (as sequences) while the query itself is taken as a whole and ran as 1 query on the db.