Chapter 7. Persisting objects into the database

 

 

Now that you know how to handle entity lifecycles, you’re ready to take the next step: persisting modifications made to objects into the database.

In this chapter, we’ll discuss how to insert, update, and delete entities in both connected and disconnected scenarios. We’ll cover single-object updates, such as a customer, and complex graph updates, such as an order and its details. By the end of this chapter, you’ll be able to manage updates through Entity Framework.

 

Note

In this chapter, we won’t be discussing topics like transaction and concurrency management. They get a chapter of their own—chapter 8.

 

Let’s start our discussion with how the persistence process works.

7.1. Persisting entities with SaveChanges

Entity persistence is the process that stores entities data inside the database. Triggering this procedure is simple as invoking the SaveChanges method of the ObjectContext class (which is the class from which the OrderITEntities class in OrderIT inherits).

The following snippet shows an example of how to use SaveChanges to persist modifications made to a customer:

C#

var customer = from c in ctx.Companies
               where c.Id == 1
               select c;
customer.Name = "new name";
ctx.SaveChanges();

VB

Dim customer = From c In ctx.Companies Where c.Id = 1
customer.Name = "new name"
ctx.SaveChanges()

Using SaveChanges is easy. When it’s invoked, it internally iterates over the entities in the Modified, Deleted, and Added states, generates the appropriate SQL statements, and executes them against the database. In the next section, we’re going deeper into this subject.

SaveChanges performs all the plumbing required for persisting entities. Not only does it synchronize the state manager with the entities (as you learned in chapter 6), but it also detects dirty entities (entities in the Added, Modified, and Deleted states), starts the connection and the transaction with the database, generates the correct SQL and commits or rolls back everything depending on whether some exception occurred. Eventually, it removes deleted entities and sets added and modified ones to Unchanged. In the end, it’s a complex process that uses the database, the state manager, and SQL to accomplish its task, as you see in figure 7.1.

Figure 7.1. The steps performed by the SaveChanges method to persist entity state

Now let’s look at each step, starting with the first.

7.1.1. Detecting dirty entities

Dirty entities are entities that are in the Added, Modified, or Deleted state. As you learned in chapter 6, entities and their related entries in the state manager aren’t always in sync. The SaveChanges method needs to synchronize them.

This phase is the easiest one in the persistence process. The SaveChanges method invokes the DetectChanges method (introduced in chapter 6) so that entities and entries in the state manager are synchronized. When DetectChanges has finished its work, the SaveChanges method queries the state manager to retrieve entries for entities in the Added, Modified, and Deleted states so that it can persist them.

Here is an excerpt of the code in the SaveChanges method that’s executed during this phase:

C#

DetectChanges();
var entries = ObjectStateManager.GetObjectStateEntries(
  EntityState.Added | EntityState.Modified | EntityState.Deleted);

VB

DetectChanges();
Dim entries = ObjectStateManager.GetObjectStateEntries(
  EntityState.Added Or EntityState.Modified Or EntityState.Deleted);

The entries returned by the query to the state manager contains first the added entities, then the modified ones, and finally the deleted ones.

Our exploration of the state manager in the last chapter should make this step fairly easy to understand. Now we can move on to the second part.

7.1.2. Starting database transactions

At this point in the process, the SaveChanges method opens a connection to the database and starts a transaction. All commands that are executed in the following phase are executed in the context of this transaction.

 

Note

In chapter 8, you’ll learn how to customize the transaction management.

 

This phase is simple. Let’s move on and discuss the most complicated one.

7.1.3. SQL code generation and execution

SQL code generation is pretty complex compared with detecting the dirty entities or starting the transaction. Here the entries returned from the call to the GetObject-StateEntries method is iterated through, and for each entity in it, the appropriate SQL code is generated.

When it comes to persisting entities in Added state, generating the SQL code isn’t difficult. A simple INSERT SQL statement is created.

The situation gets more complicated with entities in the Modified state. Because the state manager keeps track of both the original and current values of each loaded entity, Entity Framework generates UPDATE commands that modify only the changed properties. This is a great optimization that saves you a lot of work. Isn’t Entity Framework lovely?

Probably the simplest SQL code to generate is the DELETE commands for entities in the Deleted state. The SQL code deletes an object using its key properties.

The SQL generation and execution is an iterative process because statements often need data from previous ones. For example, think about the order and its details. To insert each detail, Entity Framework needs the ID of the order saved previously, so the SQL for the order detail is generated only after the order has been persisted.

7.1.4. Database transaction commit or rollback

If every SQL command executed in the previous phase is executed correctly, the database transaction is committed. But if an error occurs during the execution of a SQL statement, the transaction is rolled back.

An error can occur for several reasons. For instance, the database can be temporarily down or there can be a network problem. Those are common hardware problems, and software problems are treated exactly the same way. A duplicate key or a notnullable column receiving a null value causes the execution flow to terminate and the transaction to roll back.

Those sorts of errors are raised by the database, but there’s another type of error that causes the persistence process to stop suddenly, even if the database doesn’t raise an exception: a concurrency exception. This is a software exception, and it’s raised by Entity Framework itself. We’ll discuss concurrency in chapter 8.

7.1.5. Committing entities

By default, if the transaction terminates correctly, the context invokes the AcceptAll-Changes method, which internally invokes the AcceptChanges method of each dirty entry in the state manager. After its invocation, the entries are in sync with both the entities and the database, so you can think of this as a commit for entities. Entities in Added or Modified state become Unchanged, and entities in Deleted state are removed from the context (and the state manager).

You can manipulate this commit process by using an overload of SaveChanges that accepts an enum of type SaveOptions (namespace System.Data.Objects). The enum has three possible values:

• None—Neither the DetectChanges nor the AcceptAllChanges methods are invoked.
• AcceptAllChangesAfterSave—The AcceptAllChanges method is invoked.
• DetectChangesBeforeSave—The DetectChanges method is invoked before saving changes.

