Update a primary key value using SubSonic 2.2

subsonicsubsonic2.2

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

Best Answer

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?

Related Topic