Many to many control: duplicate key problem in mapping table

Question

When using the new many-to-many control (http://code.msdn.microsoft.com/silverlight/Many-to-Many-Control-for-52cd8c6c) I experience the problem that selecting more than 1 choice options results in a duplicate key error in the mapping table. The constraints/keys are all set on SQL database level (MS SQL Server 2008 R2).

Here is my table design:

SurveryAnswers: (this is the mapping table)
+SurveyAnswerID Integer Primary Key
+SurveyQuestionID Integer Foreign Key
+SurveyAnswerOptionID Integer Foreign Key

SurveyQuestions:
+SurveyQuestionID Integer Primary Key
+SurveyQuestionText nvarchar

SurveyAnswerOptions:
+SurveyAnswerOptionID Integer PrimaryKey
+SurveyAnswerOptionText nvarchar

As "Choices Navigation Property" within the LS many-to-many control properties I selected "SurveyAnswerOption". The "choices collection" dropdown only offers the "<Auto>" option.

The fields QuestionID and AnswerOptionID of the mapping table SurveyAnswers are saved correctly but obviously the PrimaryKey field "SurveyAnswerID" is always save as fixed "0" value (not "NULL"!) by the LS control, so trying to save more than 1 choice in this table results in a duplicate key error. I already tried to make the Primary Key field an identity field with auto numbering but as the control always explicitely updates the field with a fixed "0" value this approach does not work neither.

When manually creating some records in the mapping table all these records are displayed correctly by the control. So it seems that the table logic is recognized correctly in general but only saving does not write/update the primary key field properly.

Does anyone have an idea what went wrong?

Thanks a lot in advance for your help.

Answer 1

Hi

You can use it like this:

http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/93/Using-The-Sheel-Shah-Many-To-Many-Control.aspx

Answer 2

Thanks for the link.

I did it exactly the way it is described in the article.

The mapping table is designed like it as shown in the screenshot:

But as described in my post, the primay key "Id" in the mapping table is always inserted with the value "0" so a primary key constraint violation is thrown when multiple choices are made.

Any other ideas?

Answer 3

After some more time of evaluation I found out that obviously the problem only occurs with SQL Server based tables. Using LS integrated entities all works fine. Is this probably a general limitation?

Answer 4

Finally found the solution myself.

After directly connecting to the intrinsic LS database  as described at http://blogs.msdn.com/b/bethmassi/archive/2011/10/27/how-to-connect-to-and-diagram-your-sql-express-database-in-visual-studio-lightswitch.aspx I could get the table definitions which are like this for my example:

CREATE TABLE [dbo].[Answers] (
    [Id]                  INT IDENTITY (1, 1) NOT NULL,
    [Answer_Question]     INT CONSTRAINT [DF_Answers_Answer_Question] DEFAULT ((0)) NOT NULL,
    [Answer_AnswerOption] INT CONSTRAINT [DF_Answers_Answer_AnswerOption] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [Answer_Question] FOREIGN KEY ([Answer_Question]) REFERENCES [dbo].[Questions] ([Id]),
    CONSTRAINT [Answer_AnswerOption] FOREIGN KEY ([Answer_AnswerOption]) REFERENCES [dbo].[AnswerOptions] ([Id])
);

CREATE TABLE [dbo].[AnswerOptions] (
    [Id]               INT            IDENTITY (1, 1) NOT NULL,
    [AnswerOptionText] NVARCHAR (255) CONSTRAINT [DF_AnswerOptions_AnswerOptionText] DEFAULT ('') NOT NULL,
    CONSTRAINT [PK_AnswerOptions] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Questions] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [QuestionText] NVARCHAR (255) CONSTRAINT [DF_Questions_QuestionText] DEFAULT ('') NOT NULL,
    CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED ([Id] ASC)
);

So the clue really is to use the IDENTITY constraint for the primary key in the mapping table. I was sure that I tested this before but obviously I did it not the right way.

So hopefully this post saves some time for all who struggle with the same problem.