The enum is a flag, so you can combine the values.

 

Note

When no parameter is passed to SaveChanges, both the Accept-AllChangesAfterSave and DetectChangesBeforeSave values are automatically passed.

 

Earlier we mentioned that if an error occurs during persistence, the transaction rolls back. In that case, AcceptAllChanges isn’t triggered, so if you invoke the SaveChanges method again, the context will try to persist the entities again, the same way it did when the exception occurred because nothing has been touched.

If no error occurs during persistence and the AcceptAllChangesAfterSave value isn’t passed to SaveChanges method, the entities and the entries in the state manager remain untouched so a new call to SaveChanges would trigger persistence again.

7.1.6. Overriding SaveChanges

The SaveChanges method is virtual. You can override it in the context to inject any logic you need or even to completely override the persistence logic. Overriding SaveChanges is useful for building a logging system, as you saw in section 6.3.2. It could also be used to invoke a method on the entity, notifying it when it’s updated, added, or deleted. That’s not hard to do, especially if this method is exposed through an interface or a base class.

 

Note

If you don’t want to completely override the SaveChanges logic, remember to invoke the base implementation.

 

Now you have all the basics necessary to understand how Entity Framework persists entities. In the next section, we’ll see some of these concepts in action and look at how they affect the database.

7.2. Persisting changed entities into the database

Persisting a single entity means saving it into the database. You have three ways to do this:

• Persisting it as a new row
• Using its properties to update an existing row
• Using its key properties to delete an existing row

We’ll look at each of these options in this section.

In chapter 4, we introduced a user as the tester of OrderIT. In the next sections, this user will return to simulate a real-world scenario.

7.2.1. Persisting an entity as a new row

The first thing the user wants to do is create customers, suppliers, and products. You made the user happy about data retrieval, and you’ll make the user happy about data insertion too.

The first thing you need to do is allow the user to create customers. This is extremely easy to do: you can use the AddObject method, pass the Customer instance, and then invoke SaveChanges. Here’s what it looks like.

Listing 7.1. Persisting a new customer

C#

var cust = new Customer()
{
  Name = "Stefano Mostarda",
  BillingAddress = new AddressInfo()
  {
    Address = "5th street",
    City = "New York",
    Country = "USA",
    ZipCode = "0000000"
  },
  ShippingAddress = new AddressInfo()
  {
    Address = "5th street",
    City = "New York",
    Country = "USA",
    ZipCode = "0000000"
  },
  WSEnabled = true,
  WSUserName = "user1",
  WSPassword = "user1pwd"
};
ctx.Companies.AddObject(cust);
ctx.SaveChanges();

VB

Dim cust As new Customer() With
{
  .Name = "Stefano Mostarda",
  .BillingAddress = new AddressInfo() With
  {
    .Address = "5th street",
    .City = "New York",
    .Country = "USA",
    .ZipCode = "0000000"
  },
  .ShippingAddress = new AddressInfo() With
  {
    .Address = "5th street",
    .City = "New York",
    .Country = "USA",
    .ZipCode = "0000000"
  },
  .WSEnabled = true,
  .WSUserName = "user1",
  .WSPassword = "user1pwd"
}
ctx.Companies.AddObject(cust)
ctx.SaveChanges()

This listing shows how you can create a customer. Because the primary-key property is an identity, you don’t need to set it; if you do, the value will be ignored.

 

Note

In the event that the key isn’t an identity, you need to set it, or you’ll get an InvalidOperationException at runtime.

 

Despite its simplicity, the code generates more than a simple insert. To better understand some of the background, let’s look at the SQL that’s generated.

Listing 7.2. The SQL generated by the insert

The first thing that’s triggered is the insertion of the record . Then, after the record is added, the database-generated ID is retrieved . (The primary-key property is an Identity, which causes it to be assigned a database-generated ID.)

Notice that the discriminator column (Type) is automatically handled . Because a customer was added, the value of the column is set to C. If a supplier was added, the value would have been S—these are the values of the discriminator specified in the mapping. This is a great feature that lets you code using the OOP paradigm and leave Entity Framework to handle the transformation to the relational jargon.

The same paradigm applies to products. If you want to add a new Shirt, you create an instance, add it to the context, and then persist it. The SQL generated will insert the record into both the Product and Shirt tables. Again, you code using OOP and let Entity Framework worry about the database.

Persisting new entities isn’t tricky. Even if the persistence process involves complex stuff, it’s nicely handled by the context and kept hidden from you. Next, we’ll look at how to persist modifications to an entity.

7.2.2. Persisting modifications made to an existing entity

It’s 4 p.m. Friday, and the phone rings. The user has saved a customer with an incorrect name, and now the record needs to be modified.

Persistence in the Connected Scenario

The first path you can follow is to update data in a connected scenario—that’s when an entity is retrieved from the database and modified in the same context. In this scenario, you can modify the properties and then call the SaveChanges method, as follows.

Listing 7.3. Persisting a modified entity in the connected scenario

C#

var cust = ctx.Companies.OfType<Customer>()
  .First(c => c.CompanyId == 1);
cust.Name = "Stefano Mostarda";
ctx.SaveChanges();

VB

Dim cust = ctx.Companies.OfType(Of Customer)().
  First(Function(c) c.CompanyId = 1)
cust.Name = "Stefano Mostarda"
ctx.SaveChanges()

This code is pretty simple and needs no explanation. What’s more, because the state manager keeps track of the original and current values of entity properties, the SQL affects only columns mapped to modified properties. As a result, the UPDATE command is highly optimized, as you can see in the following snippet:

exec sp_executesql
N'update [dbo].[Company] set [Name] = @0 where ([CompanyId] = @1)',
N'@0 varchar(15), @1 int', @0='Stefano Mostarda', @1=13

