4

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

enter image description here

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

Community
  • 1
  • 1
rlcrews
  • 3,482
  • 19
  • 66
  • 116
  • Does `context.SecurityDataShares` have any Delete or Remove methods on it? – StriplingWarrior Apr 18 '12 at 17:01
  • There is a Remove method on the SecurityDataShares but this looks as if removes the entity table and not the entity itself Example: context.SecurityDatShares.Remove(); – rlcrews Apr 18 '12 at 17:11

3 Answers3

6

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.

StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315
  • 1
    That was it. I was misreading the definition of the method. Thanks for the tip on the SP as well. – rlcrews Apr 19 '12 at 15:40
  • 2
    Be aware that delete hundreds, thousands or more rows all at once (`.Remove(); .Remove(); .SaveChanges();` vs `.Remove(); .SaveChanges(); .Remove(); .SaveChanges();`) with Entity Framework by default rolling up all the deletes in a transaction means that it can take considerable time and possibly cause a commandTimeout. – Erik Philips Jul 23 '13 at 19:24
  • 1
    @ErikPhilips: Yes, as I said, if you expect a large number of objects, a stored procedure might remove the elements all in one statement. If that won't work, I'd suggest grouping the items into batches and saving after every hundred or so changes. While calling `.SaveChanges()` after each `.Remove()` would prevent SQL timeouts, it would also take much, much longer because you'd have N database round-trips. Also, before breaking things into separate transactions you have to consider whether the system would be in a correct state if only some of the items were deleted. – StriplingWarrior Jul 24 '13 at 03:13
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.

Community
  • 1
  • 1
Erik Philips
  • 53,428
  • 11
  • 128
  • 150
0

you can also pass datatable to the stored procedure with database contain dynamic type table of your type and use that table into stored procedure for deleting matching rows from Database table.

Ramanand Potdar
  • 31
  • 8