c# - 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.

question from:https://stackoverflow.com/questions/65938862/returning-a-class-type-to-be-used-in-entityframeworkcore

Answer 1

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.