There is an important point to understand here. When you modify a scalar property in a complex type, all properties of the complex type are persisted. For instance, if you modify the shipping address, the SQL will update the shipping city, country, and ZIP code even if they haven’t been modified. This happens because the state-manager entry considers a complex property to be a unique block. The modification of a single property in the block causes the entire block to be considered modified.

This connected scenario is by far the easiest one to code. In the disconnected scenario, things are more complex.

Persistence in the Disconnected Scenario

Suppose you have a method that accepts a Customer instance. You create a new context, attach the customer, and then persist it. The problem is that when you attach the entity to the context, it’s Unchanged, and SaveChanges won’t persist anything.

You have two ways to overcome this problem:

• Attach the input entity, and change its state to Modified by using the Change-ObjectState method.
• Query the database to retrieve the entity, and then use the ApplyCurrentValues method to overwrite properties of the database entity with the properties of the input entity.

The first solution is the simplest and the most used. It’s shown in the following listing.

Listing 7.4. Updating an entity using ChangeObjectState

C#

void UpdateCustomer(Customer cust)
{
  using (var ctx = new OrderITEntities())
  {
    ctx.Companies.Attach(cust);
    ctx.ObjectStateManager.ChangeObjectState(cust, EntityState.Modified);
    ctx.SaveChanges();
  }
}

VB

Sub UpdateCustomer(ByVal cust as Customer)
  Using ctx = New OrderITEntities()
    ctx.Companies.Attach(cust)
    ctx.ObjectStateManager.ChangeObjectState(cust, EntityState.Modified)
    ctx.SaveChanges()
  End using
End Sub

SQL

exec sp_executesql
N'update [dbo].[Company]
     set [Name] = @0, [BillingAddress] = @1, [BillingCity] = @2,
         [BillingZipCode] = @3, [BillingCountry] = @4,
         [ShippingAddress] = @5, [ShippingCity] = @6,
         [ShippingZipCode] = @7, [ShippingCountry] = @8, [WSUserName] = @9,
         [WSPassword] = @10, [WSEnabled] = @11
   where ([CompanyId] = @12)',
N'@0 varchar(15), @1 varchar(10), @2 varchar(8),@3 varchar(5),
  @4 varchar(3), @5 varchar(10), @6 varchar(8), @7 varchar(5),
  @8 varchar(3), @9 varchar(3), @10 varchar(3), @11 bit,
  @12 int',
@0='newCustomerName', @1='7th Avenue', @2='New York', @3='98765',
@4='USA', @5='7th Avenue', @6='New York', @7='98765', @8='USA', @9='US1',
@10='US1',@11=1,@12=1

The drawback to this approach is that ChangeObjectState marks the entity and all its properties as Modified. As a result, the UPDATE command will modify all the mapped columns, even if they weren’t really changed. If a property isn’t set, its value will overwrite the one in the database, causing data loss.

Let’s look at an example. Suppose you have a customer with ID 1. You create a Customer instance and set CompanyId to 1 and Name to NewName. You then attach the instance to the context, mark it as Modified, and call SaveChanges. The generated UPDATE command will update the Name column, and all the other columns will be assigned their default values (null for strings, 0 for integers, and so on), meaning that any previous data for that customer in the database is lost. Figure 7.2 illustrates this problem.

Figure 7.2. The billing address and city are empty in the persisted entity, so the database’s original values are lost after persistence.

Figure 7.2 clearly shows the problem that arises when using ChangeObjectState to set a partially loaded entity in the Modified state. As you can see, if all the properties are correctly set, this is a powerful technique that offers good performance and simplicity, but it can be dangerous if you don’t know the internals.

 

Note

You could argue that updating all columns, even those that weren’t modified, causes a slowdown in performance. In most databases, this cost is negligible. In fact, when the database has to perform an update, the biggest effort is in row retrieval. The column-update phase takes an insignificant percentage of the entire operation time.

 

The second solution for updating an object is pretty simple, even if it’s slightly more complex than the one based on ChangeObjectState. For this second solution, you query the database to retrieve the entity you want to modify, and then you use the ApplyCurrentValues method to apply the values from the modified entity. Finally, you invoke SaveChanges. When the modifications are persisted, the most optimized SQL is generated because the state manager knows which properties have been modified. Here’s the code for such a solution.

Listing 7.5. Updating an entity using ApplyCurrentValues

C#

void UpdateCustomerWithApplyCurrentValues(Customer cust)
{
  using (var ctx = new OrderITEntities())
  {
    ctx.Companies.OfType<Customer>()
      .First(c => c.CompanyId == cust.CompanyId);
    ctx.Companies.ApplyCurrentValues(cust);
    ctx.SaveChanges();
  }
}

VB

Private Sub UpdateCustomerWithApplyCurrentValues(ByVal cust As Customer)
  Using ctx = New OrderITEntities()
    ctx.Companies.OfType(Of Customer)().
      First(Function(c) c.CompanyId = cust.CompanyId)
    ctx.Companies.ApplyCurrentValues(cust)
    ctx.SaveChanges()
  End Using
End Sub

Unfortunately, ApplyCurrentValues suffers from the same problem as Change-ObjectState because it overwrites the current values of the entry, and the attached entity properties, with those from the input entity. If a property in the input entity isn’t set (if it’s null, empty, or set to the type’s default value), that property overwrites the current value of the entry, which is later used to update the database. The result, once again, is data loss.

ApplyCurrentValues suffers from another problem. It involves two round trips to the database: one to read data and one to update it. Performance can be hurt by this behavior. The problem gets even bigger when the data is separated in multiple tables, such as for the product data in OrderIT. Querying requires a join between tables, and updating can affect one or more tables, depending on what properties have been changed. If performance is critical, you’ll probably have to choose the ChangeObject-State approach, because it goes to the database only for the update process.

