Entity Framework – Generically Fetching Entities by Primary Key

Recently I have come across a situation where it would be desirable to retrieve an entity based only on its type and its primary key.

One possible way of doing this would be to implement and add a Find method to a partial class of each entity type. This would be possible for a small project yet quickly becomes unmaintainable in large or even medium sized projects.

Neither can we implement a Find method as an extension method to EntityObject because that would require an instance of an EntityObject, which we don’t necessarily have.

The solution is to implement a generic find method that makes use of raw SQL queries inside either a helper class or a repository.

Let’s consider the signature of such a method:

public List<TEntity> Find<TEntity>(int[] keys) where TEntity : EntityObject

In order to fetch the entities of type TEntity that have the given primary keys we need to know the following:

• The table name corresponding to TEntity
• The name of the primary key field

Fortunately, we can get both pieces of information from the EntitySetBase for the entity of type TEntity

/// <summary>
/// Gets the EntitySetBase to which the specified entity type belongs.
/// </summary>
/// <remarks>social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/a8cef6de-3cd1-464f-9b19-865b9794fe09/</remarks>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
private EntitySetBase GetEntitySet<TEntity>() where TEntity : EntityObject
{
  Contract.Requires(_context != null, "Context not set");

  EntityContainer container =
    _context.MetadataWorkspace
      .GetEntityContainer(_context.DefaultContainerName, DataSpace.CSpace);

  EntitySetBase entitySet = container.BaseEntitySets
    .Where(item => item.ElementType.Name == typeof(TEntity).Name).FirstOrDefault();

  return entitySet;
}

We can now complete our Find method as follows:

/// <summary>
/// Retrieves entities of the specified type that have the specified primary keys.
/// </summary>
/// <typeparam name="TEntity">Type of entity to retrieve.</typeparam>
/// <param name="keys">Primary keys of the entities to retrieve.</param>
/// <remarks>This method will not work correctly with entities that have composite keys.</remarks>
/// <returns>Eagerly retrieved and deatched entities.</returns>
public List<TEntity> Find<TEntity>(int[] keys) where TEntity : EntityObject
{
  var set = GetEntitySet<TEntity>();

  var entitySetName = set.ElementType.Name;
  var entityPrimaryKey = set.ElementType.KeyMembers.First().Name;

  var command = string.Format("SELECT * FROM {0} WHERE {1} IN ({2})", entitySetName, entityPrimaryKey, string.Join(",", keys));

  var query = _context.ExecuteStoreQuery<TEntity>(command);

  return query.ToList();
}

Notice that there are several limitations to this approach that are left as an exercise to the reader:

• No support for composite keys
• No support for non-integer keys
• Merge options?
• Entity is not attached to the DomainContext

Posted in .NET, C#, Entity Framework