Delete query with Where in LINQ

Question

DELETE From Table
WHERE ID in (1,2,3, ... )

Is there any way to produce following query in LINQ? I tried RemoveRange, but from SQL Server Profiler find that it actually deletes records separately

You can search the records using `Contains()` against an array/collection, then delete them using `RemoveRange()` or `DeleteOnSubmit()`. — Tetsuya Yamamoto, Oct 12 '18 at 06:50
RemoveRange doesn't produce following query and there is no DeleteOnSubmit extension method in System.Linq @TetsuyaYamamoto — DVL, Oct 12 '18 at 06:54

score 1 · Answer 1 · answered Oct 12 '18 at 07:17

1

You could first define the item(s) to remove, then iterate over the list removing them one by one: (note that the whole operation has to be done inside database context scope otherwise it won't work)

var toRemove = list.Where(l => l.id == 1 || l.id == 2 || l.id == 3);
foreach (var item in toRemove)
{
    databasecontext.table.Remove(item);      //replace databasecontext.table with your own context and table name
}

answered Oct 12 '18 at 07:17

Keyur PATEL

2,299
1
15
41

This doesn't generate one update statement as explicitly asked in the question. – Gert Arnold Apr 13 '22 at 13:06

score -1 · Answer 2 · answered Oct 12 '18 at 07:34

-1

You can write it in single line

table.RemoveAll(tbl => tbl.id == 1 || tbl.id == 2 || tbl.id == 3);

Hope this helps.

answered Oct 12 '18 at 07:34

Sridhar

55
5

The answer isn't clear. What kind of method is `RemoveAll`? – Gert Arnold Apr 13 '22 at 13:08

score -1 · Answer 3 · answered Apr 08 '22 at 05:05

-1

Try this piece of code to make this, It will work for you.

DataContextClass _DbContext = new DataContextClass();
var remove = _DbContext.tableName.where(x=>x.id >= 1 && x.id <= endValue);
if(remove != null)
{
     db.logins.RemoveRange(remove);
     db.SaveChanges();
}

answered Apr 08 '22 at 05:05

Qais Ali Abbas

36
5

This doesn't answer the question. This also "actually deletes records separately". – Gert Arnold Apr 12 '22 at 15:33
@GertArnold it's working on my side. – Qais Ali Abbas Apr 20 '22 at 12:21
Then this is not Entity Framework (or LINQ-to-SQL, as in the question) but another ORM you didn't mention. The point here is how to prevent multiple delete statements like `DELETE xx WHERE Id = yy` even when they occur by one SaveChanges call. – Gert Arnold Apr 20 '22 at 12:29

Delete query with Where in LINQ

3 Answers3