ansaurus

Question

Answer 1

+12 A:

You could do a normal SQL truncate or delete command, using the DataContext.ExecuteCommand method:

context.ExecuteCommand("DELETE FROM Entity");

Or

context.ExecuteCommand("TRUNCATE TABLE Entity");

The way you are deleting is taking long because Linq to SQL generates a DELETE statement for each entity, there are other type-safe approaches to do batch deletes/updates, check the following articles:

CMS 2009-10-04 17:49:34

make sure to fix the word "DELETE"

David 2009-10-04 17:51:06

I +1'd this. Here's a reference explaining the difference between Truncate (which I think you want to do), and Delete:http://www.mssqltips.com/tip.asp?tip=1080

David 2009-10-04 17:55:12

+1 on David's comment: truncate may be *a lot* faster than delete

Fredrik Mörk 2009-10-04 17:56:05

actually you might have to worry about this as well:http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/e21ec1b7-4468-4a37-84e1-3d6a79e58c6b

David 2009-10-04 17:56:58

@David: That problem is specific to the Entity Framework (*Linq-to-Entities*), I have used `TRUNCATE` before without problems on Linq-to-SQL

CMS 2009-10-04 18:05:51

How would you use Truncate? `context.ExecuteCommand("TRUNCATE Entity");`, or something?

Svish 2009-10-04 18:11:40

CMS, yeah, I just caught that (I use EF now). In EF you can use something like _db.DeleteObject(obj); for *one* obj.

David 2009-10-04 18:13:42

@Svish: context.ExecuteCommand("TRUNCATE TABLE Entity");

marc_s 2009-10-04 18:15:26

marc_s is right, here's the full grammar:TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name[ ; ]

David 2009-10-04 18:16:30

Cool. Guess I will create a truncate extension method then :)

Svish 2009-10-04 19:20:40

How can I get the name of a table?

Svish 2009-10-04 21:48:59

Figured it out :)

Svish 2009-10-04 22:37:05

Truncate requires more rights than the normal writer rights required for a DELETE :)

Snake 2010-09-03 09:22:19

ansaurus

tags:

views:

answers:

Linq to Sql: How to clear a table fast

related questions