How do you loop through all tables in a database context and query data

Question

I need to run a query on all tables in my database context. I believe you can loop through the context tabes by doing

foreach (var entityType in context.Model.GetEntityTypes()) {

}

However I don't see a way to run linq queries on the entityType. Is there a way to do that.

Yes. You would use reflection for that. However, it really depends on what type of linq query you are trying to execute, and what the architecture of your system is. Perhaps you could elaborate a little on that. — Travis J, Jul 12 '19 at 20:54
Perhaps this is similar to what you are looking for: https://stackoverflow.com/questions/49676508/getentitytypes-configure-entity-properties-using-the-generic-version-of-proper — Travis J, Jul 12 '19 at 20:56
The first query to all the tables is slow, so during startup I want to loop through all tables and call ..FirstOrDefault(); You can see why in this comment https://stackoverflow.com/a/48141506/234531 — Tija, Jul 12 '19 at 21:19
What type is `context` in this code? DbContextBase? DbContext? ObjectContext? ModelBuilder? — Travis J, Jul 12 '19 at 21:35
Sub class of DbContext that contains all my models as properties using EF code first. — Tija, Jul 12 '19 at 21:40

score 3 · Answer 1 · answered Jul 12 '19 at 21:59

Assuming that your code returns a set of types which correlates to entries in the DbSet<T> definitions,

i.e. I am assuming

IEnumerable<Type> entityTypes = context.Model.GetEntityTypes();

You could set up a method which could be called with that type, and simply use FirstOrDefault on its DbSet.

I am not sure what the exact scope of your area is, so some of this will assume that you need to adjust to fit into your architecture.

public class ThisClassReference
{

    // It wasn't provided, so this is just to show the containing method area,
    // and also provide reference to the current executing object instance,
    // which we will need to reference below
    public void YourExecutingMethod()
    {
        // Iterate through the set of entity types
        foreach (var entityType in context.Model.GetEntityTypes()) {

            // We need the class reference to construct the method call
            Type ThisClassReference = typeof(ThisClassReference);

            // We need the name of generic method to call using the class reference
            MethodInfo mi = ThisClassReference.GetMethod("FirstOrDefaultGeneric", BindingFlags.Instance | BindingFlags.NonPublic);

            // This creates a callable MethodInfo with our generic type
            MethodInfo miConstructed = mi.MakeGenericMethod(entityType);

            // This calls the method with the generic type using Invoke
            miConstructed.Invoke(this, null);
        }
    }

    // Once called use the generic type to access the first result in the DbSet from your context.
    private void FirstOrDefaultGeneric<T>()
    {
        var unUsed = context.Set<T>.FirstOrDefault();
    }

}

Bonus points for figuring out how to convert these calls to asynchronous ones and save yourself probably 40ms per type.

It didn't working because GetEntityTypes returns IEntityType. I was able to get a different approach working though. — Tija, Jul 14 '19 at 03:51

score 1 · Answer 2 · answered Jul 14 '19 at 03:52

I was able to get this code working:

PropertyInfo[] properties = context.GetType().GetProperties();
                foreach (PropertyInfo property in properties) {
                    var prop = context.GetType().GetProperty(property.Name).GetValue(context, null);
                    var table = prop as IEnumerable<BaseDbModel>;
                    if(table != null) {
                        var row = table.Select(a => a.createdDttm).FirstOrDefault();
                    }
                }

How do you loop through all tables in a database context and query data

2 Answers2