In the end, both approaches work in some cases but may cause data loss if misused. If you know in advance that some properties aren’t set and that you’ll lose data using one of these approaches, you have two options:

• Retrieve the customer from the database, and manually modify its properties one by one, based on the values in the input entity. This approach works well because it works like the connected scenario.
• Attach the input entity, and explicitly mark the properties to be updated. This approach eliminates both the need to go to the database to retrieve the entity and the risks of data loss.

The last option is the best one. It requires some code, but it’s effective because it offers the best performance while avoiding data loss.

Choosing What Properties to Update in the Disconnected Scenario

Let’s look at updating the customer information. The user wants to update the customer name and addresses (web service–related properties are updated in another dedicated form).

To do this, you can create a method that attaches the entity and marks the specified properties as Modified. This marking process automatically puts the entity in the Modified state too. When you invoke the SaveChanges method, only the specified properties are persisted into the database.

The method that marks a property as Modified is SetModifiedProperty. It accepts only one parameter, which represents the name of the property to be updated, as you can see in the following snippet:

C#

var entry = osm.GetObjectStateEntry(customer);
entry.SetModifiedProperty("Name");
entry.SetModifiedProperty("ShippingAddress");
entry.SetModifiedProperty("BillingAddress");

VB

Dim entry = osm.GetObjectStateEntry(customer)
entry.SetModifiedProperty("Name")
entry.SetModifiedProperty("ShippingAddress")
entry.SetModifiedProperty("BillingAddress")

We’ve now covered all the possible ways of updating an entity. The user can update customer information and is happy. But only for a couple of hours, because now the user needs to delete a customer.

7.2.3. Persisting entity deletion

Deleting an entity is a simple task. You already know that marking an entity as Deleted is a simple matter of invoking the DeleteObject method and passing in the entity. After the entity is marked, the related row in the database is deleted when the SaveChanges method is invoked.

Before being marked as Deleted, the entity must be attached to the context. This will be the case if the entity has been queried (in the connected scenario) or if it has been attached (in the disconnected scenario). The following listing shows examples of both techniques.

Listing 7.6. Deleting an entity in both connected and disconnected scenarios

Congratulations. You have successfully written code to modify customer data in any way the user may need. But that’s just the beginning. The next step is to write a new part of OrderIT: order management. This is a brand-new environment because you’ll be working with associations.

7.3. Persisting entities graphs

Order persistence requires you to work with up to four entities. Although you’ll update only the order and its details, the customer that places the order and the products in the details are equally involved as read-only data. Because more entities are involved, we talk about an entities graph (or objects graph); sometimes its persistence can be challenging, because entities in the same graph may need different actions. For instance, some may be added while others are modified or ignored.

To see these problems in practice and how to solve them, let’s get back to our user. To start with, the user needs a mask where they can choose the customer, fill in order data, and specify the products to be ordered.

7.3.1. Persisting a graph of added entities

When the user saves the order, the persistence process must be triggered. This is what you have to do:

1.  Create an Order instance.

2.  Associate the Order with a Customer.

3.  Create an OrderDetail instance.

4.  Associate the OrderDetail with a product.

5.  Add the OrderDetail to the Order.

6.  Repeat steps 3 to 5 for each detail.

7.  Add the Order to the context.

8.  Persist the modifications.

The code that performs these steps varies depending on whether you opt for foreign-key or independent associations, but the basic idea is the same. We’ll look at both options in turn.

Persisting A Graph of Added Entities Using Foreign-Key Associations

Thanks to the foreign-key feature, associating a customer with the order is pretty easy; you just have to set the foreign-key property with the primary key of the related entity. Setting the CustomerId property of the order to 1 automatically associates the order with the customer that has that ID. The same thing happens when you set the ProductId column of the detail.

As we mentioned, Entity Framework doesn’t support an object graph being partially loaded when an order is added—the details are added too. You can see this in the following listing.

Listing 7.7. Creating an order using foreign-key associations

C#

var order = new Order
{
  CustomerId = 1,
  OrderDate = DateTime.Now.Date
};
order.ShippingAddress = new AddressInfo()
{
  Address = "2th street",
  City = "New York",
  Country = "USA",
  ZipCode = "0000001"
};
var detail1 = new OrderDetail()
{
  ProductId = 2,
  Quantity = 3,
  UnitPrice = 10
};
var detail2 = new OrderDetail()
{
  ProductId = 1,
  Quantity = 5,
  UnitPrice = 10
};
order.OrderDetails.Add(detail1);
order.OrderDetails.Add(detail2);
ctx.Orders.AddObject(order);
ctx.SaveChanges();

VB

Dim order As New Order With
{
  .CustomerId = 1, _
  .OrderDate = DateTime.Now.Date
}
order.ShippingAddress = New AddressInfo()
{
  .Address = "2th street",
  .City = "New York",
  .Country = "USA",
  .ZipCode = "0000001"
}
Dim detail1 As New OrderDetail()
{
  .ProductId = 2,
  .Quantity = 3,
  .UnitPrice = 10
}
Dim detail2 As New OrderDetail()
{
  .ProductId = 1,
  .Quantity = 5,
  .UnitPrice = 10
}
order.OrderDetails.Add(detail1)
order.OrderDetails.Add(detail2)
ctx.Orders.AddObject(order)
ctx.SaveChanges()

Persisting a new order isn’t difficult thanks to foreign keys. They don’t require you to load the associated customer and products. You only work with the Order and Order-Detail entities.

Foreign keys are simple to use, but they’re not the only way to maintain relationships between entities. During model design, you may decide not to use foreign keys and to rely on independent associations instead. Or perhaps you’re upgrading an application to Entity Framework 4.0 from Entity Framework 1.0, which has no concept of foreign keys.

Persisting A Graph of Added Entities Using Independent Associations

