Best way to delete multiple records in a LINQ query?

Question

What is the best way to remove multiple records in one go with LINQ?

Answer 1

To delete records with Linq2Sql

CustomerDataContext ctx = new CustomerDataContext("connection string");
var customers = ctx.Customers.Where(c => c.Name == "david");

ctx.Customers.DeleteAllOnSubmit(customers);
ctx.SubmitChanges();

Answer 2

The good old SPROCs.....

You can drag the SPROC to your DBML file and it will generate a rich method in your databasecontext class.

• How to: Call a Stored Procedure by Using LINQ (Visual Basic)
• LINQ and Stored Procedures

Answer 3

Here is How I solved the problem :

try
{
    List<MaterialPartSerialNumber> list = db.MaterialPartSerialNumbers.Where(s => s.PartId == PartId && s.InventoryLocationId == NewInventoryLocationId && s.LocationId == LocationId).ToList();
    db.MaterialPartSerialNumbers.RemoveRange(list);
    db.SaveChanges();
}
catch(Exception ex)
{
    string error = ex.Message;
}

First, you can find a list of the items you want to delete.

Then, you can use the function RemoveRange(**list_of_item_to_delete**) so that it removes each instance in the list present in the database.

According to the MSDN, the method removes a range of elements from the List.

For more information, check it here https://msdn.microsoft.com/en-us/library/y33yd2b5(v=vs.110).aspx

Answer 4

Using entity framework 6

using (EntitiesContext db = new EntitiesContext(connString))
        {
            // Retrieve all records from database for deletion
            IEnumerable<entity> entityList = db.entity.where(x => x.id == id).toList();

            // Use Remove Range function to delete all records at once
            db.entity.RemoveRange(entityList);

            // Save changes
            db.SaveChanges();
        }

Answer 5

Removing many records based on single where clause

context.EntityModel
            .RemoveAll(r => r.property == "propertyEntered");

But you could also Remove records from the database that don't exist in List<ListOfBadRecords>

context.EntityModel
            .Where(w => w.propertyID == ID).ToList()
            .RemoveAll(r => !ListOfBadRecords.Any(a => a.anyID == r.propertyID ));

Edit:

Unsure wich is quicker but you could do the 2nd query with this also

.RemoveAll(r => !ListOfBadRecords.Select(s=>s.propertyID ).Contains(w.propertyID ))

Edit2: don't forget context.SaveChanges(); as i did in the first draft

Answer 6

This is what I used, first create an IENumerable object of the table where are the records to be removed are, then just use RemoveRange, and finally just save changes to database. Let's say you want to remove all products from one specific supplier ID on the Products table, this is how you could do that.

IEnumerable<Products> ProductsToRemove= db.Products.Where(x => x.SupplierId== Supplierid);
db.Products.RemoveRange(ProductsToRemove);
db.SaveChanges();

Answer 7

maybe its litle late for this answer but today I ran into this demand myself I and I came up with this solution

CustomerDataContext ctx = new CustomerDataContext("connection string");

ctx.Customers.DeleteAllOnSubmit(ctx.Customers.Where(c => c.Name == "david"));

ctx.SubmitChanges();

Answer 8

I agree with Khurram, it's much more efficient to do this with a simple stored procedure with LINQ (provided you have sufficient permissions in SQL to do this). I'll augment this with an example.

The stored procedure:

CREATE PROCEDURE [dbo].[RemovePermissionsFromRole] 
(
    @ROLE_ID int
)
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM [RolePermissions] WHERE [RoleID] = @ROLE_ID;
END

Drag the stored procedure from the database explorer onto the methods pane in your DBML file. Save the file. And in code:

if (Request.QueryString["RoleID"] != null) {
    int roleID = Convert.ToInt32(Request.QueryString["RoleID"]);

    SETSDataContext context = new SETSDataContext();
    context.RemovePermissionsFromRole(roleID);
}

Answer 9

With just Entity Framework I found this to be the tightest code.

db.PreperProperties.RemoveRange(db.PreperProperties.Where(c => c.preperFk == prpr.id));
db.SaveChanges();