Archive for the ‘LINQ to Entity’ 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 !!!

Advertisements

Listed below are some issues and solution you may come across when trying access a LINQ to Entity layer from a WCF Service located in a separate project.

Problem:
Argument Exception: “The specified named connection is either not found in the configuration, not intended to be used with the EntityClient Provider, not valid.”

Argument Exception: The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid

Solution:

The class library which contains the EDMX file contains an app.config file with the entity connection string. When access this from the WCF layer, there is no reference to it unless you include it in the web.config of the WCF layer

To fix the problem, copy the connection string from the app.config to the ConnectionStrings section of the web.config of the WCF project.

However, on doing this, you may receive another exception message:

MetadataException: Unable to load the specified metadata resource.
MetadataException : Unable to load the specified metadata resource

connectionString=”metadata=res://*/Model.Project.csdl|res://*/Model.Project.ssdl|res://*/Model.Project.msl;

to

connectionString=”metadata=res://*/;