Dynamically Access Table in Ef Core 2.0

Dynamically access table in EF Core 2.0

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

Dynamic table name EF CORE 2.2

Sorry, but you need to rework your model.

It is possible to do something generic as long as you have one table per type - you can go into the configuration and change the database table. OpenIddict allows that. You can overwrite the constructors of the DbContext and play whatever you want with the object model, and that includes changing table names.

What you can also do is a generic base class taking the classes you deal with as parameters. I have those - taking (a) the db entity type and (b) the api side dto type and then using some generic functions and Automapper to map between them.

But the moment you need to grab the table name dynamically you are in a world of pain. EF standard architecture assumes that an object type is mapped to a database entity. As such, an ID is unique within a table - the whole relational model depends on that. Id 44 has to be unique, for a specific object, not for an object and the table it was at this moment loaded from.

You also miss up significantly on acutally logic, i.e. for delete. I hate to tell you, but while you can implement security on other layers for reading, every single one of my write/update methods are handwritten. Now, it may seem that "Authorize" works - but no, it does not. Or - it does if your application is "Hello world" complex. I run sometimes pages of testing code whether an operation is allowed in a specific business context and this IS specific, whether the user has set an override switch (which may or may not be valid depending on who he is) do bypass certain business rules. All that is anyway specific.

Oh, what you can also do... because you seem to have a lot of tables: do NOT use one class, generate them. Scaffolding is not that complex. I hardly remember when I did generate the last EF core database classes - they nowadays all come out of Entity Developer (tool from Devart), while the db is handled with change scripts (I work db first - i actually want to USE The database and that means filtered indices, triggers, some sp's and views with specific SQL), so migrations do not really work at all.

But now, overwriting the table name dynamically - while keeping the same object in the background - will bite you quite fast. It likely only works for extremely simplistic things - you know, "hello world" example - and breaks apart the moment you actually have logic.

How to get DbSet dynamically in EF Core 3?

The link I posted in comments (Dynamically access table in EF Core 2.0) will retrieve the set for you, but you cannot operate on it since it is of type IQueryable and not IQueryable<T>.

I think there could be a better solution for you by using a simple switch and a common interface on entities instead. As long as you do not have 100+ tables in your database this will be pretty easy to maintain.

                var entityType = "Drawer";
                var pathToRemove = "somepath";
                var id = 1;

                // get queryable based on entity type
                IQueryable<IHasFiles> queryable = entityType switch
                {
                    "Drawer" => context.Set<Drawer>().OfType<IHasFiles>(),
                    "Bookshelf" => context.Set<Bookshelf>().OfType<IHasFiles>(),
                    _ => throw new ArgumentException("Unknown entity type", nameof(entityType))
                };

                // pick the item and include all files
                var item = await queryable.Where(i => i.Id == id).Include(i => i.Files).SingleAsync();

                // pick the correct file
                var file = item.Files.Single(f => f.Path == pathToRemove);

                // remove it
                item.Files.Remove(file);

                // save changes to db
                await context.SaveChangesAsync();

With the entities defined something like this

        public interface IHasFiles
        {
            int Id { get; }
            ICollection<File> Files { get; set; }
        }

        public class File
        {
            public int Id { get; set; }
            public string Path { get; set; }
        }

        public class Drawer : IHasFiles
        {
            public int Id { get; set; }

            public ICollection<File> Files { get; set; }
        }

        public class Bookshelf : IHasFiles
        {
            public int Id { get; set; }

            public ICollection<File> Files { get; set; }
        }

How to get DbSet from entity name in EF Core / .NET Core 2.0

Do this to add de extension Set(Type t) method to de dbcontext class.

using Microsoft.EntityFrameworkCore;

namespace ApiWebApplication.Utils
{
    public static class MyExtensions 
    {
        public static IQueryable<object> Set (this DbContext _context, Type t)
        {
            return (IQueryable<object>)_context.GetType().GetMethod("Set").MakeGenericMethod(t).Invoke(_context, null);
        }
    }
}

Example:

    // GET: api/Employees
    [HttpGet]
    public IEnumerable<object> GetEmployees()
    {
        return _context.Set(typeof(Employee));
    }

Handle dynamic Foreign Key columns with Entity Framework Core and scaffolding

You can use fluent syntax when querying for this so you can specify how to join

Syntax looks like this: Taken from here: https://entityframework.net/joining)

var data = context.Authors
    .Join(
        context.Books,
        author => author.AuthorId,
        book => book.Author.AuthorId,
        (author, book) => new
        {
            BookId = book.BookId,
            AuthorName = author.Name,
            BookTitle = book.Title
        }
    ).ToList();

---

Related Topics