C# WPF delete item with its primary key from database using datagrid

Question

With LINQ, I'm trying to delete a selected row in datagrid from database (made with code first) using db.Dishes.Remove(Dish);

But when I delete the item and inserting a new one, primary key (id) of new item "jumps" a value.

E.g.

1 Shoes
2 Jeans   //I delete this item

When adding a new Item

1 Shoes
3 T-Shirt    //jumps a value for Id

I've tried with this too in my DBContext.cs

modelBuilder.Entity<Cart>()
            .HasOptional(i => i.Item)
            .WithMany()
            .WillCascadeOnDelete(true);

But it's not working

Is there a better way to delete an item from database?

Your primary key is set as Identity that's why it is jumping. If you don't need the identify column in your DB then recommend you make the primary key based on functional requirement and manage it through application. — user1672994, Dec 05 '15 at 14:43
@user1672994 Ok, but I need my primary key to be database generated. Could this be possible even if I remove identity setting? — Koosshh56, Dec 05 '15 at 14:45

score 2 · Accepted Answer · edited May 23 '17 at 12:30

The thing is that when we use DELETE it removes the row from the table but the counter is not changed (if the deleted row has an auto increment PK) see DELETE vs TRUNCATE . So, if you want to reuse the key value then you could do something on the lines of:

1) Handle the Auto Increment part of Key in your code

2) If you have access to DB or want to query it something on the lines of this will might be of help (SQL Server) :

DBCC CHECKIDENT ('tablename', RESEED, newseed)

to do this from code you could after the delete do :

db.ExecuteCommand("DBCC CHECKIDENT('tablename', RESEED, newseed);")

where 'newseed' is the id of the deleted row.e.g if newseed is 0 then next insert will be 1 and if it is 10 then the insert will have 11. To get the new seed value you could also get the max id value residing in your db and then work from there. Better check out what approaches you can take if you decide to go down that road.

From Reset autoicrement in SQL Server and how to use it in code.

Your answer is very useful. Where can I do this, since I'm using code first? — Koosshh56, Dec 05 '15 at 15:23

score 0 · Answer 2 · answered Dec 05 '15 at 14:45

If your primary key is an auto integer, you cannot avoid this behavior. This is simply how the database works. If you want to control the int value, do NOT make it the primary key and do not use auto integer. Instead use uniqueidentifier as your primary key and make your int a normal field. Then when you create your new records, you need to have a robust mechanism to get the next index, lock it so nobody else can steal it, and then write your record.

This is not trivial in a multi-threaded environment! You should do some research on the topic and come up with a good scheme. Personally, I'd NEVER attempt to do this and would use a repeatable process to generate numbers that are non-sequential or unique to a thread.

score 0 · Answer 3 · answered Dec 05 '15 at 14:47

0

The primary key has to be unique (by definition), and you have also defined it as an identity column.

So when you delete a row and create a new one, that new one will take the next available key (3 in your case).

If you don't want this behaviour you will have to manage the uniqueness of the primary key yourself.

answered Dec 05 '15 at 14:47

ChrisF

134,786
31
255
325

Ok, that's clear. Thank you. By the way I prefer the database generated value, since it's a really simple application. – Koosshh56 Dec 05 '15 at 14:54

C# WPF delete item with its primary key from database using datagrid

3 Answers3