How to get DbSet dynamically in EF Core 3?

Question

Hello I'm trying to do this:

I have more classes where user can store the files - path to those files is stored in JSON format in DB and files are stored in folders named as the class (so when class name is Foo then files will be stored in Files/Foo/Foobar.pdf).

I have one controller where I'm sending the path to the file which should be deleted. Here I cannot solve the problem, that I need to delete this file path also from database, but I cannot find out how to dynamically get the DbSet.

I searched in many questions here, but almost everytime I have found DbContext.Set(), but this method does not exists in ASP.NET CORE 3.0. Could you help me how to make it?

Here is my Controller:

[HttpGet]
        public JsonResult DeleteFile(string id)
        {
            KeyValuePair<string, string> message;
            if (!string.IsNullOrEmpty(id))
            {
                string filePath = Uri.UnescapeDataString(id);
                if (filePath.CheckIfExists())
                {
                    string nameOfInstance = filePath.GetNumberAccordingToFileName();
                    string tableName = filePath[1..(filePath.IndexOf('/', 1) - 1)];
                    Type type = Assembly.GetExecutingAssembly()
                            .GetTypes()
                            .FirstOrDefault(t => t.Name.Equals(tableName,StringComparison.OrdinalIgnoreCase));
                    if(type.IsClass)
                    {
                        var entity = db.GetDbSet(Activator.CreateInstance(type)); //this is not working
                        var item = entity.GetDataFromDbase(nameOfInstance,1,0,"Number","ASC","Number",false,out int filteredResultsCount, out int totalResultsCount, false).FirstOrDefault(); //find the item
                        item = item.DeleteFileFromItem(User.Identity.Name, filePath);
                        db.Update(item);
                        var result = db.SaveChanges(); 
                        if (result > 0)
                        {
                            if (filePath.DeleteFile())
                            {
                                message = new KeyValuePair<string, string>(MessagesHandler.Success, $"File {filePath.Substring(filePath.LastIndexOf("/"))} was successfully deleted.");
                                return Json(new
                                {
                                    status = true,
                                    message
                                });
                            }
                            else
                            {
                                message = new KeyValuePair<string, string>(MessagesHandler.Error, $"File {filePath.Substring(filePath.LastIndexOf("/"))} was not deleted.");
                                return Json(new
                                {
                                    status = false,
                                    message
                                });
                            }
                        } //if the changes were not made in DB
                        message = new KeyValuePair<string, string>(MessagesHandler.Error, $"File {filePath.Substring(filePath.LastIndexOf("/"))} was deleted, error in DB");
                        return Json(new
                        {
                            status = false,
                            message
                        });
                    }

                    
                }//error message when file not found
                message = new KeyValuePair<string, string>(MessagesHandler.Error, $"File {filePath} not found.");
                return Json(new
                {
                    status = false,
                    message
                });
            }//error message when filename is empty
            message = new KeyValuePair<string, string>(MessagesHandler.Error, $"Field ID cannot be empty");
            return Json(new
            {
                status = false,
                message
            });
        }

and this is the method GetDbSet:

public static Microsoft.EntityFrameworkCore.DbSet<TEntity> GetDbSet<TEntity>(this DataContext db, TEntity t) where TEntity : class
        {
            Type type = t.GetType();
        return (Microsoft.EntityFrameworkCore.DbSet<TEntity>)typeof(DataContext).GetMethod(nameof(DataContext.Set)).MakeGenericMethod(type).Invoke(db, null);
        }

Here I got this exception:

System.InvalidCastException HResult=0x80004002 Message=Unable to cast object of type 'Microsoft.EntityFrameworkCore.Internal.InternalDbSet1[SmartLab_System.Models.TestRequirement]' to type 'Microsoft.EntityFrameworkCore.DbSet1[System.Object]'.

Method GetDataFromDbase:

