8

Is there any way to accomplsig something simple as this using EF Code First

DELETE FROM Order WHERE OrderDate >= @minOrderDate AND OrderDate >= @maxOrderDate

I have a table of which I would like to delete at least 10.000 records. I think it would be rather inefficient to retrieve all records first before I can delete them using a for-each loop.

jhoefnagels
  • 359
  • 1
  • 7
  • 21

2 Answers2

31

You can use 

ctx.Database.ExecuteSqlCommand(sqlDeleteStatement,
    new SqlParameter("@minOrderDate", minDate),
    new SqlParameter("@maxOrderDate", maxDate));

NOTE: The accepted answer does not compile.

Eric J.
  • 147,927
  • 63
  • 340
  • 553
3

You can always send across raw SQL through your context. _context.Database.SqlQuery(sqlDeleteStatement, parameterList)

string sqlDeleteStatement = "DELETE FROM Order" + 
    "WHERE OrderDate >= @minOrderDate AND OrderDate >= @maxOrderDate";

List<SqlParameter> parameterList = new List<SqlParameter>();

parameterList.Add(new SqlParameter("@minOrderDate", minDate));

parameterList.Add(new SqlParameter("@maxOrderDate", maxDate));

_context.Database.SqlQuery(sqlDeleteStatement, parameterList);
bdparrish
  • 3,216
  • 3
  • 37
  • 58
  • I saw this option comming up in intellisense but wondered if writing SQL code was the only way to go to make this simple db operation work – jhoefnagels Mar 06 '12 at 15:09
  • 7
    Unless I am mistaken, the code provided here does not even compile - both the [generic](http://msdn.microsoft.com/en-us/library/gg696545(v=vs.103).aspx) and [non-generic](http://msdn.microsoft.com/en-us/library/gg679117(v=vs.103).aspx) versions of `SqlQuery` take a type argument, either as a generic or a regular method argument. Did you mean to use [`ExecuteSqlStatement` method](http://msdn.microsoft.com/en-us/library/gg679456(v=vs.103).aspx)? – Jean Hominal Jun 26 '13 at 15:51
  • This is not an answer at all. -1. – Frederik Prijck Sep 26 '13 at 06:02