Archive for the ‘Entity Framework’ Category

While I’ve been working with Version 1 of the Entity Framework I, amongst others, became very frustrated when trying to update foreign keys.  I realise that this article is probably out of date now as EF 4.0 was released a couple of weeks ago, which allows you access to foreign keys. Never the less, there might be some struggling out there with version 1.

Scenario : I was trying to update or insert a record using the entity framework but there were two references pointing to the same entity.

I was aware that you can get the entity reference property set it to null then re-attach it as seen in figure 1.

Figure 1
  1. /// <summary>
  2. /// Saves the non network device.
  3. /// </summary>
  4. /// <param name=”nonNetworkDeviceDto”>The non network device dto.</param>
  5. public void SaveNonNetworkDevice(NonNetworkDeviceDto nonNetworkDeviceDto)
  6. {
  7. using (var context = new AssetNetworkEntities2())
  8. {
  9. var changedDevice = TransformationHelper.ConvertNonNetworkDeviceDtoToEntity(nonNetworkDeviceDto);
  10. if (!nonNetworkDeviceDto.DeviceId.Equals(-1))
  11. {
  12. var originalDevice =
  13. context.NonNetworkDevices.Include(“Status”).Include(“NonNetworkType”).FirstOrDefault(
  14. d => d.DeviceId.Equals(nonNetworkDeviceDto.DeviceId));
  15. context.ApplyAllReferencedPropertyChanges(originalDevice, changedDevice);
  16. context.ApplyCurrentValues(originalDevice.EntityKey.EntitySetName, changedDevice);
  17. }
  18. else
  19. {
  20. var maxNetworkDevice = context.NonNetworkDevices.OrderBy(“it.DeviceId DESC”).First();
  21. changedDevice.DeviceId = maxNetworkDevice.DeviceId + 1;
  22. var status = changedDevice.Status;
  23. var nonNetworkType = changedDevice.NonNetworkType;
  24. changedDevice.Status = null;
  25. changedDevice.NonNetworkType = null;
  26. context.AttachTo(“DeviceStatuses”, status);
  27. if (nonNetworkType != null)
  28. {
  29. context.AttachTo(“NonNetworkTypes”, nonNetworkType);
  30. }
  31. changedDevice.Status = status;
  32. changedDevice.NonNetworkType = nonNetworkType;
  33. context.AddToNonNetworkDevices(changedDevice);
  34. }
  35. context.SaveChanges();
  36. }
  37. }

However, if you have two fields referencing the same lookup for the foreign key this method fails.

In figure 1 on line 9 I call a static method of the my TransformationHelper class ConvertNonNetworkDeviceDtoToEntity. This class’s purpose is to convert DTO’s to an entity and can be seen in the code below in Figure 2

Figure 2
  1. /// <summary>
  2. /// Converts the non network device dto to entity.
  3. /// </summary>
  4. /// <param name=”nonNetworkDeviceDto”>The non network device dto.</param>
  5. /// <returns>An entity of type <see cref=”NonNetworkDevice”/></returns>
  6. internal static NonNetworkDevice ConvertNonNetworkDeviceDtoToEntity(NonNetworkDeviceDto nonNetworkDeviceDto)
  7. {
  8. var nonNetworkDevice = new NonNetworkDevice
  9. {
  10. DeviceId = nonNetworkDeviceDto.DeviceId,
  11. Description = nonNetworkDeviceDto.Description ?? string.Empty,
  12. LocationDescription = nonNetworkDeviceDto.LocationDescription ?? string.Empty,
  13. IPOctet1 = nonNetworkDeviceDto.IPOctet1 ?? string.Empty,
  14. IPOctet2 = nonNetworkDeviceDto.IPOctet2 ?? string.Empty,
  15. IPOctet3 = nonNetworkDeviceDto.IPOctet3 ?? string.Empty,
  16. IPOctet4 = nonNetworkDeviceDto.IPOctet4 ?? string.Empty,
  17. AssetNumber = nonNetworkDeviceDto.AssetNumber ?? string.Empty,
  18. Owner = nonNetworkDeviceDto.Owner ?? string.Empty,
  19. Status = new DeviceStatus { Status = nonNetworkDeviceDto.Status.Status },
  20. LocationCode = nonNetworkDeviceDto.LocationCode
  21. };
  22. if (nonNetworkDeviceDto.NetworkType != null)
  23. {
  24. nonNetworkDevice.NonNetworkType = new NonNetworkType
  25. {
  26. TypeID = nonNetworkDeviceDto.NetworkType.TypeId,
  27. TypeName = nonNetworkDeviceDto.NetworkType.TypeName
  28. };
  29. }
  30. return nonNetworkDevice;
  31. }

