How to iterate over all tables or DbSet then iterate over all columns in Entity Framework 6 code-first?

Question

I have a problem regarding on how to iterate over all the tables/DbSet in EF6 (not EF Core 6) code first.

I have these DbSet examples:

public partial class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() : base(AppHelper.ConnectionString())//"name=cn"
    {
        this.Configuration.LazyLoadingEnabled = false;
    }        

    public virtual DbSet<Function> Functions { get; set; }
    public virtual DbSet<Role> Roles { get; set; }
    public virtual DbSet<User> Users { get; set; }
}

Then in my Winforms app, I did find some example from Stackoverflow which I revised the below code to loop all the table or Dbset. Please I need your help on how do I call and get the value of columns using the GetValue() method shown below:

private void Form_Load(object sender, EventArgs e)
{
    var myEntityModel = new ApplicationDbContext(); //your context
    var dbSets = myEntityModel.GetType().GetProperties().Where(p => p.PropertyType.Name.StartsWith("DbSet")); //get Dbset<T>            

    foreach (var dbSetProps in dbSets)
    {                
        var dbSet = dbSetProps.GetValue(myEntityModel, null);
        var dbSetType = dbSet.GetType().GetGenericArguments().First();                
        BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
        PropertyInfo[] properties = dbSetType.GetProperties(flags);

        foreach (PropertyInfo prop in properties)
        {
            var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;

            if (type == typeof(DateTime))
            {
                // DateTime DataType
                Console.WriteLine("Name: " + prop.Name + ", Value: " + prop.GetValue(, null)); //<-- This is my problem what should i put here
            }
            else
            {
                // Not a DateTime DataType
                Console.WriteLine("Name: " + prop.Name + ", Value: " + prop.GetValue( , null));//<-- This is my problem what should i put here
            }
        }
    }
}

score 0 · Answer 1 · answered Feb 11 '22 at 05:05

0

With this code, you'll get all of tables in DB

var tableNames = context.Model.GetEntityTypes()
    .Select(t => t.GetTableName())
    .Distinct()
    .ToList();

then iterate over the tables and get individual table info

foreach(var tableName in tableNames  {
    
    var tableType = _dbContext.Model.GetEntityTypes().First(c => c.GetTableName() == tableName);
        foreach (var property in tableType.GetProperties())
        {
            var type = property.ClrType;
            var typeName = type.ShortDisplayName();
        }
    }

answered Feb 11 '22 at 05:05

Asif Rahman

225
2
13

Hi Asif. have test the solution you provided, but error stated below. ApplicationDbContext does not contain a definition for Model – Jam Godz Feb 11 '22 at 06:22
i think, GetEntityTypes() is for EF Core. but I am using EF6 CodeFirst – Jam Godz Feb 11 '22 at 06:32
for EF 6, check this one - https://stackoverflow.com/questions/3892926/entity-framework-get-list-of-tables – Asif Rahman Feb 11 '22 at 07:43
thank you for the reply, but still doesnt answer my problem. – Jam Godz Feb 11 '22 at 08:47
I am using Code-First not Database First – Jam Godz Feb 11 '22 at 08:47

How to iterate over all tables or DbSet then iterate over all columns in Entity Framework 6 code-first?

1 Answers1