Update a primary key value using SubSonic 2.2

Question

Hello everyone,

I'm currently developing an app using SubSonic 2.2 and I'm having problems updating one of the columns of a composite PK on a table.

My code is roughly this:

foreach (pageItem page in pages) {
    page.IdFile = newIdFile;
    page.PageNumber = counter;
    counter++;
    page.Save();
}

This does not update my record.

My pageItem table contains 4 columns that make up the PK. I only need to update one of them to simulate that I'm moving a page object from one file to another, to avoid creating a new record just to copy essentially all the same values except for that Id and the page number.

When I use SQL Server profiler then I can see that the following code is emitted to the DB:

exec sp_executesql N'UPDATE [dbo].[pageItem] SET [IdFile] = @IdFile, [PageNumber] = @PageNumber WHERE [IdPage] = @IdPage AND [IdCont] 
= @IdCont AND [IdExp] = @IdExp AND [IdFile] = @IdFile; 
SELECT @IdPage AS id',N'@PageNumber int,@IdPage int,@IdCont int,
@IdExp varchar(1),@IdFile int', @PageNumber=4,@IdPage=329, @IdCont=1, @IdExp='1', @IdFile=156

So basically my problem is that the update is NOT using different values for the old and new IdFile keys, they're both using the @IdFile parameter.

Is there a workaround that would preferably not involve using CodingHorror for example, that I may use to update my records? I don't mind using a raw SQL statement, it's just that I'm very interested in using SubSonic to its full potential, so any advices will be greatly appreciated.

Regards,

Fernando

Answer 1

Composite PKs can be a pain in many environments. I think that SS2.x doesn't support them very well, but can't now find a reference to confirm that. Is there way that you can re-engineer your table to have a single PK field?

Answer 2

You have to code these special cases in partial class files. Look at the generated code for the table with multiple primary keys to locate the problem. Then code whatever is necessary in a non-generated file. I don't think v3 deals with compound keys either.

Answer 3

Subsonic 3 does not support composite primary keys. Ref: Rob's - the author's - answer in this post: http://stackoverflow.com/questions/1183595/subsonic-3-and-multiple-pk-columns

Fairly certain that this is not supported in SubSonic 2 either.