public static List<T> GetDataFromDbase<T>(this IEnumerable<T> entity, string searchBy, int take, int skip, string sortBy,
            string sortDir, string columnName, bool showDeactivatedItems, out int filteredResultsCount, out int totalResultsCount, bool countResult = true) where T : class
        {
            IEnumerable<T> helper;
            if (!String.IsNullOrEmpty(searchBy))//if any string to search was given
            {
                //find the properties we would like to search in
                var properties = typeof(T).GetProperties()
                                    .Where(x => x.CanRead && columnName.Contains(x.Name, StringComparison.InvariantCultureIgnoreCase) && columnName.Length == x.Name.Length)
                                    .Select(x => x.GetMethod)
                                    .Where(x => !x.IsStatic);
                                    //.ToList();

                //list of all items where searched value was found
                helper = entity.GetActiveItems(showDeactivatedItems)
                            .Where(m => properties
                                .Select(p => p.Invoke(m, null)?.ToString() ?? string.Empty)
                                .Any(a => a.ToString().Contains(searchBy, StringComparison.InvariantCultureIgnoreCase)) == true);
                            //.ToList();
            }
            else//if no string to search was given
            {
                helper = entity.GetActiveItems(showDeactivatedItems); //all items
            }

            List<T> result;
            if (take < 1)
            {
                result = helper
                            .OrderBy(sortBy + " " + sortDir)
                            .ToList();
            }
            else
            {
                result = helper
                           .OrderBy(sortBy + " " + sortDir)
                           .Skip(skip)
                           .Take(take)
                           .ToList();
            }

            if (countResult)
            {
                filteredResultsCount = helper.Count();//how many items is sent
                totalResultsCount = entity.Count(); //how many items is in database
            }
            else
            {
                filteredResultsCount = 0;//how many items is sent
                totalResultsCount = 0;
            }

        return result;

    }

except of calling db.GetDbSet(type)I have tried also db.GetDbSet(Activator.CreateInstance(type))but not working.

You probably need to use reflection. This should do the trick => `typeof(DbContext).GetMethod(nameof(DbContext.Set).MakeGenericMethod(type).Invoke(db, null)` and then you need to cast it to correct type. — dropoutcoder, Aug 31 '20 at 18:26
Thank you for your answer, I have now edited my question regards to results I got with your code — Lukas, Sep 01 '20 at 08:50
possible duplicate https://stackoverflow.com/questions/48041821/dynamically-access-table-in-ef-core-2-0 ? — joakimriedel, Sep 01 '20 at 08:54
@joakimriedel I have tried this but the problem is the same - when the inputparameter it IQueryable (T should be my class in this case) then when I try to make typeof(T) I'll get "object" and then `var properties = typeof(T).GetProperties() .Where(x => x.CanRead && columnName.Contains(x.Name, StringComparison.InvariantCultureIgnoreCase) && columnName.Length == x.Name.Length).Select(x => x.GetMethod).Where(x => !x.IsStatic);` does not work — Lukas, Sep 02 '20 at 12:07
If you have to write all that code it's a *very* strong indication you're doing it wrong. DbContext is as dynamic as needed already. A DbContext is a Unit of Work, not a connection or a database model and a DbSet is a repository. You shouldn't even have to use `Set()`, you should use the *configured* sets instead. After all, you can't have a Unit of Work if you don't know what entities are involved — Panagiotis Kanavos, Sep 04 '20 at 11:37
What are you actually trying to do? Explain the actual problem, not how you think it should be solved. If all you want is to delete a row, you don't need an ORM. With an ORM like EF Core you have to load the entity, eg with a simple `myFilesContext.Files.Find(someID)` or `var file=myContext.Files.First(f=>f.Path=path)`, use `myFilescontext.Files.Remove(file)` and then `myFilesContext.SaveChanges()`. You'd load the entities in the client just to send a DELETE back. Or you can use ADO.NET or Dapper to execute a `DELETE` directly. Use the option that makes sense — Panagiotis Kanavos, Sep 04 '20 at 11:41
In any case, it's only a few lines. There's no need for such complex code — Panagiotis Kanavos, Sep 04 '20 at 11:42

score 2 · Accepted Answer · answered Sep 10 '20 at 09:37

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; }
        }

Thanks for hint - I have changes all necessary classes to implement interface IHasFiles and then it was possible to cast IEnumerable — Lukas, Sep 14 '20 at 10:46

How to get DbSet dynamically in EF Core 3?

1 Answers1