This conversion is fine for an update, but if you try to insert you have to go through the procedure shown in Figure 1 of assigning the reference properties to null, attaching then reassigning, because if you don’t EF tries to add new records to the reference tables, resulting in a Duplicate Primary key error.

So, I created some extension methods to help me with this task. Figure 3 shows the reworked static method,with Figure 4 and 5 showing the reference properties

Figure 3
  1. /// <summary>
  2. /// Converts to non network device entity.
  3. /// </summary>
  4. /// <param name=”context”>The context of the entity .</param>
  5. /// <param name=”nonNetworkDeviceDto”>The non network device dto.</param>
  6. /// <returns>A converted <see cref=”NonNetworkDevice”/></returns>
  7. internal static NonNetworkDevice ConvertToNonNetworkDeviceEntity(this AssetNetworkEntities2 context, NonNetworkDeviceDto nonNetworkDeviceDto)
  8. {
  9. var isNew = nonNetworkDeviceDto.DeviceId.Equals(-1);
  10. var nonNetworkDevice = new NonNetworkDevice
  11. {
  12. DeviceId = nonNetworkDeviceDto.DeviceId,
  13. Description = nonNetworkDeviceDto.Description ?? string.Empty,
  14. LocationDescription = nonNetworkDeviceDto.LocationDescription ?? string.Empty,
  15. IPOctet1 = nonNetworkDeviceDto.IPOctet1 ?? string.Empty,
  16. IPOctet2 = nonNetworkDeviceDto.IPOctet2 ?? string.Empty,
  17. IPOctet3 = nonNetworkDeviceDto.IPOctet3 ?? string.Empty,
  18. IPOctet4 = nonNetworkDeviceDto.IPOctet4 ?? string.Empty,
  19. AssetNumber = nonNetworkDeviceDto.AssetNumber ?? string.Empty,
  20. Owner = nonNetworkDeviceDto.Owner ?? string.Empty,
  21. LocationCode = nonNetworkDeviceDto.LocationCode,
  22. Status =
  23. context.ConvertToDeviceStatusEntity(nonNetworkDeviceDto.Status, isNew),
  24. NonNetworkType =
  25. context.ConvertToNonNetworkTypeEntity(nonNetworkDeviceDto.NetworkType, isNew)
  26. };
  27. return nonNetworkDevice;
  28. }
Figure 4
  1. /// <summary>
  2. /// Converts to device status entity.
  3. /// </summary>
  4. /// <param name=”context”>The context.</param>
  5. /// <param name=”deviceStatusDto”>The device status dto.</param>
  6. /// <param name=”isNew”>if set to <c>true</c> [is new].</param>
  7. /// <returns>A minimal converted <see cref=”DeviceStatus”/> for foreign key reference</returns>
  8. private static DeviceStatus ConvertToDeviceStatusEntity(this AssetNetworkEntities2 context, DeviceStatusDto deviceStatusDto, bool isNew)
  9. {
  10. var status = isNew
  11. ? context.DeviceStatuses.First(ds => ds.Status == deviceStatusDto.Status)
  12. : new DeviceStatus { Status = deviceStatusDto.Status };
  13. return status;
  14. }
Figure 5
  1. /// <summary>
  2. /// Converts to non network type entity.
  3. /// </summary>
  4. /// <param name=”context”>The context.</param>
  5. /// <param name=”nonNetworkTypeDto”>The non network type dto.</param>
  6. /// <param name=”isNew”>if set to <c>true</c> [is new].</param>
  7. /// <returns>A minimal converted <see cref=”NonNetworkType”/> for foreign key reference</returns>
  8. private static NonNetworkType ConvertToNonNetworkTypeEntity(this AssetNetworkEntities2 context, NonNetworkTypeDto nonNetworkTypeDto, bool isNew)
  9. {
  10. if (nonNetworkTypeDto == null)
  11. {
  12. return null;
  13. }
  14. var nonNetworkType = isNew
  15. ? context.NonNetworkTypes.First(nt => nt.TypeID == nonNetworkTypeDto.TypeId)
  16. : new NonNetworkType
  17. {
  18. TypeID = nonNetworkTypeDto.TypeId,
  19. TypeName = nonNetworkTypeDto.TypeName
  20. };
  21. return nonNetworkType;
  22. }

