VB.NET LINQ to SQL Delete All Records

Question

I am having problems with deleting all records in a table with VB.NET. I am using this code to delete all records in the Contacts table

        For Each contact In database.Contacts
             database.Contacts.DeleteOnSubmit(contact)
        Next

But I get this error

Can't perform Create, Update or Delete operations on 'Table(Contact)' because it has no primary key.

Anyone have any suggestions?

Thanks

Answer 1

You should probably have a primary key on your table. This will make working with your table much easier. If you don't have a primary key, try finding a suitable candidate key to set as the primary key. If you have no suitable columns then you may wish to consider adding an auto incrementing surrogate key (called an identity in SQL Server). If you already have a primary key, make sure your LINQ to SQL classes are updated.

However if you just want to delete all values you may find that this method is too slow. An alternative is to execute SQL directly using DataContext.ExecuteCommand:

database.ExecuteCommand("DELETE Contacts");

This doesn't require that the table has a primary key. Note that this will irretrievably delete all rows in your table, so be careful. Even faster is the TRUNCATE command, but note that this requires greater privileges:

database.ExecuteCommand("TRUNCATE TABLE Contacts");

Again, be careful with this command. It will delete all rows from your table.