how delete more than one record from database using Linq in asp.net mvc

Question

This is the code for removing one record:

var vehicleProperty = db.VehicleProperties.Where(a => a.EngineId == id).ToList();
db.VehicleProperties.Remove(vehicleProperty);
db.SaveChanges();

If I want to remove more than one item then what I will do, for example, delete all where typeId = 4

I am trying with the code below, but causes an error.

var vp = db.VehicleProperties.Where(a => a.EngineId == id).ToList();
db.VehicleProperties.Remove(vp);
db.SaveChanges();

i am using entity framework Version=5.0.0.0 and using EF Designer form database (entity data model) i tried many code but errors please check my screenshots with code and error

Answer 1

To do it through Linq to Entities you need to iterate through the collection removing them one at a time

var vps = db.VehicleProperties.Where(a => a.EngineId == id).ToList();
foreach (var vp in vps)
    db.VehicleProperties.Remove(vp);
db.SaveChanges();

Alternatively you can just pass in a SQL command as per this MSDN article

Answer 2

From the related article:

var vp = db.VehicleProperties.Where(a => a.EngineId == id);
db.VehicleProperties.RemoveRange(vp);
db.SaveChanges();

or

db.Database.ExecuteSqlCommand("delete from VehicleProperties where EngineId = {0}", 4);

Answer 3

dbContext.Database.ExecuteSqlCommand("delete from VehicleProperties where EngineId = {0}", id);

this is the only time saving way... Check Answer Here

Answer 4

my problem is due to foreign key check out my 2nd image show Exception. first remove child then remove parents

The DELETE statement conflicted with the REFERENCE

Solution i just go to that SQL Server Managment studio open foreign key and set Delete Rule :Cascade (in INSERT And UPDATE Spcification)

Answer 5

You can do the following:

db.VehicleProperties.Where(a => a.EngineId == id).ToList().ForEach(db.tblA.DeleteObject);
db.SaveChanges();

Answer 6

Update after requested information:

As per our inner exception details its quite clear that there is a foreign key constraint conflict.

What that means is, before deleting a key that is referred in other table as foreign key, you have to delete the dependent rows first. You can also remove the constraint causing error to keep the rows dependent on key.

More information about how to achieve it using EF is here

---

In case the number of items to be deleted is not significantly large, you can use what Andy Nichols suggested.

But when you want to delete hundreds and thousands of items, you would notice that method is not performant. I recently encounter one such case and changed my method to what's shown below:

    public void DeleteAll<T>(IEnumerable<T> items) where T : class
    {
        Guard.ArgumentNotNull(items, "items");

        var autoDetectChangesEnabledBefore = Configuration.AutoDetectChangesEnabled;
        var validateOnSaveEnabled = Configuration.ValidateOnSaveEnabled;

        // It is said to make the performance better.
        Configuration.AutoDetectChangesEnabled = false;
        Configuration.ValidateOnSaveEnabled = false;

        foreach (var item in items)
        {
            Set<T>().Attach(item);
            Set<T>().Remove(item);
        }

        SaveChanges();

        Configuration.AutoDetectChangesEnabled = autoDetectChangesEnabledBefore;
        Configuration.ValidateOnSaveEnabled = validateOnSaveEnabled;

    }

You can read about these flags here.

Turning both of these flags off has advantage and disadvantage. In my case it was serving more good (huge performance saving) then bad. Open for comments on if there will be any side effects (haven't seen any in last one month).

Answer 7

When you want to delete one ROW use .First() OR FirstOrDefault()

var x = (from obj in db.Vehiclepropertie where obj.Engineid == id select obj).First();

db.VehicleProperties.Remove(x);

db.SaveChanges();

Note:- You can also use .ToList() or FirstOrDefault instead of First().

AND IF YOU WANT TO DELETE MORE THAN ONE ROW ON THE BASIS OF id THEN

var x = (from obj in db.Vehiclepropertie where obj.Engineid == id select obj).ToList();

db.Vehiclepropertie.RemoveRange(x); 

db.SaveChanges();