When you use an independent association, the CustomerId column of the Order table is mapped to the CompanyId property of the Customer class. There’s no CustomerId property in the Order class. As a result, to associate a customer with an order, you have to create a customer instance and associate it with the order. The same process applies to the association between the details and their product.

Because you need only the ID of the customer and the ID of the product, instead of retrieving them from the database, you can create an instance (a stub) for each of them, set the ID, and then associate the instances with the order and the details. The following listing contains a first draft of the code for this technique.

Listing 7.8. Creating an order using independent associations

C#

var cust = new Customer() { CompanyId = 1 };
var product1 = new Product() { ProductId = 1 }
var product2 = new Product() { ProductId = 2 };

var order = new Order
{
  Customer = cust,
  OrderDate = DateTime.Now.Date
};
order.ShippingAddress = new AddressInfo()
{
  Address = "2th street",
  City = "New York",
  Country = "USA",
  ZipCode = "0000001"
};
var detail1 = new OrderDetail()
{
  Product = product1,
  Quantity = 3,
  UnitPrice = 10
};
var detail2 = new OrderDetail()
{
  Product = product2,
  Quantity = 5,
  UnitPrice = 10
};
order.OrderDetails.Add(detail1);
order.OrderDetails.Add(detail2);

ctx.Orders.AddObject(order);
ctx.SaveChanges();

VB

Dim cust As New Customer() With { .CompanyId = 1 }
Dim product1 As New Product() With { .ProductId = 1 }
Dim product2 As New Product() With { .ProductId = 2 }
Dim order As New Order With
{
  .Customer = cust,
  .OrderDate = DateTime.Now.Date
}
order.ShippingAddress As New AddressInfo() With
{
  .Address = "2th street",
  .City = "New York",
  .Country = "USA",
  .ZipCode = "0000001"
}
Dim detail1 As New OrderDetail() With {
  .Product = product1,
  .Quantity = 3,
  .UnitPrice = 10
}
Dim detail2 As New OrderDetail() With
{
  .Product = product2,
  .Quantity = 5,
  .UnitPrice = 10
}
order.OrderDetails.Add(detail1)
order.OrderDetails.Add(detail2)

ctx.Orders.AddObject(order)
ctx.SaveChanges()

Take a moment to look through the code again and ask yourself, “Will this code work?” The answer is, “No.” It will raise an exception during the persistence phase. More precisely, you’ll receive an UpdateException whose InnerException is a Sql-Exception stating that you can’t insert a null value into the Name column of the Company table.

This happens because the customer is marked as Added, due to the AddObject method, so the persistence process tries to insert the customer too. Because you’ve set only the ID, the insert fails because the name is null, and the database doesn’t allow that.

To make everything work correctly, you have to use the ChangeObjectState method to mark the customer as Unchanged before calling SaveChanges. The same method must be invoked to set the products as Unchanged. This way, only the order and its details are persisted; the rest remains untouched. The following listing shows the code that must be added to listing 7.8 to make everything work.

Listing 7.9. Correctly creating an order with independent associations

C#

ctx.Orders.AddObject(order);
osm.ChangeObjectState(cust, EntityState.Unchanged);
osm.ChangeObjectState(product1, EntityState.Unchanged);
osm.ChangeObjectState(product2, EntityState.Unchanged);
ctx.SaveChanges();

VB

ctx.Orders.AddObject(order)
osm.ChangeObjectState(cust, EntityState.Unchanged)
osm.ChangeObjectState(product1, EntityState.Unchanged)
osm.ChangeObjectState(product2, EntityState.Unchanged)
ctx.SaveChanges()

As you can see, independent associations are harder to manage.

The user is happy for a while. But then the user discovers that when a new customer calls, the user can take the order but can’t associate it with the customer, because the new customer hasn’t been created yet. The user has to go back to the customers form, create the customer, and then reinsert the order. Instead, the user wants to be able to insert data about the new customer and the order in the same form.

Persisting A Graph of Entities in Different States

Achieving this goal is pretty easy. You can create a Customer instance with all the data, associate it with the order, and not set its state to Unchanged.

If you write the code directly in the form, you’ll probably have a flag indicating whether the customer is new or not. If you use an external method, you could pass that flag, but a more independent solution would be better. The problem is how to decide whether the customer must be inserted into the database or not without having a flag.

The ID is the key. In the case of foreign-key associations, if the foreign-key property is 0, the customer is new; otherwise, the customer already exists. In the case of independent associations, if the ID of the Customer entity is 0, the customer is new; otherwise, the customer exists.

 

Note

By following this simple rule, you can decide whether an instance is new or not and correctly persist any graph. If you have composed keys or you use GUIDs, another check might be needed, but the general idea remains the same. Using mapping information (metadata) you could even write an extension method that checks any graph in a generic way and set entity state automatically.

 

Persisting a graph of added entities isn’t difficult. The real challenge comes when you have to save modifications made to the objects—to do this, you have to manually detect what’s changed.

7.3.2. Persisting modifications made to a graph

You’ve just deployed the new version of OrderIT, and the user has called to congratulate you on the excellent job. Five minutes later, the user gets back to you saying that they’ve made a mistake in the shipping address of an order and need to change it. That’s pretty easy to do. It doesn’t even involve a graph, because you can update the order and ignore its details.

Soon the user is back again. A customer has changed their mind and needs more red shirts (ProductId 1), no shoes (ProductId 2), and a new green shirt (ProductId 3). What’s worse, the user discovered that the order has been erroneously associated with the wrong customer.

This is a big challenge. The data in the Order instance is untouched, but the association with the customer has changed. Some items in the details list must be changed or removed, and others must be added. Coordinating all the updates will require some effort.

Persisting Modifications Using Foreign-Key Associations

If you’re in a connected scenario, you read the data and show it on the form, and when the user saves the modifications, you apply them to the entities. In this case, everything is pretty simple, as the following listing shows, because you’re always connected to the context, which tracks any modifications.

