How to query all tables that implement an interface

Question

I have implemented an interface for some of my entity classes:

public partial class Order : IReportable
{
   public string TableName { get { return "Order"; } }
}

public partial class Client: IReportable
{
 public string TableName { get { return "Client"; } }
}


public interface IReportable
{
    string TableName { get; }
}

Then I added this to the DbContext:

public virtual DbSet<IReportable> IReportable { get; set; }

When I try to query all the tables that implement this interface (as shown here):

var result = from reportabletable in db.IReportable
             where reportabletable.TableName == table_name
            select reportabletable

I get the following exception:

The type 'Report.DataAccess.IReportable' was not mapped. Check that the type has not been explicitly excluded by using the Ignore method or NotMappedAttribute data annotation. Verify that the type was defined as a class, is not primitive or generic, and does not inherit from EntityObject.

Answer 1

Accepted solution does not work in EF Core. Here is my first working draft

public IEnumerable<T> SetOf<T>() where T : class
{
    var firstType = AppDomain.CurrentDomain.GetAssemblies().SelectMany(x => x.GetTypes())
        .FirstOrDefault(type => typeof(T).IsAssignableFrom(type) && !type.IsInterface);
    if (firstType == null) return new List<T>();

    var dbSetMethodInfo = typeof(DbContext).GetMethod("Set");
    var dbSet = dbSetMethodInfo.MakeGenericMethod(firstType);

    IQueryable<T> queryable = ((IQueryable)dbSet.Invoke(this, null)).Cast<T>();

    return queryable.ToList().Cast<T>();
}

Then you could use like this

_dbContext.SetOf<ISomeInterface>();

More info here Expose method DbContext.Set(Type entityType)

Answer 2

First of all MarcGravell comment is on the money. Its up to you to know which table to query. Personally I go through list of poco types that implement an interface or have an custom attribute. But if you are keen to go via the DBContext only, here are some extensions that give you access to the "names". You will still need to access that part of the context afterwards one at a time.

Again you can do that via generics, but you can just go directly as you suggest.
You will need to iterate of a list of types. eg:

ReportRespository : BaseRespository where t : IReport

Check the assembly for Certain types and attributes eg

     /// <summary>
    /// POCOs that have XYZ Attribute of Type  and NOT abstract and not complex
    /// </summary>
    /// <returns></returns>
    public static List<Type> GetBosDirDBPocoList() {
        var result = new List<Type>();
        // so get all the Class from teh assembly that public non abstract and not complex
        foreach (var t in Assembly.GetExecutingAssembly().GetTypes()
                                  .Where(t => t.BaseType != null
                                              && t.IsClass
                                              && t.IsPublic
                                              && !t.IsAbstract
                                              && !t.IsComplexType()
                                              && t.GetMyAttribute() != null)) {


                result.Add(t);
            }
        }
        return result;
    }

     public static GetMyAttribute(this Type T) {
        var myAttr= T.GetCustomAttributes(true)
                      .Where(attribute => attribute.GetType()
                      .Name == "XYZAttr").Cast<BosDir>().FirstOrDefault();

        return myAttr;
    }

Extensions

 public static class DalExtensions {
    // DbSet Names is the plural property name in the context
    public static List<string> GetModelNames(this DbContext context) {
        var propList = context.GetType().GetProperties();
        return GetDbSetNames(propList);
    }

    // DbSet Names is the plural property name in the context
    public static List<string> GetDbSetTypeNames<T>() where T : DbContext {
        var propList = typeof (T).GetProperties();
        return GetDbSetNames(propList);
    }

    // DBSet Types is the Generic Types POCO name  used for a DBSet
    public static List<string> GetModelTypes(this DbContext context) {
        var propList = context.GetType().GetProperties();
        return GetDbSetTypes(propList);
    }

    // DBSet Types POCO types as IEnumerable List
    public static IEnumerable<Type> GetDbSetPropertyList<T>() where T : DbContext {
        return typeof (T).GetProperties().Where(p => p.PropertyType.GetTypeInfo()
                                                      .Name.StartsWith("DbSet"))
                         .Select(propertyInfo => propertyInfo.PropertyType.GetGenericArguments()[0]).ToList();
    }

    // DBSet Types is the Generic Types POCO name  used for a DBSet
    public static List<string> GetDbSetTypes<T>() where T : DbContext {
        var propList = typeof (T).GetProperties();
        return GetDbSetTypes(propList);
    }


    private static List<string> GetDbSetTypes(IEnumerable<PropertyInfo> propList) {
        var modelTypeNames = propList.Where(p => p.PropertyType.GetTypeInfo().Name.StartsWith("DbSet"))
                                     .Select(p => p.PropertyType.GenericTypeArguments[0].Name)
                                     .ToList();
        return modelTypeNames;
    }

    private static List<string> GetDbSetNames(IEnumerable<PropertyInfo> propList) {
        var modelNames = propList.Where(p => p.PropertyType.GetTypeInfo().Name.StartsWith("DbSet"))
                                 .Select(p => p.Name)
                                 .ToList();

        return modelNames;
    }
}

}

Answer 3

I would go for something like this:

Create this extension method

public static class DbContextExtensions
{
    public static IEnumerable<T> SetOf<T>(this DbContext dbContext) where T : class
    {
        return dbContext.GetType().Assembly.GetTypes()
            .Where(type => typeof(T).IsAssignableFrom(type) && !type.IsInterface)
            .SelectMany(t => Enumerable.Cast<T>(dbContext.Set(t)));
    }
}

And use it like this:

using (var db = new dbEntities())
{
 var result = from reportabletable in db.SetOf<IReportable>()
         where reportabletable.TableName == table_name
        select reportabletable
}

Answer 4

EF doesn't like mapping interfaces directly to tables. You can get around this by making using a generic Repository, as outlined Here!

Then use repository method and supply the Type of the table(s) you want to query. Something like: myRepo.GetAll<myClient.GetType()>();

Get the classes that inherit that interface and run the query for all of them:

var types = System.Reflection.Assembly.GetExecutingAssembly().GetTypes().Where(mytype => mytype .GetInterfaces().Contains(typeof(myInterface)));
foreach (var mytype in types)
 { // aggregate query results }

Hope this helps! There is probably a more graceful solution