Retrieve only base class from Entity Framework

If I have three classes in entity framework.

class Base {}

class Left : Base {}

class Right : Base {}

and I call DBContext.Bases.ToList();

This returns all instances of Base fully typed into their associated inherited types, as some people have noticed, the performance of EF on large inheritance structures is not great to say the least. My actual query in my project is 600 lines long, just for returning one entity and takes 2 seconds to generate.

They query runs much faster if you tell it which type to return, as it does not have to join across the whole structure. e.g.

DBContext.Bases.OfType<Left>.ToList();
or
DBContext.Bases.OfType<Right>.ToList();

However I now want to ONLY return the base class. Unfortunalty doing

DBContext.Bases.OfType<Base>.ToList(); 

does the same as DBContext.Bases.ToList();

It gets the WHOLE inheritance structure... Is there any way (without making a new type in EF) of ONLY returning the class Base when looking through the Base collection?

---

Sorry I cant log into my actual account...

Maybe I didnt make myself clear, I want to bring back all the objects (including Base, Left and开发者_Go百科 Right) but I only want the Base class to be returned, even if in the database they are actual Left and Right classes.

OFTYPE was a good suggestion but it filters out all my entities because none are the actual Base type. But I want to return only the Base type values in the Base type object.

Any ideas?

---

The GetType() is not understood by Entity Framework, but the keyword is does work. As such you can build an Expression and apply it to your query. The code here should work for EF5+ to add an extension method that you can call as: query.OfOnlyType<Base, SubTypeWithDescendants>(). (Or with the same two Type arguments if you need to, my hierarchy is more complicated than that though)

public static IQueryable<ReturnType> OfOnlyType<ReturnType, QueryType>
        (this IQueryable<QueryType> query)
        where ReturnType : QueryType {

    // Look just for immediate subclasses as that will be enough to remove
    // any generations below
    var subTypes = typeof(ReturnType).Assembly.GetTypes()
         .Where(t => t.IsSubclassOf(typeof(ReturnType)));
    if (subTypes.Count() == 0) { return query.OfType<ReturnType>(); }

    // Start with a parameter of the type of the query
    var parameter = Expression.Parameter(typeof(ReturnType));

    // Build up an expression excluding all the sub-types
    Expression removeAllSubTypes = null;
    foreach (var subType in subTypes) {
        // For each sub-type, add a clause to make sure that the parameter is
        // not of this type
        var removeThisSubType = Expression.Not(Expression
             .TypeIs(parameter, subType));

        // Merge with the previous expressions
        if (removeAllSubTypes == null) {
            removeAllSubTypes = removeThisSubType;
        } else {
            removeAllSubTypes = Expression
                .AndAlso(removeAllSubTypes, removeThisSubType);
        }
    }

    // Convert to a lambda (actually pass the parameter in)
    var removeAllSubTypesLambda = Expression
         .Lambda(removeAllSubTypes, parameter);

    // Filter the query
    return query
        .OfType<ReturnType>()
        .Where(removeAllSubTypesLambda as Expression<Func<ReturnType, bool>>);
}

I've only tested it on EF6.1 with a code-first model. It borrows heavily from Alex James' tip 35.

---

To answer the question that none of the above answers seem to take care of (that is, we are only filtering returned columns to only be the base type columns, but not filtering out the rows that have derived type information), there is a fairly straightforward way of doing this with anonymous types. See here for another stackoverflow question dealing with the specifics.

The idea is to do something like this:

db.BaseTypes.Select(o => new { Prop1 = o.Prop1, Prop2 = o.Prop2, ....})
.AsEnumerable()
.Select(a => new BaseType() { Prop1 = a.Prop1, Prop2 = a.Prop2, ...});

The Linq-to-Entities will return a list of anonymous objects, while the .AsEnumerable() returns you back to Linq-to-Objects and allows you to call new BaseType() with an object initializer list.

This has an unfortunate downside of being specific to the types. Somebody here at the office wants a generic one written, so I'll return soon and edit this answer with a fully generic version of this.

EDIT (tested, but not in production EntityFramework):

Thanks to this answer for the SelectDynamic code.

public static class QueryableExtensions {

    /// <summary>
    /// Constructs a query that only selects the columns that are actually in the type <typeparamref name="T"/> as public properties.
    /// 
    /// Useful for inherited types when you only want the base type information.
    /// </summary>
    /// <remarks>
    /// This function materializes the query. You'll want to call the where clauses BEFORE this call (since it is an optimization).
    /// </remarks>
    /// <typeparam name="T">Entity type.</typeparam>
    /// <param name="query">Source query.</param>
    /// <returns>An IEnumerable of items of type <typeparamref name="T"/>.</returns>
    public static IEnumerable<T> FilterColumnsByType<T>(this IQueryable<T> query) where T : new() {
        Type type = typeof(T);
        List<string> selectedProps = type.GetProperties().Select(p => p.Name).ToList();

        Tuple<IQueryable, Type> anonObjectTypePair = query.SelectDynamicAndType(selectedProps);
        IQueryable anonObjects = anonObjectTypePair.Item1;
        Type anonType = anonObjectTypePair.Item2;

        return anonObjects.Cast<object>().AsEnumerable().Select(ob => {
            var ret = new T();
            selectedProps.ForEach(p =>
                type.GetProperty(p).SetValue(ret, anonType.GetField(p).GetValue(ob)));
            return ret;
        });
    }

