Let's say I have a list of string representing the name of generated classes with Entity Framework from SQL Server:

public static List<string> tableList = new List<string>
        {
            "aaa",
            "bbb",
            "ccc",
            "ddd",
            "eee",
        };

And I want to load data from entities:

DateTime from_date = DateTime.MinValue;
DateTime to_date = DateTime.MaxValue;

using (var ctx = new MyEntities())
{
    IList<aaa> aa = ctx.aaa
                       .Where(a => a.date_added >= from_date && 
                                   a.date_added <= to_date)
                       .ToList();
}

But I'll have to do that with like 20 or more tables.

Is there a way that I can do this dynamically? Like:

Result res = new Result();

List<Result> r = new List<>();

foreach (string table in tableList)
{
    using (var ctx = new MyEntities())
    {
        IList<*table*> aa = ctx.*table*
                               .Where(a => a.date_added >= from_date && 
                                           a.date_added <= to_date)
                               .ToList();

        res.quantity = aa.Count();
        res.title = table;
    }

    r.Add(res);
}

All of the needed tables have the column date_added.

PS: I just need to count how many rows there are in each table for a defined period. Like tables client, customer, employee: how many were registered from 2020 to 2021. And I will output:

{
        "title":"Client",
        "quantity": 19,
        "from_date": [some_date],
        "to_date": [some_date]
},
{
        "title":"Customer",
        "quantity": 123,
        "from_date": [some_date],
        "to_date": [some_date]
},
{
        "title":"Employee",
        "quantity": 31,
        "from_date": [some_date],
        "to_date": [some_date]
},

CodePudding user response：

Considering your answer to my comments, You can add classes to the namespace where the classes are generated by EF. I suggest you to create a new interface in that namespace like this one :

public interface IObjectWithDates
{
    DateTime date_added { get; }
}

Now we want the generated classes having a date_added field to implement the IObjectWithDates interface. Instead of modifying the generated classes, you can add, in the same namespace, a additional file with

public partial class Client: IObjectWithDates
{ }

public partial class Customer: IObjectWithDates
{ }

//and so on with the classes you want to track

Now we have an assembly with some classes implementing IObjectWithDates. You can get the clases by their names with Type t = Type.GetType(...) if you like. You can also ask C# to give you all the classes that implements IObjectWithDates in your model namespace :

public IEnumerable<Type> AllTrackedClassesInNameSpace(string namespaceName, Assembly assembly)
{
    return assembly.GetTypes()
       .Where(x => ((!string.IsNullOrWhiteSpace(x.Namespace)) && (x.Namespace.ToUpper().Contains(namespaceName.ToUpper()))))
       .Where(x => x.IsClass)
       .Where(x=>typeof(IObjectWithDates).IsAssignableFrom(x));
}

[EDITED HERE] At this point, you will have a list of Types (IEnumerable<Type> typeList in the following code), all implementing IObjectWithDates and you should be able to do this :

public int MyCount<T>(DbContext ctx) where T: class, IObjectWithDates
{
    return ctx.Set<T>().Count(a => a.date_added >= from_date && a.date_added <= to_date);
}

and then

Result res = new Result();

List<Result> r = new List<Result>();

using (var ctx = new DbContext(""))
{
    foreach (var type in typeList)
    {
        var method = this.GetType().GetMethod("MyCount").MakeGenericMethod(type);

        res.quantity = (int)method.Invoke(this, new object[]{ ctx });
        res.title = table;
    }

    r.Add(res);
}

Some details may need to be adjusted (from_date / to_date could be parameters of MyCount or properties of the calling class for instance) but the global idea is there. You could get the Set<>() method on the context by reflection but in my opinion, a separate method is more easey to read and debug.

Note that it is not wise to generate an IList<> of objects just to count them because SQL will request all the objects, EF will then instantiate a lot of instances, on per row... just to count them instead using a Select Count...