Linq to Entities Remove without fetching

Question

Can this code:

using(DbContext db = new DbContext())
{
    IEnumerable<Setting> settings = db.Settings.Where(s=> s.UserId==Uid);
    db.Settings.RemoveRange(settings); 
}

Be written in some way that there would be no need for fetching first? something like:

using(DbContext db = new DbContext())
{
    db.Settings.Remove(s=> s.UserId==Uid); 
}

Answer 1

Of course it can be done:

If UserId is primary key:

using(DbContext db = new DbContext())
{
    Setting settingToRemove = new Setting { UserId = Uid };
    db.Entry(settingToRemove).State = EntityState.Deleted;
    db.SaveChanges();
}

If UserId is not the key you have to query database, but what you need is just get primary keys according to UserId.

using(DbContext db = new DbContext())
{
    List<int> settingIdsToRemove = db.Setting
        .Where(m => m.UserId == Uid)
        .Select(m => m.Id)
        .ToList();

    foreach(int settingId in settingIdsToRemove)
    {
        Setting settingToRemove = new Setting { Id = settingId };
        db.Entry(settingToRemove).State = EntityState.Deleted;
    }

    db.SaveChanges();
}

Answer 2

You can have a look at the EntityFramework.Extended Library which offers you to write the below query:

//delete all Settings where UserId matches
db.Settings.Where(s=> s.UserId == Uid).Delete();

Docs:

A current limitations of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Now in most scenarios this is just fine. There are however some scenarios where performance would suffer. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. Batch update and delete eliminates the need to retrieve and load an entity before modifying it.

Answer 3

If you don't want to spend time in foreach loop the only way to do it is

db.Database.ExecuteSqlCommand(..)

https://msdn.microsoft.com/en-us/library/system.data.entity.database.executesqlcommand(v=vs.113).aspx