Deleting from a table that contains a composite primary key field

Nov 10, 2009 at 3:47 PM

Hi there,

I seem to have stumbled across an issue to which I could do with some guidence.

I have a joining table ProductCategory which is - as you could guess - just a table with a composite primary key.

When it comes to deleting an entry it seems to try and approach the delete as if the table has a single primary key, checking to make sure it is - else throws an exception.

A solution would be to write a sproc and then use that, but I thought it might be good to hear what others have done.

Surely I am not the first to come across this, so speak up :)

Thanks in advance for the advice..

 

Developer
Nov 10, 2009 at 3:57 PM
Edited Nov 10, 2009 at 3:59 PM

To try and keep things simple we assign one column as the primary key. You can always add a unique index for productId and categoryid if you want 100% functionality of composite primary key. The datalayer does not work composite primary keys.

Yes I know its not optimal but it works and makes your life simple in the long run. The problems start occurring when you have a join table on a join table on a join table, as you got to keep track of the primary key ids.

Hence join tables looks like this:

ProductCategory
==============
-ProductCategoryId (PK identiy)
-ProductId (FK Product.ProductId)
-CategoryId (FK Category.CategoryId)

Nov 10, 2009 at 4:07 PM

Wow. talk about a quick response.

It's a solution, not what i was expecting, but it's a fair one.  

Cheers

Coordinator
Nov 10, 2009 at 8:44 PM
Hi magicgumbo, can you add a single surrogate primary key to this table? That is what I would suggest, as you've spotted the GenericRepository assumes that you will use a single primary key.

On Tue, Nov 10, 2009 at 4:48 PM, magicgumbo <notifications@codeplex.com> wrote:

From: magicgumbo

Hi there,

I seem to have stumbled across an issue to which I could do with some guidence.

I have a joining table ProductCategory which is - as you could guess - just a table with a composite primary key.

When it comes to deleting an entry it seems to try and approach the delete as if the table has a single primary key, checking to make sure it is - else throws an exception.

A solution would be to write a sproc and then use that, but I thought it might be good to hear what others have done.

Surely I am not the first to come across this, so speak up :)

Thanks in advance for the advice..

 

Read the full discussion online.

To add a post to this discussion, reply to this email (MultiTierLinqToSql@discussions.codeplex.com)

To start a new discussion for this project, email MultiTierLinqToSql@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com