How can you retrieve all records of certain subtype dynamically with Linq to Entities?

Question

I am trying to get a list of all objects in the database of a specified type. I have done this before when the type was known at compile time, but now I am trying to pass a type into the method and have the method return all the records of that specified type, and I can't get it working. I have tried the following:

    public IList<WritingObject> GetBasicObjectsByProject(int projectId, Type oType)
    {
        var results = from o in _objects.AsQueryable
                      where o.Project.Id == projectId
                      && o.GetType() == oType
                      select o;

        return results.ToList<WritingObject>();
    }

This didn't work because Linq to Entities doesn't support the GetType() method. Next I tried

        var results = from o in _objects.AsQueryable
                      where o.Project.Id == projectId
                      && o is oType
                      select o;

This doesn't work because the compiler claims that oType is not of a known type. Using typeof(oType) produces the same error, as does performing OfType<oType>() on the IQueryable.

I'm running out of ideas to keep this dynamic without splitting it out into one method per sub-type. Does anyone have any ideas?

Answer 1

Something like: ?

var query = from o in _objects.AsQueryable()
            where o.Project.Id == projectId
            select o;

var ofType = typeof (Queryable).GetMethod("OfType").MakeGenericMethod(oType);
var list = (IQueryable)ofType.Invoke(
            null, new object[] {query}).Cast<WritingObject>().ToList();

Note this will include other sub-sub-types too.

Answer 2

I think the issue here is that Linq-to-Entities is trying to translate o.GetType into SQL, which it obviously can't. After re-reading the question I realised that it's to do with linq to entities not being able to map a CLR type to a database type. Linq to Entities help talks about that:

The LINQ standard query operators that deal with CLR type conversion and testing are supported in the Entity Framework. Only CLR types that map to conceptual model types are supported in LINQ to Entities. For a list of conceptual model types, see Conceptual Model Types.

The above means that it can only convert a handful of supported CLR types into appropriate EDM types as a part of query generation. If the type you are trying to use is not on that list, you will need to filter on type after retrieving the query result.

The solution to this problem might be inefficient - you will need to request all records matching projectId criteria and then filter them by type:

    var results = (from o in _objects.AsQueryable
                  where o.Project.Id == projectId
                  select o).ToList(); //force query execution here

    results = from o in results 
              where o.GetType() == oType
              select o;

    return results.ToList<WritingObject>();