Now, instead of calling the TransformationHelper class I directly access the extension method in Figure 3 from the context as seen in Figure 6, line 9

  1. /// <summary>
  2. /// Saves the non network device.
  3. /// </summary>
  4. /// <param name=”nonNetworkDeviceDto”>The non network device dto.</param>
  5. public void SaveNonNetworkDevice(NonNetworkDeviceDto nonNetworkDeviceDto)
  6. {
  7. using (var context = new AssetNetworkEntities2())
  8. {
  9. var changedDevice = context.ConvertToNonNetworkDeviceEntity(nonNetworkDeviceDto);
  10. if (!nonNetworkDeviceDto.DeviceId.Equals(-1))
  11. {
  12. var originalDevice =
  13. context.NonNetworkDevices.Include(“Status”).Include(“NonNetworkType”).FirstOrDefault(
  14. d => d.DeviceId.Equals(nonNetworkDeviceDto.DeviceId));
  15. context.ApplyAllReferencedPropertyChanges(originalDevice, changedDevice);
  16. context.ApplyPropertyChanges(originalDevice.EntityKey.EntitySetName, changedDevice);
  17. }
  18. else
  19. {
  20. var maxNetworkDevice = context.NonNetworkDevices.OrderBy(“it.DeviceId DESC”).First();
  21. changedDevice.DeviceId = maxNetworkDevice.DeviceId + 1;
  22. context.AddToNonNetworkDevices(changedDevice);
  23. }
  24. context.SaveChanges();
  25. }
  26. }

I now no longer need to set the references to null and reattach.

Lesson Learned: Wish I’d waited for EF4 !!!

Problem : You have two database tables which map on a one to one relationship with the same primary key. In turn, both of these table have foreign key relationships to other, different, tables. If you had control over the database, you would probably move all the columns into the same table. However, in this scenario, you don’t have any control over the database, and if you changed it, there are other existing application which use it.

Tow Entities sharing a primary key

Solution : After creating an entity model from the existing database you need to make one entity out of two tables and remap the existing association to the new entity columns.

As can be seen in the NetworkLocation and the Location entities both share the common primary key LocationCode. Our first step is to move the existing columns in NetworkLocation to the Location entity, this can be done using the following steps:

  1. Copy and paste all the columns, except LocationCode from NetworkLocation to Location
  2. Delete NetworkLocation from the diagram
  3. Right click on the Location entity and select table mapping
  4. in the mapping details window, scroll to the bottom where it says “Add a Table or View”
  5. Select the drop down and choose the table which mapped to the entity you have just deleted
  6. Remap the entity to properties to the underlying table, as can be seen below

remap the entitiy to old table

Now, as can be seen below, the entity properties have been moved, but the reference to the NMC entity has been lost. To allow us to remap this, we need to look at the underlying XML.

Properties of the entity have been moved

To do this, in solution explorer, right click on the edmx file and choose “open with”. In the displayed dialog box, choose “XML Editor” and you should now see the underlying XML.

One thing to remember is that when you delete the entity from the diagram, all you have done is delete it from the conceptual model. The relationship in storage model still exists.

So, bearing that in mind, we need to navigate to the conceptual model part of the the XML. Now, we need to add XML fragments, in four places as described below:

  1. Firstly, copy the existing AssociationSet from the Storage Model to the Conceptual Model in the entity container section and remove the word “Store” from the association attribute value. Then rename the EntitySet to the declared in the EntitySet Element (highlighted in yellow in the diagram below)
    Adding AssociationSet to XML
  2. Next, scroll to the bottom of the conceptual model and add in the association, just before the schema tag.
    re-adding association
    Note: the name in the type attribute should be the same as the name of your entity.
  3. Now, you have these two in place you should be able to open the model and see the two entities reconnected. Give this a try before going any further.

  4. Once you have, this relationship in place, you need to add the navigations back in. These need to be added in two places: to the location entity and to the NMC entity, as seen below
    Remapping naviation properties

    Okay, that’s us finished with the XML and we can do the rest through the GUI

  5. Select either of the navigation properties and remap the initial table columns to the entity by adding the table and choosing the columns from the drop down.
    re-map existing table to new associations