Trying to work with Entity Framework in a normalized database can pose a little learning curve as due to the intricacies of the ORM. As an example, most of my business objects have child collections, as I assume yours do as well. A user has phones, addresses, orders etc. When I call up a user, I usually want all these sub collections as well.
In most cases, my applications have a web service in between the repository and the user interface. This means that I’m working detached from the ORM. Usually having the Entities mapped to more well structured business objects in the Service layer as well.
So what happens when I call up a user and we want to modify an address, or add a new address? Well, let’s take a look.
First, I have a database with two tables that both have Primary Keys and a Foreign Key relationship.
The Address.CustId in the Addresses table is linked to the Customer.Id primary key. If I run a query on the database as below, you can see the data:
SELECT c.Id as CustID, c.Name, a.Id as AddId
, a.Address1, a.CustId
FROM [lookupsdemo].[dbo].[Customers] c
JOIN [lookupsdemo].[dbo].[Addresses] a
ON c.Id = a.CustId
The data this produces is:
So now let’s say I want to update an address. According the Microsoft, this is easy, just write some code like:
Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1601 Penn Ave", CustId = 2 };
_cust.Addresses.Add(_add);
Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;
_entities.SaveChanges();
Life should be good, after running this code. The data looks like:
What? Looks like the name was updated, but not the address. Well, it turns out that marking the parent object as EntityState.Modified does not have any effect on the child objects.
Let’s modify the code a bit and see what happens:
Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam 2" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1602 Penn Ave", CustId = 2 };
_cust.Addresses.Add(_add);
Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;
foreach(Models.Address _item in _cust.Addresses)
{
_entities.Addresses.Attach(_item);
_entities.Entry(_item).State = System.Data.Entity.EntityState.Modified;
}
_entities.SaveChanges();
Alright, after running this code, let’s see what we have:
Well now, we finally got what we wanted, Jim’s name and Address1 have been updated!
What if we went to add a new address you ask. Well, just for fun, I threw in the code like this:
Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam 2" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1602 Penn Ave", CustId = 2 };
Models.Address _add1 = new Models.Address() { Id = 0, Address1 = "1400 Fun Street", CustId = 2 };
_cust.Addresses.Add(_add);
_cust.Addresses.Add(_add1);
Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;
foreach(Models.Address _item in _cust.Addresses)
{
_entities.Addresses.Attach(_item);
_entities.Entry(_item).State = System.Data.Entity.EntityState.Modified;
}
_entities.SaveChanges();
Anybody want to bet a dollar on whether this worked? Well, you’re lucky, because you would have lost your dollar. This results in a System.Data.Entity.Infrastructure.DbUpdateConcurrencyException.
I’ve modified the code to use a marker to tell if the child collection items are modifications or additions, here I’m using the Id > 0 to assume these are mods, all others are adds.
Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam 3" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1603 Penn Ave", CustId = 2 };
Models.Address _add1 = new Models.Address() { Id = 0, Address1 = "1400 Fun Street", CustId = 2 };
_cust.Addresses.Add(_add);
_cust.Addresses.Add(_add1);
Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;
foreach(Models.Address _item in _cust.Addresses)
{
if (_item.Id > 0)
{
_entities.Addresses.Attach(_item);
_entities.Entry(_item).State = System.Data.Entity.EntityState.Modified;
}
else
{
_entities.Addresses.Add(_item);
}
}
_entities.SaveChanges();
This gets us everything we wanted. We get the parent modifications, as well as the address modification and the new address.
As you can see that Jim is happy with both of his addresses.
It’s a little more code than I would like to use, but in the long run, it’s still less than coding a SQLClient Command with stored procedures.