EF Core 6 UpdateRange Question?

[raysefo]

Hello friends,

Long time no see There is something I want to talk to you experts. In my Blazor Server application, I am uploading an excel and updating records like this.

public async Task UpdateReportAsync(List<OrderDetail> reports)
{
            _db.UpdateRange(reports);
            await _db.SaveChangesAsync();

}

After updating successfully, when I navigate to another razer page and display data, I am encountering the data unchanged. What is wrong with UpdateRange?

public async Task<IEnumerable<Order?>> GetAllOrders(ClaimsPrincipal user)
        {
            if (user.IsInRole("Administrators"))
            {
                var result = await _db.Orders
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .Include(c => c.Customer)
                    .OrderByDescending(s => s.Id)
                    .ToListAsync();
                return result;
            }

            return await _db.Orders
                .Where(u => u.DoneBy == user.Identity.Name)
                .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                .ThenInclude(v => v.Vendor)
                .Include(c => c.Customer)
                .OrderByDescending(s => s.Id)
                .ToListAsync();
        }

 

[raysefo]

Edit:​

I changed it like this, it seems working but it is not an efficient way. How can I improve it?

public async Task<IEnumerable<Order?>> GetAllOrders(ClaimsPrincipal user)
{
            List<Order> result;
            if (!user.IsInRole("Administrators"))
            {
                result = await _db.Orders
                    .Where(u => u.DoneBy == user.Identity.Name)
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .Include(c => c.Customer)
                    .OrderByDescending(s => s.Id)
                    .ToListAsync();
                foreach (var existingOrder in result
                             .Select(order => _db.Orders.Local.SingleOrDefault(o => o.Id == order.Id))
                             .Where(existingOrder => existingOrder != null))
                {
                    _db.Entry(existingOrder).State = EntityState.Detached;
                }

                return result;
            }
                
            result = await _db.Orders
                .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                .ThenInclude(v => v.Vendor)
                .Include(c => c.Customer)
                .OrderByDescending(s => s.Id)
                .ToListAsync();

            foreach (var existingOrder in result
                         .Select(order => _db.Orders.Local.SingleOrDefault(o => o.Id == order.Id))
                         .Where(existingOrder => existingOrder != null))
            {
                _db.Entry(existingOrder).State = EntityState.Detached;
            }

            return result;

}

 

[Skydiver]

Perhaps step back and explain why you needed to mark some objects as detached. In your original post you made it sound like nothing was being returned. With that latest code it seems like you are getting objects returned, but you have to mark part of the object graph as detached.

 

[raysefo]

As I tried to explain after I update the records with Excel, somehow I can not retrieve the updated records so I added this code where I am retrieving the records.

foreach (var existingOrder in result
                         .Select(order => _db.Orders.Local.SingleOrDefault(o => o.Id == order.Id))
                         .Where(existingOrder => existingOrder != null))
            {
                _db.Entry(existingOrder).State = EntityState.Detached;
            }

 

[Skydiver]

But what is the relationship of the your update code to your query code? Your update code is saving out OrderDetails, but your query is returning Orders.

I can see that your query is trying to also include OrderDetails, EF should be returning the same instances that you had passed in to update. But regardless, why would you need to detach the top level object that you got back, when the thing that was supposedly was not updating was a child object?

Is is possible that when you had the attached objects, you code elsewhere that is accidentally undoing the effects of your update?

For that matter you still haven't fully explained what you mean by

I can not retrieve the updated records

Does that mean that you are not getting any Orders back? Or does that mean that you are getting back Orders, but their OrderDetails are not the same as what you had updated?

If the latter have you verified that:
You are getting the same instances that you updated; and
The underlying database actually got updated.

 

[Skydiver]

Also, you mentioned changing pages. Are you sure that the _db instance between the two pages are the same instances? If not then any updates done in one db context will not be reflected in the other context. Recall that EF keeps track of entity instances in memory and that tracking is done within the db context instance.

 

[raysefo]

Also, you mentioned changing pages. Are you sure that the _db instance between the two pages are the same instances?

Very good point @Skydiver. Here is how I use db context.

program.cs

builder.Services.AddDbContextFactory<IMSContext>(options =>
    {
        options.EnableSensitiveDataLogging().UseSqlServer(builder.Configuration.GetConnectionString("InventoryManagement"));
    }
);

