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?

Solution 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(...)

Solution 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\"");

Comments

[HGMamaci]

TableTypeDictionary ?

[Pablo]

No overload for method 'Set' takes 1 arguments

[Zane Claes]

IQueryable<Object> does not have an implementation for .Where(string).

[Bamdad]

@HGMamaci I've edited the answer to cover your question.

[Sam]

Does not work anymore with ef core6

[jdmneon]

check out the accepted answer. This answer worked when it was posted.