ThroughAssociations - Require identity?

 Just began evaluating Lightspeed and I'm trying to get to grips with ThroughAssociations. I've setup what I think is a simple typical many-to-many relationship in my database:

User :  ID, Username, etc
Role: ID, RoleName
User_Role: UserID, RoleID

I have setup a ThroughAssociation on User so that it goes through User_Role to get to Roles. This appears fine, however I can't add Roles to Users. It seems as if it's expecting User_Role to have a separate identity column ( So it'd be: ID, UserID, RoleID ) which I would find somewhat redundant. Is this required? At the moment I am receving the error "Object cannot be cast from DBNull to other types." when I try to insert the full User.

My definition of User_Role looks very similar to the ContributionTag example.

I can provide source if required :)

Hi Alex,

You're right - you need a PK on the UserRole table.

Cheers,

Andrew.

That's a shame.

Am I right in also saying you cannot use Find with a ThroughAssociation yet? Will this be added?

This works:

var users = unitOfWork.Find<User>(Entity.Attribute("UserRoles.Role.RoleName") == "Administrator");

This does not:

var users = unitOfWork.Find<User>(Entity.Attribute("Roles.Role.RoleName") == "Administrator");

Ideally I'd stop remove the public accessor for UserRoles and also stop using the references to it outside of my model, but it seems I may not be able to?

Hi Alex,

[quote user="AlexW"]Am I right in also saying you cannot use Find with a ThroughAssociation yet? Will this be added? [/quote] 

Correct. I will add this feature to our backlog.

[quote user="AlexW"]Ideally I'd stop remove the public accessor for UserRoles and also stop using the references to it outside of my model, but it seems I may not be able to?[/quote] 

You should be able to make it private. Let me know if you run into any issues.

Cheers,

Andrew.