IMSContext:

 public class IMSContext : DbContext
    {
        public IMSContext(DbContextOptions options) : base(options)
        {

        }

 
        public DbSet<Order?> Orders { get; set; }
        public DbSet<OrderDetail> OrdersDetail { get; set; }

Update is done in OrderDetail Repo:

public class OrderDetailRepository : IOrderDetailRepository
    {
        private readonly IMSContext _db;

        public OrderDetailRepository(IMSContext db)
        {
            _db = db;
        }
        ...
        public async Task UpdateReportAsync(List<OrderDetail> reports)
        {
  
            _db.UpdateRange(reports);
            await _db.SaveChangesAsync();

retrieving is done in another repo:

public class OrderRepository : IOrderRepository
    {
        private readonly IMSContext _db;

        public OrderRepository(IMSContext db) { _db = db; }
        ...
        public async Task<IEnumerable<Order?>> GetAllOrders(ClaimsPrincipal user)
        {
            List<Order> result;
            if (!user.IsInRole("Administrators"))
            {
                result = await _db.Orders
                    .Where(u => u.DoneBy == user.Identity.Name)
                    .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                    .ThenInclude(v => v.Vendor)
                    .Include(c => c.Customer)
                    .OrderByDescending(s => s.Id)
                    //.AsNoTracking()
                    .ToListAsync();
                foreach (var existingOrder in result
                             .Select(order => _db.Orders.Local.SingleOrDefault(o => o.Id == order.Id))
                             .Where(existingOrder => existingOrder != null))
                {
                    _db.Entry(existingOrder).State = EntityState.Detached;
                }

                return result;
            }
                
            result = await _db.Orders
                .Include(d => d.OrderDetails.Where(od => od.IsActive == 1))
                .ThenInclude(v => v.Vendor)
                .Include(c => c.Customer)
                .OrderByDescending(s => s.Id)
                //.AsNoTracking()
                .ToListAsync();

            foreach (var existingOrder in result
                         .Select(order => _db.Orders.Local.SingleOrDefault(o => o.Id == order.Id))
                         .Where(existingOrder => existingOrder != null))
            {
                _db.Entry(existingOrder).State = EntityState.Detached;
            }

            return result;

        }

Is there anything that creates this situation?
Thank you.

 

[Skydiver]

Sorry, I am not familiar with how EF registers its classes with the IoC, nor are you showing how you are registering your repository classes.

Basically, if you can register them all as application lifetime (or worse singleton), and the problem goes away, then that increases the chances that my theory that you had two different contexts involved.

 

[cjard]

It's not a wise thing to do. See DbContext Lifetime, Configuration, and Initialization - EF Core

It's also wise to avoid a pattern of retrieving entities, and using them as DTOs, then reattach them to another context to save them.. but it's possible. Personally, if I was doing this in Blazor server (and I do do this in BS) I'd look at using something like borisdj's ef core bulk extensions so you can jsut instantiate a bunch of new objects from your excel and send them to the DB in a single op and the database will (likely) run a MERGE statement that makes the inserts and updates as necessary. If you're not doing it in bulk, look at tailoring a DTO for the UI, and map the DB object to it and back. You will still need to retrieve the object that you wish to update just before you update it, because you won't have a context long lived enough to experience the change (don't use the same context to downlaod the entity, wait for the user to make changes, then send them back; it's too long compared to the intended lifecycle of a context)

In essence, a BS "get, save" button pair might look like this:

private PersonDto Person{get;set;} //the UI components bind to this e.g. <MudTextField @bind-Text="Person.Name"

private async Task GetPerson(int id){ //mayeb the name and id are in a list and clicking a list item causes this to fire populating the form
    var context = _contextFactory.CreateContext(); 
    var p = await context.People.FindAsync(id);

    Person = _automapper.Map<PersonDto>(p);
}

private async Task SaveCurrentPerson(){ //mayeb the name and id are in a list and clicking a list item causes this to fire populating the form
    var context = _contextFactory.CreateContext(); //different context
    var p = await context.People.FindAsync(Person.Id);

    Person = _automapper.Map<PersonDto>(Person, p); //autoamapper updates db entity from DTO used by the UI
    await context.SaveChangesAsync();
}

 

[raysefo]

It's not a wise thing to do. See DbContext Lifetime, Configuration, and Initialization - EF Core

It's also wise to avoid a pattern of retrieving entities, and using them as DTOs, then reattach them to another context to save them.. but it's possible. Personally, if I was doing this in Blazor server (and I do do this in BS) I'd look at using something like borisdj's ef core bulk extensions so you can jsut instantiate a bunch of new objects from your excel and send them to the DB in a single op and the database will (likely) run a MERGE statement that makes the inserts and updates as necessary. If you're not doing it in bulk, look at tailoring a DTO for the UI, and map the DB object to it and back. You will still need to retrieve the object that you wish to update just before you update it, because you won't have a context long lived enough to experience the change (don't use the same context to downlaod the entity, wait for the user to make changes, then send them back; it's too long compared to the intended lifecycle of a context)

Can you please explain why this problem occurs in the first place? And thank you for your time to help.

 

[Skydiver]

I think what both @cjard and I are trying to tell you is that you likely have two different contexts and you are trying to use objects across the two of them.

 

[cjard]

Can you please explain why this problem occurs in the first place? And thank you for your time to help.

The problem of you sending an object for update and nothing in the DB changing? It's primarily because, unlike in days of old with things like a DataRow where the row itself maintained a memory of its original value for column X and its current value, it is the EF context that now maintains this memory of "what were the previous values of object X?". When you download X, make changes to it and then save, you don't need to specifically tell EF you've updated the object because it knows what it downloaded and started tracking. If you take your data class and use it for something else, some minutes long process where the user types new values, then the user sends the updated object back, you can't just attach it to a different EF context and do a Save, because the memory of what the original object looked like was kept with whe context that downloaded it.

You thus either have to download it again (not a bad idea in a concurrent world), transfer the info in and save the changes so that the same context does the download and upload.. or you have to pull yet another clumsy hack where you then tell the context to assume the new object has changed, at which point it will form an update statement that sets every field, even the ones that haven't changed.

These aren't the only strategies, but like I mentioned I would either do a download/upload and have the interim data storage object not be a db entity, or I would be doing a bulk process where I'd send all thousands of records to the db and get it to locally do an "insert or update depending on if it is already known" type process