Listing 7.10. Updating an order in a connected scenario

C#

order.ActualShippingDate = DateTime.Now.AddDays(2);

var product1 = new Product() { ProductId = 3 };
var detail1 = new OrderDetail()
{
  Product = product1,
  Quantity = 5,
  UnitPrice = 3
};
order.OrderDetails.Add(detail1);

order.OrderDetails[1].Quantity = 2;
ctx.OrderDetails.DeleteObject(order.OrderDetails[2]);
ctx.SaveChanges()

VB

order.ActualShippingDate = DateTime.Now.AddDays(2)

Dim product1 As New Product() With { .ProductId = 3 }
Dim detail1 As New OrderDetail() With
{
  .Product = product1,
  .Quantity = 5,
  .UnitPrice = 3
}
order.OrderDetails.Add(detail1)

order.OrderDetails(1).Quantity = 2
ctx.OrderDetails.DeleteObject(order.OrderDetails(2))
ctx.SaveChanges()

This code causes four operations to occur. The order shipping date is updated, a new detail is added, an existing quantity is modified, and another is deleted. Fairly easy to understand, isn’t it?

In a disconnected scenario, everything is more difficult. Suppose you’re putting persistence logic in an external method—it doesn’t know anything about what has been modified, added, or removed. It receives the order and then needs a way to discover what’s been changed. In a layered application, this is a common scenario.

The solution is simple. You query the database to retrieve the order and its details. After that, you use the ApplyCurrentValues method to update the order from the database with the value of the input order. Unfortunately, the ApplyCurrentValues method affects only the order, leaving the details untouched. To discover how the details have been modified, you have to use LINQ to Objects.

The added order details are in the input order but not in the order retrieved from the database. (Because their OrderDetailId property is 0, you could use that search condition to locate added order details too.) The removed order details are among those downloaded from the database, but they won’t have been received by the method. The order details in both the database and the input order may have been modified, and by using ApplyCurrentValues, you can easily find out. Figure 7.3 illustrates this technique.

Figure 7.3. An order and details with matching keys are updated. Details from the input order with a key of 0 have been added. Other details from the database order are deleted.

At the end of the matching process, the order you have read from the database is updated with the input data and is ready to be persisted. The following listing shows the overall code for this technique.

Listing 7.11. Updating an order in the disconnected scenario

C#

void UpdateOrder(Order order)
{
  using (var ctx = new OrderITEntities())
  {
    var dbOrder = ctx.Orders.Include("OrderDetails")
      .First(o => o.OrderId == orderId);
    var added = order.OrderDetails.Except(order2.OrderDetails);
    var deleted = order2.OrderDetails.Except(order.OrderDetails);
    var modified = order2.OrderDetails.Intersect(order.OrderDetails);
    ctx.Orders.ApplyCurrentValues(order);
    added.ForEach(d => dbOrder.OrderDetails.Add(d));
    deleted.ForEach(d => ctx.OrderDetails.DeleteObject(d));
    modified.ForEach(d => ctx.OrderDetails.ApplyCurrentValues(d));
    ctx.SaveChanges();
  }
}

VB

Sub UpdateOrder(ByVal order As Order)
  Using ctx as New OrderITEntities()
    Dim dbOrder = ctx.Orders.Include("OrderDetails").
      First(Function(o) o.OrderId = orderId)
    Dim added = order.OrderDetails.Except(order2.OrderDetails)
    Dim deleted = order2.OrderDetails.Except(order.OrderDetails)
    Dim modified = order2.OrderDetails.Intersect(order.OrderDetails)
    ctx.Orders.ApplyCurrentValues(order)
    added.ForEach(Function(d) dbOrder.OrderDetails.Add(d))
    deleted.ForEach(Function(d) ctx.OrderDetails.DeleteObject(d))
    modified.ForEach(Function(d) ctx.OrderDetails.ApplyCurrentValues(d))
    ctx.SaveChanges()
  End Using
End Sub

What’s lovely about this approach is that, thanks to foreign keys, the association with the customer can be modified too, because the foreign-key property is tracked as a scalar property and so is affected by ApplyCurrentValues. Naturally, the same behavior applies to the relationship between a detail and the product.

 

Note

The LINQ queries work only because these entities implement Equals and GetHashCode. If that’s not the case, you should pass an object that implements IEqualityComparer.

 

When independent associations are used, things are, once again, harder to figure out.

Persisting Modifications Using Independent Associations

When independent associations are involved, the code that modifies the order and its details remains the same. What’s different is the way you change the association with the customer.

Because you don’t have the foreign-key property, ApplyCurrentValues doesn’t change the association between the order and the customer. The only way to change the customer is to assign an instance representing the new customer to the Customer property of Order.

If the customer instance isn’t attached to the context, it will be associated to the context in the Added state. That’s clearly not what you need, because you don’t want to insert the customer but just change the customer associated with the order. You have three possible ways to do that:

• Retrieve the customer from the database, so that it’s already attached to the context, and assign it to the order.
• Associate the customer with the order, and then modify the entity state using the ChangeObjectState method.
• Attach the entity to the context, and then associate it with the order.

All of these options will work, so choosing one pattern or the other is a matter of personal taste and case-by-case circumstances. Naturally, you can change the association between an order detail and a product in the same way.

The following listing shows how to change the customer association using the third option. You’ve already seen the code for the other options. Note that the same code can be used to change the association between an order detail and a product.

Listing 7.12. Changing the customer of an order using independent associations

C#

var order = GetOrder();
ctx.Companies.Attach(order.Customer);
dbOrder.Customer = order.Customer;
ctx.SaveChanges();

VB

Dim Order = GetOrder()
ctx.Companies.Attach(order.Customer)
dbOrder.Customer = order.Customer
ctx.SaveChanges()

Persisting modifications made to objects is difficult, because you need to check lots of things. When it comes to object deletion, the situation is simpler.

