Lookup of Many-to-Many relationship

I'm wondering if something is wrong with my configuration or my database, because I haven't been able to find any generated code that allows me to do a many-to-many lookup.

I have 3 tables in my DB: 

• User (Id, Code, Password, Active)
• Role (Id, Code, Description)
• UserRole (Id, User, Role)

The 'UserRole' table has two FKs. One to 'User' and the other to 'Role'. I want to write some code that will retrieve a list of Roles that one User belongs to. So basically I have the 'Code' field on the 'User' table. I checked the properties for generating:

<property name="IncludeManyToMany">True</property>
<property name="ManyToManyFormat">{0}From{1}</property>

These look like the proper values. However, I still don't get anything methods generated that have the word 'From' in it. Any ideas what I am doing wrong?

For a while there I thought that my problem was that I had a PK of Id on the UserRole table. I removed that Id from the table and re-generated and still nothing with the 'From' syntax. I then specified the PK to be the User and Role fields on the UserRole table and re-generated. Still no luck.

Any and all help is greatly appreciated.
 

On your RoleProvider, you should have method that looks something like GetByIdFromUserRole where you would pass in the Id of the user you want to get the roles for.  If you can post your complete DDL for the 3 tables (tables schemas, pks and relationships) we can provide additional help.

[edit] UserRoleProvider should be RoleProvider

Also, on your User entity you should have a property called RoleCollection_From_UserRole that returns TList<Role>.  You can deep load this property like:

UserService service = new UserService();
User user = service.GetById(id); //Provide your user id here
service.DeepLoad(user, true, DeepLoadType.IncludeChildren, typeof(TList<Role>));

TList<Role> roles = user.RoleCollection_From_UserRole;

I checked my RoleProvider and there isn't a method called 'GetIdFromUserRole'. I also checked my User Entity for the property 'RoleCollection_From_UserRole' and I couldn't find that either. To be completely certain that I wasn't looking in the wrong place, I performed a search on the generated code that looked for the string 'IdFrom' and got 0 matches.

Here is my DDL for the tables:

CREATE TABLE [Security].[Role](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](255) NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Security].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [Account] [int] NULL,
    [AccountCode] [nvarchar](50) NULL,
    [Password] [varchar](32) NULL,
    [Hint] [varchar](200) NULL,
    [Culture] [varchar](10) NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_User_Account] UNIQUE NONCLUSTERED
(
    [Account] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_User_Code] UNIQUE NONCLUSTERED
(
    [Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Security].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Account] FOREIGN KEY([Account])
REFERENCES [Genealogy].[Account] ([ID])
ON DELETE SET NULL
GO
ALTER TABLE [Security].[User] CHECK CONSTRAINT [FK_User_Account]

CREATE TABLE [Security].[RoleUser](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [User] [int] NOT NULL,
    [Role] [int] NOT NULL,
 CONSTRAINT [PK_RoleUser] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Security].[RoleUser]  WITH CHECK ADD  CONSTRAINT [FK_RoleUser_Role] FOREIGN KEY([Role])
REFERENCES [Security].[Role] ([Id])
GO
ALTER TABLE [Security].[RoleUser] CHECK CONSTRAINT [FK_RoleUser_Role]
GO
ALTER TABLE [Security].[RoleUser]  WITH CHECK ADD  CONSTRAINT [FK_RoleUser_User] FOREIGN KEY([User])
REFERENCES [Security].[User] ([Id])
GO
ALTER TABLE [Security].[RoleUser] CHECK CONSTRAINT [FK_RoleUser_User]

Ben,

 I think he is missing  composite primary key for UserRole table on User and Role columns, eh?

I'll give that a try, but one question first. Should I get rid of the Id field on the UserRole table before making the composite key? Or should I just make a composite key that includes the Id, User, and Role fields? 

csd,

Yes, you don't need Id column (you may want to also name User column to be UserId and Role - RoleId)

That worked! Thanks for all the help. Unfortunately I ran into a similar issue on a related set of tables: dbo.Application, security.User, security.ApplicationUser.

I tried doing the same thing mentioned before about making Application and User fields on the ApplicationUser table the PK. However, it didn't seem to have the same effect. I thought that I should be able to find a property called 'ApplicationCollection_From_ApplicationUser' on the User entity. I couldn't find that property, but I did find 'ApplicationUserCollection'. I'm not sure what the problem could be. The tables seem nearly identical. The only difference that I can see is that the Application table belongs to the dbo schema and Role table belongs to the security schema. Would that matter? Or is there something in my DDL that I'm not seeing?

CREATE TABLE [dbo].[Application](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [Description] [nvarchar](255) NULL,
 CONSTRAINT [PK_Application] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [Security].[ApplicationUser](
    [User] [int] NOT NULL,
    [Application] [int] NOT NULL,
 CONSTRAINT [PK_ApplicationUser_1] PRIMARY KEY CLUSTERED
(
    [User] ASC,
    [Application] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Security].[ApplicationUser]  WITH CHECK ADD  CONSTRAINT [FK_ApplicationUser_Application] FOREIGN KEY([Application])
REFERENCES [dbo].[Application] ([Id])
GO
ALTER TABLE [Security].[ApplicationUser] CHECK CONSTRAINT [FK_ApplicationUser_Application]
GO
ALTER TABLE [Security].[ApplicationUser]  WITH CHECK ADD  CONSTRAINT [FK_ApplicationUser_User] FOREIGN KEY([User])
REFERENCES [Security].[User] ([Id])
GO
ALTER TABLE [Security].[ApplicationUser] CHECK CONSTRAINT [FK_ApplicationUser_User]


CREATE TABLE [Security].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [nvarchar](50) NOT NULL,
    [Account] [int] NULL,
    [AccountCode] [nvarchar](50) NULL,
    [Password] [varchar](32) NULL,
    [Hint] [varchar](200) NULL,
    [Culture] [varchar](10) NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

Yep, that did the trick. It looks like CodeSmith was caching the DB Schema. And yes I did remove the 'Id' field from both of the tables: ApplicationUser and RoleUser.

Everything is working great. Thanks again for the help!

csd