    /// <summary>
    /// Constructs a query that selects only the <paramref name="propNames"/> given and returns an <see cref="IQueryable"/> of dynamic objects with only the selected fields.
    /// 
    /// Also returns the type information of the dynamic objects.
    /// </summary>
    /// <param name="source">Source query.</param>
    /// <param name="propNames">The list of properties names to select.</param>
    /// <returns>A query of anonymous types defined by the supplied <paramref name="propNames"/> and the actual <see cref="Type"/> used to construct anonymous type.</returns>
    public static Tuple<IQueryable, Type> SelectDynamicAndType(this IQueryable source, IEnumerable<string> propNames) {
        Dictionary<string, PropertyInfo> sourceProperties = propNames.ToDictionary(name => name, name => source.ElementType.GetProperty(name));
        Type dynamicType = LinqRuntimeTypeBuilder.GetDynamicType(sourceProperties.Values);

        ParameterExpression sourceItem = Expression.Parameter(source.ElementType, "t");
        IEnumerable<MemberBinding> bindings = dynamicType.GetFields().Select(p => Expression.Bind(p, Expression.Property(sourceItem, sourceProperties[p.Name]))).OfType<MemberBinding>();

        Expression selector = Expression.Lambda(Expression.MemberInit(
                Expression.New(dynamicType.GetConstructor(Type.EmptyTypes)), bindings), sourceItem);

        return Tuple.Create(source.Provider.CreateQuery(Expression.Call(typeof(Queryable), "Select", new Type[] { source.ElementType, dynamicType },
                                 Expression.Constant(source), selector)), dynamicType);
    }


    /// <summary>
    /// Constructs a query that selects only the <paramref name="propNames"/> given and returns an <see cref="IQueryable{dynamic}"/> of dynamic objects with only the selected fields.
    /// </summary>
    /// <param name="source">Source query.</param>
    /// <param name="propNames">The list of properties names to select.</param>
    /// <returns>A query of anonymous types defined by the supplied <paramref name="propNames"/>.</returns>
    public static IQueryable<dynamic> SelectDynamic(this IQueryable source, IEnumerable<string> propNames) {
        return source.SelectDynamicAndType(propNames).Item1.Cast<dynamic>();
    }

    static class LinqRuntimeTypeBuilder {
        private static AssemblyName assemblyName = new AssemblyName() { Name = "DynamicLinqTypes" };
        private static ModuleBuilder moduleBuilder = null;
        private static Dictionary<string, Type> builtTypes = new Dictionary<string, Type>();

        static LinqRuntimeTypeBuilder() {
            moduleBuilder = Thread.GetDomain().DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run).DefineDynamicModule(assemblyName.Name);
        }

        private static string GetTypeKey(Dictionary<string, Type> fields) {
            string key = string.Empty;
            foreach (var field in fields.OrderBy(kvp => kvp.Key).ThenBy(kvp => kvp.Value.Name))
                key += field.Key + ";" + field.Value.Name + ";";

            return key;
        }

        private static Type GetDynamicType(Dictionary<string, Type> fields) {
            if (null == fields)
                throw new ArgumentNullException("fields");
            if (0 == fields.Count)
                throw new ArgumentOutOfRangeException("fields", "fields must have at least 1 field definition");

            try {
                Monitor.Enter(builtTypes);
                string className = GetTypeKey(fields);

                if (builtTypes.ContainsKey(className))
                    return builtTypes[className];

                TypeBuilder typeBuilder = moduleBuilder.DefineType(className, TypeAttributes.Public | TypeAttributes.Class | TypeAttributes.Serializable);

                foreach (var field in fields)
                    typeBuilder.DefineField(field.Key, field.Value, FieldAttributes.Public);

                builtTypes[className] = typeBuilder.CreateType();

                return builtTypes[className];
            } catch (Exception ex) {
                //log.Error(ex);
                Console.WriteLine(ex);
            } finally {
                Monitor.Exit(builtTypes);
            }

            return null;
        }

        public static Type GetDynamicType(IEnumerable<PropertyInfo> fields) {
            return GetDynamicType(fields.ToDictionary(f => f.Name, f => f.PropertyType));
        }
    }
}

---

Assuming you are able to use LINQ, could you use something along the lines of the following quick and dirty example?:

var result = from item in DBContext.Bases.ToList()
            where (!item.GetType().IsSubclassOf(typeof(Base)))
           select item;

---

Not sure about the performance differences, but i could imagine that this would be faster than loading all rows (when a lot of rows are in DB):

List<int> ids = DBContext.Rights.Select(x => x.Id).ToList();
ids.AddRange(DBContext.Lefts.Select(x => x.Id).ToList());
var bases = DBContext.Bases.Where(x => !ids.Contains(x.Id)).ToList();

---

You can use DbSet.SqlQuery:

DBContext.Bases.SqlQuery("select * from BaseTable").AsNoTracking().ToList();

Be aware that not using .AsNoTracking() will get you into hot water sooner or later (if there are derived types already loaded into the context you will get unique key violations/exceptions immediatelly).

---

I currently use the following LINQ extension, assuming sub-classes are located in the same assembly.

public static class MyLinqExtensions
{
    public static IQueryable<T> OfTypeOnly<T>(this IQueryable<T> query)
    {
        Type type = typeof (T);
        IEnumerable<Type> derivedTypes = Assembly
            .GetAssembly(type)
            .GetTypes()
            .Where(t => t.IsSubclassOf(type));

        return query.ExceptTypes(derivedTypes.ToArray());
    }

    public static IQueryable<T> ExceptTypes<T>(this IQueryable<T> query, params Type[] excludedTypes)
    {
        if (excludedTypes == null)
            return query;

        return excludedTypes.Aggregate(query,
            (current, excludedType) => current.Where(entity => entity.GetType() != excludedType));
    }
}

Usage:

var bases = DBContext.Bases.OfTypeOnly<Base>();