Linq DBML Mapping a Table With A Primary Key That Spans 2 Columns

Question

I have three tables: Login, LoginRoles and Roles. Login joins to LoginRoles and LoginRoles joins to Roles. LoginRoles is a many-to-many table, and I want to constrain the columns as being unique TOGETHER.

LoginRoles: MemberId (int), RoleId(int)

This is similar to the aspnet_UsersInRoles table in the default membership database: Table 3.2

I set the primary key for this table as both of the columns.

When dragging the table into the DBML diagram I can not see this primary key represented in the diagram and I get the warning (and other similar ones):

Warning 1 DBML1062: The Type attribute 'LoginRole' of the Association element 'Login_LoginRole' of the Type element 'Login' does not have a primary key. No code will be generated for the association. 0 0

Any ideas on how to make it recognise the Primary Key?

Answer 1

You are trying to use 2 value keys as the primary key for the table. This should really be done in the SQL Table Definition. If you right click inside the table definition you should be able to access the Indexes/Keys option. In there you are able to specify the 2 columns you want to use as the primary key.

Alternatively you could run a small SQL Script, something like:

ALTER TABLE LoginRoles
ADD CONSTRAINT pk_MemberRole PRIMARY KEY (MemberID,RoleID)

Once you have updated your table you need to delete/re-drop your table back onto the DBML designer and the code will update.