Delete Multiple Rows in Linq to SQL

Question

I am trying to use linq to delete rows from SQL Server database.


var something = (from inv in InventoryLTD

where inv.rate == 1000
select inv)

I tried the following           

 foreach (var detail in something)
             {
                 db.INVENTORYLTD.DeleteOnSubmit(detail);
             }
db.SubmitChanges();

and


db.INVENTORYLTD.DeleteAllOnSubmit(something);
db.SubmitChanges();


both of them only help me to delete one row from the table, but var something should have at least 100 rows .
Can someone tell me why ?

Thanks

Answer 1

 

Did you run SQL PRofiler to see what is being passed to the database?

Answer 2

Notice that performing a batch DELETE in this way, would make the 100 rows to be obtained from the db (with all the fields, except for the lazy loaded ones), and then a DELETE sql statement is auto-generated and sended back to the Sql for every row on SubmitChanges.

Also, the whole collection must be present in memory (at the DataContext ChangeSet) at once.

 

Depending on your scenario, this could be a big performance overhead.

AFAIK Linq to SQL can't easily help you avoid this, that's why Linq still has Stored Procedure support, which seems to me as as a better choice here.

 

Code Snippet

db.DeleteInvByRate(100); // call a SP with a DELETE instruction

 

 

Alternativily, a not so elegant solution, you can including the SQL statement in your code, as:

 

Code Snippet

db.ExecuteQuery("DELETE FROM ....");

 

 

But of course!, I know this doesn't answer your question

Answer 3

It only ran 1 delete sql.

Also, I think it is caused by the primary key suggestion that you gave me.
WHen I actually added one to it... it started to loop through it

Answer 4

So is there anyway to delete everything in one shot using linq?

Answer 5

 

The quick answer is: No without writing any SQL code (Stored Proc or ExecuteQuery)

 

Answer 6

Well... is there anyway to override the current delete method?  My company want everything in strong typed and they are trying to avoid SQL.

Answer 7

 

Yes, see here:

http://weblogs.asp.net/scottgu/archive/2007/08/23/linq-to-sql-part-7-updating-our-database-using-stored-procedures.aspx

BTW, in my opinion you should be using procs for any insert, updates and deletes. Do you guys (sorry ReneeC) have a DBA?

Answer 8

 

You can override the SubmitChanges, and write some custom code, that takes objects from the DataContext.GetChangeSet()

 

But I think that your worse problem here is retrieving the whole collection from the db, keeping it in memory just to delete it. I don't you could avoid it simply. (In my very humble opinion, and without knowing details of the app you are working) my experience with Linq showed me that is a little naif to believe that you can use Linq to completely forget about SQL. It really helps you to forget about 99% of the tedious CRUD querys, or simple selects, but in some oportunities you will need SQL. The lack of strong typing won't hurt in a simple batch DELETE stored proc.

 

Maybe someone else can think in a workaround, anyone?

Answer 9

crock wrote:

Yes, see here: http://weblogs.asp.net/scottgu/archive/2007/08/23/linq-to-sql-part-7-updating-our-database-using-stored-procedures.aspx BTW, in my opinion you should be using procs for any insert, updates and deletes. Do you guys (sorry ReneeC) have a DBA?

 

Yes you can override the Insert/Update/Delete by providing your own Stored Proc for it, but that's at a per-entity level, no batch operations.

 

Linq (out-of-the-box) doesnt allow you to generate any INSERT/UPDATE/DELETE query except those auto-generated to persist single entity changes.

 

There's no:

 

dc.Orders.Where(price > 100).DeleteAll();

 

producing a "DELETE FROM Orders WHERE price > 100" travelling to the Sql

 

AFAIK at the moment that can only be acheived with SP, or SQL Queries,... or extending Linq to SQL Query Provider, (which is not an easy task! )

Answer 10

 

Maybe that's an extension method we could write.

Answer 11

 

Yes!  It would be fun surely , I was thinking that you may need to extend the query provider, the object that translates the Expression Tree into SQL syntax, but maybe the Extension method could (in a not so elegant way as the Query Provider overriding) take the SQL select produced by the previous IQueryable, e.g:

 

Code Snippet

var query = dc.Orders.Where(o => o.Price > 100).DeleteAll();

 

 

 

the extension method could be something like this:

 

Code Snippet

public void(or int) DeleteAll(this IQueryable collection) {

 

    // we should check that collection is an sql queryable attached to a DataContext

 

    string sql = collection.ToString(); // = "SELECT field1, field2 FROM Orders WHERE Price > 100"

 

    //edit this string, by replacing the "SELECT (anything) FROM" by a "DELETE"

   sql = sql.Replace( .... use Regex for this

   // sql = "DELETE FROM Orders WHERE Price > 100"

 

   DataContext dc = collection .... find the reference to DataContext, it must be in the QueryProvider object

    dc.ExecuteQuery(sql);

 

}

 

 

 

This is just pseudo-code... but it sounds like it should work, this is what you had in mind?

 

Let's code it !

 

Answer 12

Looks good to me...

Answer 13

 

Well, It wasn't as easy as it seemed, but here it is,

 

My "multiple delete" is called like this:

 

Code Snippet

private void btnDeleteSales_Click(object sender, EventArgs e)

{

using (MyLibraryDataContext dc = new MyLibraryDataContext())

{

dc.DeleteAll(dc.Books.Where(b => b.AuthorId == author.AuthorId &&

b.Title.StartsWith("[SALE]")));

}

}