Returning a Class Type to be used in EntityFrameworkCore

Question

I have a lot of tables in a database and am using Entity Framework Core to connect my application to them. I have about 150 tables that all have exactly the same structure (same columns in each one). Each table identifies what the data it contains by the way the tables are named, e.g. table_grade1, table_grade2, table_grade3 etc.

Now I know I could just create a single table with an extra column (grade1, grade2, grade3 etc) to identify the content data, and put all of the data in that table. But a lot of other applications currently access this database, not to mention spreadsheet queries.

Ideally, what I would like to do is write a method takes a string parameter (table name, e.g. "table_grade1") and returns the EF class so I can use it to create a data repository as I am implementing the Repository Pattern. So ideally something like:

var EFClass = GetMyEF("table_grade1")

Then I can use this object when creating my Repository from the UnitOfWork class -

MyRepository repo = UnitOfWork.GetRepo<EFClass>();

This will return the repository with the DbSet that Entity Framework would called TableGrade1 and would be returned using the code -

MyRepository repo = UnitOfWork.GetRepo<TableGrade1>();

I have used methods to return the class Type from a string but I cannot use these class Types to return the repo. I have also created instances of the class but cannot use these objects to create the repo.

I don't know if what I am wanting to do is even possible but I hope so otherwise I will probably have to write a switch statement in my UnitOfWork class to return the correct repo - with about 150 cases - and I am trying to avoid this if possible as I was hoping to leave this open to future changes in the table names in the database and writing the switch statement means I have two projects that will need updating every time there is a database change instead of one.

I have added more detailed info here:

So the database has around 150 tables and each one has the same columns but the prices are for a different asset, each table has the columns published_date, pricing_date, price, relative_month. Tables are listed similar to the following table_grade1, table_grade2, table_grade3, table_grade4, and so on..

I used to just use the excellent Npgsql nuget package to access the tables and could therefore have a list of strings that could be read at runtime with the table names in the database at the time (as tables may be added if another asset class is starting to be tracked in the DB) and the table name can be inserted into a SQL statement as a parameterized query to get a dynamic reference to the current list of tables.

I have in my main program

   static void Main()
    {
        var uow = new UnitOfWork();
        var type = Type.GetType("table_grade1"); 
        uow.TheType = type;
        var dynamicRepo = uow.GetForwardRepo(type);

    }

In UnitOfWork

    private DbContext _dbContext = new MyDBContext();
    public Type TheType { get; set; }

    public MyRepository<T> GetForwardRepo<T>() where T : class
    {
        return new MyRepository<T>(_dbContext);
    } 

    public bool SetTheType(string TableName)
    {
        TheType = GetTypeFromTableName(TableName);
        if (TheType != null) return true;
        return false;
    }

    public Type GetTypeFromTableName(string TableName)
    {
        string derivedTableName = GetEFClassNameFromTableName(TableName);
        
        Type returnType = Type.GetType(derivedTableName);
        return returnType;
    }

The MyRepository is a standard repository for Type T (the EntityFramework created class derived from DB tables) with CRUD operations.

public class MyRepository<T> : IMyRepository<T> where T : class
{
    DbSet<T> _dbSet;
    private DbContext _dbContext;

    public MyRepository(DbContext dbContext)
    {
        _dbContext = dbContext;
        _dbSet = _dbContext.Set<T>();
    }
}

`

The only thing I have not included here is the GetEFClassNameFromTableName method, as this just takes the table name and converts the string into the EF DBSet name (removes underscores, add capitalisation removes railing 's' etc.

What I am ultimately trying to do is enable some way of taking a string for the DB table name (e.g. "table_gradeX") and using it to return a repository with the DBSet of that table from EntityFramework in the UnitOfWork. Whether I need helper methods along the way (to convert the string into a type or instance of the class or whatever) I am not sure, but that is my ultimate aim.

Thanks for looking at this, I do appreciate it.

ps I know that having the property TheType in UnitOfWork makes no sense for this program as is, but it will be used once I can figure out how to dynamically create the repository from the string.

Answer 1

OK so I want to be able to pass a string and return data from one of a possible 150 tables from a database. All tables have the same structure comprising 4 columns. Ideally this would be done utilising a Repository pattern. My first attempt was fixed on working with the data as a DbSet and so was trying to return a specific DbSet using a string parameter to allow for new tables not requiring a change to the code. I did not want to use a switch statement or similar to return the correct Repo. I had to change my approach as without specifically casting to the specific DbSet it seemed impossible to pass a string to return the repository with the data in it and be able to work with it. Instead I will create a list of generic Class I have created with the same properties as the tables. I then retrieve the data using SQL and using a solution from Manish Banga posted on donetstudy.com utilising his MapToList method can translate the returned table into list of my generic Class types. I have this as a method in my GenericRepository

private bool GetForwardDbSet(string tableName)
{
    if (tableName.Substring(0, 2) != "z_") return false;
    Regex regEx = new Regex("z_");
    var tableList = _dbContext.Model.GetRelationalModel().Tables.Select(c => c.Name).Where(c => regEx.IsMatch(c)).ToList();
    if (!tableList.Contains(tableName)) return false;

    using (var command = _dbContext.Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = $"SELECT * FROM {tableName};";
        _dbContext.Database.OpenConnection();
        using (var result = command.ExecuteReader())
        {
            // do something with result
            _dbSet = result.MapToList<ForwardDbSet>();
        }
    }

    if (_dbSet.Count > 0) return true;

    return false;
}

The method does some checking to ensure the string that is passed in is in the right format for the tables I want to target, I retrieve a list of those target table names and check to see if the string is on the list. I then run a SQL command to return the data from the database and Map this data to a List of a generic class type I call ForwardDbSet, (the dbset in the names here are not actual DbSet types they are a hangover from when I was trying to cast to genuine DbSet types, this is merely a class I have created with the properties of the tables in the database being returned). I store this list in the variable _dbSet (again not an actual DbSet type) and then rreturn it to be worked with. As this data is read only I do not need it to be tracked as I will not be performing any Create, Update or Delete functions once the data has been read.

I have also created some helper functions in the Repository to check whether the _dbSet object is populated with prices and for working with subsets of the data but that is specific to my application and irrelevant for this solution. So this is my workaround, thanks to everyone who helped get here especially @Joao-Victor-de-Paula-Ramos.

Answer 2

If you need create Repository with dynamic Type this Code will do.

Type entityType = Type.GetType("table_grade1"); // Get Entity Type
if (entityType == null) throw new Exception();

Type repoType = typeof(MyRepository<>); //Get Repository Type

Type genericRepository = repoType.MakeGenericType(entityType); // Make <T> to repoType
var dynamicRepo = Activator.CreateInstance(genericRepository, new object[] { context }); // Repository, Params

Remember the entityType need to exist on Context Entity Mapping (DbSet)

Edit

If you need invoke the Method this code can help.

                                                        // Method Name, Type Params
MethodInfo method = genericRepository.GetType().GetMethod("MethodName", new Type[] { });
entities = (IEnumerable<Entity>)method.Invoke(genericRepository, new object[] { });
                                                                // Method Params

In this case i Cast the return of Method.Invoke for a List of Super Class.