How to Remove Range after Add Range in same operation

user_2564537 0
Viewed 1764

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?

asp.net-core entity-framework sql-server linq c#
2 Answers

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

user_14106640

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:

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