7.3.3. Persisting deletions made to a graph

Our user has just received sad news—a customer has cancelled an order. As a consequence, the order must be removed from the database. In this scenario, the association type between the order, the customer, the details, and the products makes the difference.

Persisting Deletions Using Foreign-Key Associations

Once again, foreign-key associations make everything simple. In a connected scenario, you retrieve the order, call DeleteObject, and then call SaveChanges.

In a disconnected scenario, you create an order instance populating the ID, attach it to the context, invoke DeleteObject, and invoke SaveChanges, and the game is done. There’s no need for a graph here because details are useless. The following listing shows the simplicity of deleting a graph in the disconnected scenario.

Listing 7.13. Deleting an order with foreign-key associations, disconnected scenario

C#

void DeleteOrder(int orderId)
{
  using (var ctx = new OrderITEntities())
  {
    Order order = new Order() { OrderId = orderId };
    ctx.Orders.Attach(order);
    ctx.Orders.DeleteObject(order);
    ctx.SaveChanges();
  }
}

VB

Sub DeleteOrder(ByVal Int32 As orderId)
  Using ctx as New OrderITEntities()
    Dim order As New Order() With { .OrderId = orderId }
    ctx.Orders.Attach(order)
    ctx.Orders.DeleteObject(order)
    ctx.SaveChanges()
  End Using
End Sub

There is a little performance caveat you need to be aware of. Even if the EDM is aware of the cascade constraint in the database, the context will issue a DELETE command for each child entity attached to the context.

Suppose you have an order with 30 details. You may expect that one delete for the entire order is issued, but it’s not like that. In the conceptual schema, a cascade constraint between the order and order details is specified; so, if they’re attached to the context, they’re marked as deleted, and a delete for each of them is issued to the database.

Naturally, if you don’t have the delete-cascade constraint, you must retrieve all details and mark them as deleted. Unfortunately, this solution is error prone, because in the time between retrieving the details and their physical deletion, a new detail could be added, and the context would know nothing about it. This would result in a foreign-key error when deleting data from the database, because this detail wouldn’t be deleted and would remain an orphan when the order was deleted.

An alternative solution is to launch a custom database command that deletes all details and then lets the context issue a separate delete for the order. This spares the retrieval and deletion of all the details from the database, and this is good.

 

Note

You’re probably thinking that if you’re removing an order and someone else adds a detail in the meantime, there should be a concurrency check. In the next chapter, we’ll talk about that, but let’s ignore it for now.

 

In the end, if you use delete cascade, everything is simpler. The code doesn’t care about details, and database performance improves. Without the cascade, you have more control over the deletion process, but that means more code and slower performance.

That’s all there is to deleting entities using foreign-key associations. Independent associations are harder to manage because the relationships stored in the state manager claim their role.

Persisting Deletions Using Independent Associations

Conceptually speaking, deleting entities in a graph using independent associations is no different from deleting an entity in a graph using foreign-key properties. What changes is the code, because to delete an entity, the context requires all the one-to-one related entities to be attached. If you need to delete a customer, you don’t need orders because they’re on the many side. But if you need to delete an order, you need its customer, and this is weird because only the order ID should be needed.

The reason why deleting an order requires you to know the customer is that the state manager marks the relationship between the two entities as Deleted and translates this into SQL, adding the foreign-key column to the WHERE clause of the DELETE command for the order.

In the end, to delete an order, instead of issuing a statement like DELETE FROM Order WHERE OrderId = 1, the context emits DELETE FROM order WHERE OrderId = 1 AND CustomerId = 2. Unfortunately, this odd requirement is one of those things that you’ll have to live with.

In the connected scenario, the difference between independent and foreign-key associations doesn’t exist. When you retrieve the order, the state manager already knows about its customer and their relationship, so it has everything it needs.

In the disconnected scenario, you must attach the customer along with the order. This is trivial; all you need to do is create the order, associate the related customer, attach the order to the context, and then pass it to the DeleteObject method. Because the cascade constraint exists, details are automatically deleted too, as you can see in the following listing.

Listing 7.14. Deleting an order with independent associations

C#

void DeleteOrder(int orderId, int customerId)
{
  using (var ctx = new OrderITEntities())
  {
    var order = new Order() { OrderId = orderId };
    order.Customer = new Customer() { CompanyId = 1 };
    ctx.Orders.Attach(order);
    ctx.Orders.DeleteObject(order);
    ctx.SaveChanges();
  }
}

VB

Sub DeleteOrder(ByVal Int32 As orderId, ByVal Int32 As customerId)
  Using ctx as New OrderITEntities()
    Dim order As New Order() With { .OrderId = orderId }
    order.Customer = New Customer() With { .CompanyId = 1 }
    ctx.Orders.Attach(order)
    ctx.Orders.DeleteObject(order)
    ctx.SaveChanges()
  End Using
End Sub

If the delete cascade constraint doesn’t exist, the same considerations made for foreign keys apply. The only thing to point out is that in order to delete the details, their related products must be loaded too (just as the customer is required by the order). To remove an order without delete-cascade constraints, you have to load the whole graph.

That’s it. Now the user can manipulate orders any way they need. There’s only one thing that the user needs to do now: associate products with a supplier.

7.3.4. Persisting many-to-many relationships

Many-to-many relationships introduce nothing new. You have learned everything you need in the previous sections. To add a product to a supplier, you create the supplier and the product instances, add the product to the supplier’s list of products, and call SaveChanges.

The same result can be obtained the opposite way. You can create the supplier and product, add the supplier to the product’s list of suppliers, and invoke SaveChanges.

When you have to remove a product from a supplier in the connected scenario, you remove the product from the supplier’s list and call SaveChanges. In the disconnected scenario, you retrieve data from the database and compare it to the input data to identify the changes. As you can see, there’s nothing new here.

