How to Remove Range after Add Range in same operation

Question

I need to RemoveRange (total records) and then add new data to table. In this I'm getting the following error:

A second operation started on this context before a previous operation completed. This is usually caused by different threads using the same instance of DbContext, however instance members are not guaranteed to be thread safe. This could also be caused by a nested query being evaluated on the client, if this is the case rewrite the query avoiding nested invocations.

Below is my code

using (var transaction = context.Database.BeginTransaction())
{
    List<Permission> permission = context.permissions.ToList();
    context.permissions.RemoveRange(permission);
    context.SaveChangesAsync();
    var permissions = new Permission[]
    {
        new Permission { PermissionId=1, Resource = "User Info", CanMenuItem = true ,Action= "Get", AppICON="User.jpg", WebICON="ft-users", APPURI="UserPage", WebURI="", IsActive=true,  Notify=false, UpdatedOn = System.DateTime.Now, SortBy = 1 },
        new Permission { PermissionId=2, Resource = "User", CanMenuItem = false ,Action= "Get", AppICON="User.jpg", WebICON="", APPURI="UserPage", WebURI="/user", IsActive=true, Parent=1, Notify=false, UpdatedOn = System.DateTime.Now, SortBy = 1 },
        new Permission { PermissionId=3, Resource = "User", Action= "Post", AppICON="", WebICON="", APPURI="", WebURI="", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now },
        new Permission { PermissionId=4, Resource = "User", Action= "Put", AppICON="", WebICON="", APPURI="", WebURI="", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now },
        new Permission { PermissionId=5, Resource = "User", Action= "Delete", AppICON="", WebICON="", APPURI="", WebURI="", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now },
    }

    context.permissions.AddRange(permissions);
    context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[Permission] ON");
    context.SaveChanges();
    context.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT [dbo].[Permission] OFF");
}

I want delete all data from table and add new data to table in same call?

Please use async await for `SaveChangesAsync()` method or rename it to `SaveChanges()`. I'm thinking this is causing the exception for you. — Richárd Baldauf, May 15 '20 at 05:50
@Richard Baldauf ...yes ur right thats cause ..now working fine using savechanges() — user2564537, May 15 '20 at 06:02
Nooo... you have a working async/await tree setup, why do a potentially long operation like this synchronously? Now you probably have a warning "does not contain await and will ..." because your method is still marked async but you don't do anything async — Caius Jard, May 15 '20 at 06:08
Btw ExecuteSqlCommand is obsolete. ExecuteSqlRaw must be used instead. — heuristican, May 15 '20 at 06:13
First `SaveChangesAsync` call starts running the operation in a different thread than the main thread. If it's not finished when it hits the `SaveChanges` call you get this error. To avoid this you must wait the first operation to complete. You have a couple options here as pointed out. If you want to preserve the `async` call you can `await` it. Or changing `SaveChangesAsync` to `SaveChanges` also would work because the next statement is not executed until it finishes. — heuristican, May 15 '20 at 06:23

Caius Jard · Answer 1 · 2020-05-15T06:06:47.953

As Richárd notes in the comments, by using SaveChangesAsync at the top without any technique to wait for it to complete you'll end up trying to run your two operations at the same time, because the code after it will certainly take less time to run than a "download all then delete".

Exchange it for SaveChanges (least preferable), or await it so that your code stops and waits for the delete to finish before the addition is executed (better), or remove it entirely and just save async once (probably best)

//this
context.permissions.RemoveRange(permission)(
var permissions = new Permission[]

//or this
context.permissions.RemoveRange(permission);
await context.SaveChangesAsync();
var permissions = new Permission[]

//or this
context.permissions.RemoveRange(permission);
context.SaveChanges();
var permissions = new Permission[]

Personally I would use the first and use async/await on the ultimate save, as this could be a long operation and you seem to have the async hierarchy set up

Also, if there are many rows in this table you might not want to download them all just to delete them as you do here. Have a read of Entity Framework. Delete all rows in table - there are ways to get the db to dump all the rows directly

heuristican · Answer 2 · 2020-05-15T06:46:17.343

While playing around I realized that the code also lacks commit. So I decided to post a full code.

using (var transaction = context.Database.BeginTransaction())
{
    List<Permission> permission = context.Permission.ToList();
    context.Permission.RemoveRange(permission);
    //context.SaveChanges();  // Not needed

    var permissions = new Permission[]
    {
        new Permission { PermissionId=1, Resource = "User Info", CanMenuItem = true ,Action= "Get", AppICON="User.jpg", WebICON="ft-users", APPURI="UserPage", WebURI="", IsActive=true,  Notify=false, UpdatedOn = System.DateTime.Now, SortBy = 1 },
        new Permission { PermissionId=2, Resource = "User", CanMenuItem = false ,Action= "Get", AppICON="User.jpg", WebICON="", APPURI="UserPage", WebURI="/user", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now, SortBy = 1 },
        new Permission { PermissionId=3, Resource = "User", Action= "Post", AppICON="", WebICON="", APPURI="", WebURI="", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now },
        new Permission { PermissionId=4, Resource = "User", Action= "Put", AppICON="", WebICON="", APPURI="", WebURI="", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now },
        new Permission { PermissionId=5, Resource = "User", Action= "Delete", AppICON="", WebICON="", APPURI="", WebURI="", IsActive=true, Notify=false, UpdatedOn = System.DateTime.Now },
    };

    context.Permission.AddRange(permissions);
    context.Database.ExecuteSqlRaw(@"SET IDENTITY_INSERT [dbo].[Permission] ON");
    context.SaveChanges();
    context.Database.ExecuteSqlRaw(@"SET IDENTITY_INSERT [dbo].[Permission] OFF");
    context.Database.CommitTransaction();
}

Changes:

First SaveChanges() removed as it's not needed at all.
Semi colon after Permission array initialization.
ExecuteSqlRaw call instead of obsolete ExecuteSqlCommand.
context.Database.CommitTransaction(); to commit the changes. Of course handling exceptions and transaction rollback must be considered as well.

How to Remove Range after Add Range in same operation

2 Answers2