how to delete multiple rows of data with linq to EF using DbContext

Question

Within a project I have a database table with the following columns

I would like to be able to delete from this table all rows which have a matching SharingAgencyId and ReceivingAgencyId values that I can pass in.

What I have tried so far:

 public static ICollection<SecurityDataShare> UpdateSharedEntites(long conAgency, long recAgency)
        {
            ICollection<SecurityDataShare> agShares = null;
            try
            {
                using (var context = new ProjSecurityEntities(string.Empty))
                {
                    agShares = (from a in context.SecurityDataShares
                               .Where(c => c.ReceivingAgencyId == recAgency && c.SharingAgencyId == conAgency)
                                select a); 
                }
            }
            catch (Exception ex)
            {
                //ToDo
                throw;
            }
        }

My thought process was to retrieve the records where the id's matched the parameters passed in and then using a foreach loop iterate through (agShare) and remove each row followed by saving my changes. With the current implementation I don't seem to have access to any of the Delete methods.

Looking to the example above I'd appreciate any suggestions on how to remove the rows within the table that contained a value of 43 and 39 using dbContext.

Cheers

Answer 1

If I understand right, your DbContext's properties, like SecurityDataShares should be typed as IDbSet<SecurityDataShare>. If that's correct, you should be able to use this Remove method.

foreach(var agShare in agShares) {
    context.SecurityDataShares.Remove(agShare);
}
context.SaveChanges();

Be aware that this creates a separate SQL statement for deleting these objects. If you expect the number of objects to be rather large, you may want to use a stored procedure instead.

Answer 2

Entity Framework doesn't make it easy to run a single command to delete multiple rows (that I know of). My preference is to run a SQL statement directly for multi-entity updates/deletes using native sql with the dbcontext of sorts.