How can I delete multiple rows with Entity Framework?

Question

I have this code but it doesn't work :

public bool DeleteAccess(int _UserID, IEnumerable<int> _ModulID)
{
 MPortalContext db=new MPortalContext();
 foreach (var item in _ModulID)
 {
 var validation = db.WebSite_PermissionDB.Where(x => x.UserID == _UserID && x.ModuleID == item);
 db.WebSite_PermissionDB.Remove(validation);
 }
 db.SaveChanges();
 return true;
}

my (_ModulID) is a collection of ids that i filter them and then with foreach delete them but it doesn't work ??

Answer 1

one way to delete is

context.Database.SqlQuery<WebSite_PermissionDB>(
 "DELETE FROM Users WHERE UserID = @userId ",
 new [] { new SqlParameter("@userId ", 1) }
);

Answer 2

First get the all entities that mathces with your condition, then use RemoveRange method:

var entities = db.WebSite_PermissionDB
 .Where(x => x.UserID == _UserID && _ModulID.Contains(x.ModuleID));
db.WebSite_PermissionDB.RemoveRange(entities);
db.SaveChanges();

Also you might want to use using statement in order to make sure that your DbContext is Disposed properly.

using(MPortalContext db = new MPortalContext())
{ 
 ...
} 

• A.A: In my code : dont know RemoveRange & Contains . But i use nameSpaces :using System.Data.Entity; & using System.Data.Objects.DataClasses; ??but dont know.

Answer 3

var distinctResult = db.WebSite_PermissionDB.Distinct();
db.WebSite_PermissionDB.RemoveRange(db.WebSite_PermissionDB.Except(distinctResult));
db.SaveChanges();

• Dutts: Could you add some explanation for what this code sample does to your answer?
• Meysam Khoshbakht: first select data by removing duplicate rows(Distinct method), then remove all rows unless selected data (Except method) in previous code.

Answer 4

 MPortalContext db=new MPortalContext();
 foreach (var item in _ModulID)
 {
 var validation = db.WebSite_PermissionDB.Where(x => x.UserID == _UserID && x.ModuleID == item).FirstOrDefault();
 db.WebSite_PermissionDB.Remove(validation);
 db.SaveChanges();
 } 
 return true;

I forget use .FirstOrDefault(); now correct. Thank friends.