Introduction

This is the second article from the series about how to improve your .NET application that uses Entity Framework. In this lesson I will show you how to reduce the number of connections and queries sent to database when updating database. I will show you a few good practices in some common cases. Let’s start.

Update single object

Many developers use the following pattern when updating data:

public async Task UpdateOneFiled()
        {
            var product = await GetProduct();
            if (product == null)
                return;

            product.ModifiedDate = DateTime.Now;

            using (var context = new ProdcutionContext())
            {
                var currentObject = await context.Product.FirstOrDefaultAsync(p => p.ProductID == product.ProductID);
                currentObject.ModifiedDate = product.ModifiedDate;
                context.SaveChanges();
            }
        }

Which we can describe as follows:

1. Retrieve data from user (GetProduct).
2. Get current version of an object.
3. Update current object with the data from user.
4. Save data.

Sounds familiar? Unnecessary step is of course getting current version of object from database. I’ve seen many tutorials and articles where the author suggests such a way. It probably comes from not knowing the method Attach() and the way how EF in a previous version was updating data. In the default scenario EF was updating all fields. Most of the time we update only some bunch of fields and we don’t need to check what the previous version was. Imagine that we have a kind of an application for managing goods. We can change its name, color, cost and last modification date should change after each update:

public async Task UpdateSeveralFiledWithAttach()
        {
            var product = await GetProduct();
            if (product == null)
                return;

            using (var context = new ProdcutionContext())
            {
                context.Product.Attach(product);
                context.Entry(product).State = EntityState.Unchanged;
                context.Entry(product).Property(x => x.Name).IsModified = true;
                context.Entry(product).Property(x => x.Color).IsModified = true;
                context.Entry(product).Property(x => x.StandardCost).IsModified = true;
                context.Entry(product).Property(x => x.ModifiedDate).IsModified = true;
                await context.SaveChangesAsync();
            }
        }

Now our scenario looks like:

1. Retrieve data from user (GetProduct).
2. Mark object as tracked by data base context (Attach).
3. Set object as unchanged.
4. Set only needed fields as changed.
5. Save data.

We should remember that the object “product” must have a proper ID. Checking the Profiler we can see that only the fields set as changed are updated. It’s great! In Profiler we can see what is happening:

Update bigger object

In this scenario we need to update an order. The order has some attributes like date of order and list of position. Let’s assume that client can create order within a few days. He can save the order again and again modifying the list of positions:

1. Adding new positions.
2. Removing previously added positions.
3. Modifying previously added positions.

Unfortunately the most common scenario looks like that:

1. Get order from client.
2. Get the list of positions that should by modified.
3. Get the list of positions should be added.
4. Get the list of positions that should be removed from database.
5. Modify list of positions from step 2.
6. Add list of positions from step 3.
7. Remove list of object from step 4.

The code looks like that:

public async Task UpdateWrongWay()
        {
            var order = await GetOrder();

            using (var context = new ProdcutionContext())
            {
                //prepare data
                var elementsToUpdate = order.OrderItems.Where(x => x.SalesOrderDetailID > 0).ToList();
                var elementsToAdd = order.OrderItems.Where(x => x.SalesOrderDetailID == 0).ToList();
                var elementsToDelete = (await context.OrderItem
                    .Where(x => x.SalesOrderID == order.SalesOrderID)
                    .ToListAsync())
                    .Where(x => !order.OrderItems.Any(item => item.SalesOrderDetailID == x.SalesOrderDetailID)).ToList();

                //update
                foreach (OrderItem itemToUpdate in elementsToUpdate)
                {
                    var item = await context.OrderItem.FirstOrDefaultAsync(x => x.SalesOrderDetailID == itemToUpdate.SalesOrderDetailID);
                    item.OrderQty = itemToUpdate.OrderQty;
                }

                //add
                foreach (OrderItem itemToAdd in elementsToAdd)
                    context.OrderItem.Add(itemToAdd);

                //remove
                foreach (OrderItem itemToDelete in elementsToDelete)
                    context.OrderItem.Remove(itemToDelete);

                await context.SaveChangesAsync();
            }
        }

It isn’t the worst solution, I have seen the code where elements to update where retrieved from database using query or using SaveChanges() method after each data manipulation like updating object, inserting and deleting.

First of all we should consider if this is a proper situation when the order can be changed by two different users. In the overwhelming number of cases the answer is no. So if a user starts editing order nothing will be changed. Therefore we can modify data model sending data from client for extra property “DeleteByUser”. It will inform us that the user has deleted such position from order. We should also modify our GUI to send back to the server a list of items deleted by the user, but only those that were previously read from the database.

Now our scenario looks like:

1. Get the order from the client.
2. Filter the list of positions that should by modified.
3. Filter the list of positions should be added.
4. Filter the list of positions that should be removed from database.
5. Modify the list of positions from step 2 without reading data.
6. Add the list of positions from step 3.
7. Remove the list of objects from step 4.

The code would look like as follows:

public async Task Optymized()
        {
            var order = await GetOrder();

            using (var context = new ProdcutionContext())
            {
                //prepare data
                var elementsToUpdate = order.OrderItems.Where(x => x.SalesOrderDetailID > 0 && !x.DeleteByUser).ToList();
                var elementsToAdd = order.OrderItems.Where(x => x.SalesOrderDetailID == 0).ToList();
                var elementsToDelete = order.OrderItems.Where(x => x.DeleteByUser).ToList();


                //update
                context.OrderItem.AttachRange(elementsToUpdate);
                foreach (OrderItem itemToUpdate in elementsToUpdate)
                {
                    context.Entry(itemToUpdate).State = EntityState.Unchanged;
                    context.Entry(itemToUpdate).Property(x => x.OrderQty).IsModified = true;
                    itemToUpdate.OrderQty = itemToUpdate.OrderQty;
                }

                //add
                foreach (OrderItem itemToAdd in elementsToAdd)
                    context.OrderItem.Add(itemToAdd);

                //remove
                foreach (OrderItem itemToDelete in elementsToDelete)
                    context.OrderItem.Remove(itemToDelete);
                
                await context.SaveChangesAsync();
            }
        }

Now we don’t need to query database for elements to delete. We get rid of reading database for actual version of each position to update.

Summary

If we modify only few properties we should do this like in a clean SQL – update only needed properties. Then we can get rid of retrieving current version from database. We can reach that by using method Attach and by manipulating state of object. Little change in frontend can help improve the way we check which elements from list we should delete. Don’t afraid of using method AttachRange().