Entity Framework Code First - select based on type of child TPH class

Question

I am currently working with Entity Framework 4 on a project that is using Table Per Hierarchy to represent one set of classes using a single table. The way this works is that this table represents states and different states are associated with different other classes.

So you might imagine it to look like this, ignoring the common fields all states share:

InactiveState 
  has a -> StateStore

ActiveState 
  has a -> ActivityLocation

CompletedState
  has a -> CompletionDate

Each state has a collection of all the InventoryItems that belong to it.

Now each item in my inventory has many states, where the last one in the history is the current state. To save on lists I have a shortcut field that points to the current state of my Inventory:

public class InventoryItem : Entity
{

    // whole bunch of other properties

    public virtual ICollection<InventoryState> StateHistory { get; set; }

    public virtual InventoryState LastState { get; set; }

}

The first problem I am having is when I want to find, for example, all the InventoryItems which are in the Active state.

It turns out Linq-To-Entities doesn't support GetType() so I can't use a statement like InventoryRepository.Get().Where( x => x.LastState.GetType() == someType ). I can use the is operator, but that requires a fixed type so rather than being able to have a method like this:

 public ICollection<InventoryItem> GetInventoryItemsByState( Type state )
 {
     return inventoryRepository.Get().Where( x => x.LastState is state );
 }

I have to run some kind of if statement based on the type before I make the Linq query, which feels wrong. The InventoryItem list is likely to get large, so I need to do this at the EF level, I can't pull the whole list into memory and use GetType(), for example.

I have two questions in this situation, connected closely enough that I think they can be combined as they probably reflect a lack of understanding on my part:

Is it possible to find a list of items that share a child table type using Linq To Entities?
Given that I am not using Lazy Loading, is it possible to Include related items for child table types using TPH so that, for example, if I have an InactiveState as the child of my InventoryItem I can preload the StateStore for that InactiveState?

score 3 · Accepted Answer · answered Apr 18 '13 at 17:47

Is it possible to find a list of items that share a child table type using Linq To Entities?

I don't think it's possible in another way than using an if/switch that checks for the type and builds a filter expression using is T or OfType<T>. You could encapsulate this logic into an extension method for example to have a single place to maintain and a reusable method:

public static class Extensions
{
    public static IQueryable<InventoryItem> WhereIsLastState(
        this IQueryable<InventoryItem> query, Type state)
    {
        if (state == typeof(InactiveState))
            return query.Where(i => i.LastState is InactiveState);
        if (state == typeof(ActiveState))
            return query.Where(i => i.LastState is ActiveState);
        if (state == typeof(CompletedState))
            return query.Where(i => i.LastState is CompletedState);

        throw new InvalidOperationException("Unsupported type...");
    }
}

To be used like this:

public ICollection<InventoryItem> GetInventoryItemsByState(Type state)
{
   return inventoryRepository.Get().WhereIsLastState(state).ToList();
}

I don't know if it would be possible to build the i => i.LastState is XXX expression manually using the .NET Expression API and based on the Type passed into the method. (Would interest me too, to be honest, but I have almost no clue about expression manipulation to answer that myself.)

Given that I am not using Lazy Loading, is it possible to Include related items for child table types using TPH so that, for example, if I have an InactiveState as the child of my InventoryItem I can preload the StateStore for that InactiveState?

I am not sure if I understand that correctly but generally eager loading with Include does not support any filtering or additional operations on specific included children.

One way to circumvent this limitation and still get the result in a single database roundtrip is using a projection which would look like this:

var result = context.InventoryItems
    .Select(i => new
    {
        InventoryItem = i,
        LastState = i.LastState,
        StateStore = (i.LastState is InactiveState)
            ? (i.LastState as InactiveState).StateStore
            : null
    })
    .AsEnumerable()
    .Select(x => x.InventoryItem)
    .ToList();

If the query is a tracked query (which it is in the example above) and the relationships are not many-to-many (they are not in your example) the context will fixup the relationships when the entities are loaded into the context, that is InventoryItem.LastState and InventoryItem.LastState.StateStore (if LastState is of type InactiveState) will be set to the loaded entities (as if they had been loaded with eager loading).

Entity Framework Code First - select based on type of child TPH class

1 Answers1