Now we can look at a new question. What happens if you wrongly associate an order with a customer that doesn’t exist? More generally, what happens when something goes wrong during persistence? And what if you need to execute a custom command? Those questions will be answered in the next section.

7.4. A few tricks about persistence

Many things may cause an error during persistence. A string that’s too long, a foreign-key violation, a not-nullable column set to null, a duplicate key, and so on—these are typical reasons for exceptions. Let’s look at how you can handle exceptions in code.

7.4.1. Handling persistence exceptions

Generally speaking, handling an exception is simple; you wrap the call to SaveChanges inside a try/catch block, and you’re done. In terms of exceptions caused by Entity Framework, the situation is similar, but you have one specific exception to catch: UpdateException. UpdateException contains information about the entry whose entity persistence caused the error.

The entry is exposed by the StateEntries property, which is of type ReadOnly-Collection<ObjectStateEntry>. The StateEntries property is a list because it returns an EntityEntry for the entity that caused the exception, plus all its related RelationshipEntry instances. You receive only one entity, because as soon as the problem is encountered, the persistence process stops and the current entry is passed to the exception.

Important information is stored in the InnerException too. Here you’ll find the raw SqlException raised by the managed provider.

If you mix up the information from the inner exception with that in the entries, you can build a log entry that is useful for understanding what went wrong. If you want to go further, you can build a form that gives the user enough information to understand what the problem was and how it can be solved (if possible). The following listing shows how to catch the exception and write it to a log.

Listing 7.15. Managing persistence exceptions

C#

try
{
  ...
  ctx.SaveChanges();
}
catch (UpdateException ex)
{
  Log.WriteError(ex.StateEntries, ex.InnerException);
}

VB

Try
  ...
  ctx.SaveChanges()
Catch ex As UpdateException
  Log.WriteError(ex.StateEntries, ex.InnerException)
End Try

Handling errors is simple; you can easily log information, rethrow the exception, display information to the user, or do whatever you need. We suggest wrapping SaveChanges in a method and invoking that when persisting entities. This way, you don’t have to clutter your code with try/catch blocks every time you save data.

The next feature about persistence that you must know is how to send custom commands to the database to update data. This turns out to be particularly useful when manipulating orders and their details.

7.4.2. Executing custom SQL commands

Entity Framework can persist any modifications, but there are cases where a custom SQL command simplifies things compared to having Entity Framework persist everything. Let’s see how a custom SQL command can help in the OrderIT application.

In OrderIT, there are always 0 items in stock. This value is never updated when the product is created or when an item is sold. That’s because in chapter 5, you mapped the AvailableItems property to the AvailableItems column in the Product table and set the property as Computed. This means the property is never used to update the mapped column in the database; when an entity is persisted, the computed column value is immediately queried from the database and put into the property.

Essentially, this approach relieves the context from updating the available items by delegating the update to the database or a custom SQL command like a stored procedure. This approach was necessary to correctly calculate the value.

To correctly calculate the available items, you need to modify the code you have already written to add the following actions:

1.  When you create the product, you let the context create the row in the database and then issue a SQL command that updates the AvailableItems column.

2.  When you create or update an order, if the detail is new, you issue a SQL command that subtracts the sold items from the number in stock; if the detail is removed, you issue a SQL command that adds the number of sold items to the number in stock; and if the detail is changed, you issue a SQL command that subtracts the old quantity and then adds the new quantity to the number in stock.

Why do it this way? Why not let Entity Framework persist the column? The reasons are two: concurrency and simplicity.

• Concurrency —Suppose two users create an order simultaneously. They both read the product data at the same time, and the first user then updates the data; the second user will subsequently be updating stale data. A concurrency check could be a solution, but this would be a waste of user time. The user shouldn’t have to reinsert the order because the number of items in stock has changed since the data was last read.
• Simplicity —Updating a product requires the product to be attached, even if you use foreign-key associations. What’s more, you have to update the Available-Items property and the overall entity state, resulting in more complex code. A manual UPDATE is more straightforward and less complex.

Performing the task this way is pretty simple. Before invoking SaveChanges, you take all the details in Modified state and add the quantity to AvailableItems. After SaveChanges, you take all the Added and Modified entities and subtract the quantity from AvailableItems. Finally, for each detail entity in Deleted state, you add the quantity to AvailableItems.

To execute a custom SQL command, you use the ObjectContext class’s Execute-StoreCommand method. This method has the same features as the ExecuteStore-Query<T> method you saw in chapter 4. You can use a numbered list as well as classic ADO.NET parameters in SQL code, and the parameter values can be passed as DbParameter or simple values. This listing shows how you can use such a method.

Listing 7.16. Executing a custom SQL command to update data

C#

ctx.ExecuteStoreCommand("Update Product set   AvailableItems = AvailableItems - 3 where productid = 1");

VB

ctx.ExecuteStoreCommand("Update Product set   AvailableItems = AvailableItems - 3 where productid = 1")

You learned in chapter 4 that embedding SQL in the application code isn’t always a good idea, because you tie the application to a specific database. This might not be problem in many situations, but a stored procedure can be more desirable. You’ll learn how to replace the preceding code with a stored procedure in chapter 10.

7.5. Summary

You now know how to persist objects into the database. Although the order-detail scenario is simple, it introduced lots of caveats and intricacies. Delete-cascade constraints, connected and disconnected scenarios, foreign keys and independent associations, and frequently updated columns are situations that you’ll often encounter during development. Now you’re ready to master them. It doesn’t matter whether it’s an order-detail scenario or different types of data: the concepts remain the same.

You’re almost ready to write a complete application, but we still have to cover two important concepts related to persistence: transaction management and concurrency checks. The first is particularly important when you need to launch custom commands that must be executed in the same transaction of the context. The second is essential to avoid updating stale data when multiple users work on the same entity at the same time. In the next chapter, we’ll cover these two subjects.