Dynamically access table in EF Core 2.0

Question

I am using System.Linq.Dynamic.Core to dynamically add in lambda expressions to queries in EF.

I want to also be able to select the table by name. I found this answer:

https://stackoverflow.com/a/28101268/657477

But it is not working in asp.net core 2.0. I cannot use DbSet I must use DbSet<TEntity> it says in error message.

I want to be able to do db.GetTable("Namespace.MyTable").Where(...)

How can I do this?

Answer 1

First you need to get the type of the entity from the name (in case you have the type, just use it directly). You can use reflection for that, but probably the correct way for EF Core is to use FindEntityType method.

Once you have the type, the problem is how to get the corresponding DbSet<T>. EF Core currently does not provide non generic Set(Type) method similar to EF6, mainly because there is no non generic DbSet class. But you can still get the corresponding DbSet<T> as IQueryable by either using some EF Core internals:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore.Internal;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomExtensions
    {
        public static IQueryable Query(this DbContext context, string entityName) =>
            context.Query(context.Model.FindEntityType(entityName).ClrType);

        public static IQueryable Query(this DbContext context, Type entityType) =>
            (IQueryable)((IDbSetCache)context).GetOrAddSet(context.GetDependencies().SetSource, entityType);
    }
}

or (preferable) invoking the generic Set<T> method via reflection:

using System;
using System.Linq;
using System.Reflection;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomExtensions
    {
        public static IQueryable Query(this DbContext context, string entityName) =>
            context.Query(context.Model.FindEntityType(entityName).ClrType);

        static readonly MethodInfo SetMethod = typeof(DbContext).GetMethod(nameof(DbContext.Set), Type.EmptyTypes);

        public static IQueryable Query(this DbContext context, Type entityType) =>
            (IQueryable)SetMethod.MakeGenericMethod(entityType).Invoke(context, null);
    }
}

In both cases you can use something like this:

db.Query("Namespace.MyTable").Where(...)

or

db.Query(typeof(MyTable)).Where(...)

Answer 2

EF Core no longer has a non generic Set method but This extension class makes it easy to query your table based on a string using dynamic linq. Consider TableTypeDictionary to be a Dictionary<string, Type> mapping DbSet types with their type names.

public static class DbContextExtensions
{
    public static IQueryable<Object> Set(this DbContext _context, Type t)
    {
        return (IQueryable<Object>)_context.GetType().GetMethod("Set").MakeGenericMethod(t).Invoke(_context, null);
    }


    public static IQueryable<Object> Set(this DbContext _context, String table)
    {
        Type tableType;
        //One way to get the Type
        tableType = _context.GetType().Assembly.GetExportedTypes().FirstOrDefault(t => t.Name == table);
        //The Second way, get from the dictionary which we've initialized at startup
        tableType = TableTypeDictionary[table];
        //The third way, works only if 'table' is an 'assembly qualified type name'
        tableType = Type.GetType(table);

        IQueryable<Object> objectContext = _context.Set(tableType);
        return objectContext;
    }
}

Usage:

IQueryable<Object> query = db.Set("TableName");
//or if you're using the third way:
query = db.Set(MyTable.GetType().AssemblyQualifiedName);

// Filter against "query" variable below...
List<Object> result = query.ToList();
// or use further dynamic Linq
IQueryable<Object> query = db.Set("TableName").Where("t => t.TableFilter == \"MyFilter\"");

Answer 3

For those of you like me reading this question now and on newer versions of Ef Core you can just do the following (ef core 5):

// Add extension method:
public static class DBContextExtensions
{
    public static IQueryable<Object> Get(this DbContext _context, Type t)
    {
        return (IQueryable<Object>)_context.GetType().GetMethod($"get_{t.Name}").Invoke(_context, null);
    }

    // Or the modified set which handles ambigous ref issues:
    public static IQueryable<Object> Set(this DbContext_context, Type t)
    {
        return (IQueryable<Object>)_context.GetType().GetMethods()
                .First(x => x.Name == "Set" && x.ContainsGenericParameters)
                .MakeGenericMethod(t).Invoke(_context, null);
    }
}

// Important using for string value in where clause of set query
using System.Linq.Dynamic.Core;

// Use get as follows
var type = MyDbType.ClrType.AssemblyQualifiedName; // var MyDbType is an IEntityType
context.Get(type).ToList();

// Use set as follows:
var myRecord = dbContext.someTable.First().GetType();
var myType = myRecord.ClrType; // var myRecord is an IEntityType
var myWhereClause = "myColumnNameFromTable = 5";
var query = dbContext.Set(fkType).AsNoTracking().OfType(fkType).Where(myWhereClause).ToDynamicList();