Delete all entities in a table

Question

I want to delete content (only data, but not the structure and foreign keys.) of all tables (all entities using Entity Framework 4+). How can this be done? I am using SQLCE and Entity framework with Visual Studio 2010.

entities.ExecuteStoreCommand("DELETE FROM TABLENAME");

Above query doesn't delete all rows. Later, I would also be using a where claused like

entities.ExecuteStoreCommand("DELETE FROM TABLENAME WHERE COL1=0");

Context.Database.SqlQuery("DELETE FROM TABLENAME") , will that help — Vishal Sharma, Mar 14 '14 at 08:28
duplicate question, http://stackoverflow.com/questions/6089403/delete-all-entities-in-entity-framework — Ehsan, Mar 15 '14 at 08:31
Linked questions are for SQL Server. My question is about SQL Compact — user2330678, Mar 17 '14 at 16:49

Ben · Answer 1 · 2014-03-14T00:09:53.277

0

In SQL you can use TRUNCATE TABLENAME but if you have foreign keys, you will have to set the key count to 0, then truncate, then set keycount to 1 again:

"SET FOREIGN_KEY_CHECKS = 0;"
"TRUNCATE TABLE TALBENAME;"
"SET FOREIGN_KEY_CHECKS = 1;"

OR

"ALTER TABLE TABLENAME;"
"DROP CONSTRAINT FK_Name;"

edited Mar 14 '14 at 00:09

answered Mar 13 '14 at 23:12

Ben

2,433
5
39
69

please elaborate on how to set key count to 0. – user2330678 Mar 13 '14 at 23:15
SqlCeException occurred. Set option= KEYCOUNT – user2330678 Mar 13 '14 at 23:35
Sorry didn't have the syntax right. Edited again, now it should work. – Ben Mar 13 '14 at 23:42
invalid SET OPTION foreign_key_checks – user2330678 Mar 13 '14 at 23:52
I believe there are also ways to manage your foreign keys in Your toolbox/workbench, whichever it might be called – Ben Mar 14 '14 at 00:08
can you guide me through it? I am new to sqlce. BTW can it be done temporarily using toolbox? – user2330678 Mar 14 '14 at 00:10
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49702/discussion-between-ben-and-user2951819) – Ben Mar 14 '14 at 00:13
there are 3 foreign keys. Can I drop and add them again in a single query, ie. without using their names? – user2330678 Mar 14 '14 at 01:11
I may be wrong, but I don't see why not. `DROP CONSTRAINT FK_Name1, FK_Name2, FK_Name3;` could possibly do it. – Ben Mar 14 '14 at 01:26
Hardcoding the names would be the last option. – user2330678 Mar 14 '14 at 01:49

solidau · Accepted Answer · 2014-03-15T08:15:19.833

0

You can use the EntityFramework.Extended library to perform bulk deletes. Here's a sample of what it looks like:

//delete all users where FirstName matches
context.Users.Delete(u => u.FirstName == "firstname");

since you want to delete everything, you could use it like this:

//delete all users
context.Users.Delete(u => u.UserId >= 0);

you can download the package from nuget, or their Github: https://github.com/loresoft/EntityFramework.Extended

It occurred to me that you may be having problems with delete because you need to enable cascade on delete. If you are using the code-first method of EF, you can do it like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    modelBuilder.Entity<User>()
        .HasOptional(a => a.UserDetail)
        .WithOptionalDependent()
        .WillCascadeOnDelete(true); // <-- add this to the code-first model builder.
}

edited Mar 15 '14 at 08:15

answered Mar 14 '14 at 00:14

solidau

4,021
3
24
45

Can you give me a link for download from Nuget? – user2330678 Mar 14 '14 at 01:05
1

it is a little more complicated than that. Nuget is a package management solution for .NET. you dont really download the file, at least not directly. instead, you install the package into your project. here's a brief overview of how you can use the PackageManager Console: http://docs.nuget.org/docs/start-here/using-the-package-manager-console – solidau Mar 14 '14 at 01:07
Will it work with SQL compact? Please excuse my ignorance. I am new to sqlce. – user2330678 Mar 14 '14 at 01:09
as far as i know, yes – solidau Mar 14 '14 at 01:10

Delete all entities in a table

2 Answers2