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

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

Does context.SecurityDataShares have any Delete or Remove methods on it? — StriplingWarrior
– StriplingWarrior, Commented Apr 18, 2012 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
– rlcrews, Commented Apr 18, 2012 at 17:11

StriplingWarrior · Accepted Answer · 2012-04-18 21:00:26Z

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.

edited Apr 18, 2012 at 21:00

answered Apr 18, 2012 at 16:56

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rlcrews Over a year ago

That was it. I was misreading the definition of the method. Thanks for the tip on the SP as well.

Erik Philips Over a year ago

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.

StriplingWarrior Over a year ago

@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.

Community · Accepted Answer · 2017-05-23 12:06:58Z

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.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Apr 18, 2012 at 17:05

Erik Philips

54.8k11 gold badges133 silver badges158 bronze badges

Comments

Ramanand Potdar · Accepted Answer · 2014-08-04 12:19:18Z

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.

answered Aug 4, 2014 at 12:19

Ramanand Potdar

311 silver badge8 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related