Can we dynamically query tables?

Yossu · December 2019

I had a bug report today that turned out to be caused by a row in a table having a zero primary key. The code assumed that zero meant a new entity, and got confused.

I wanted to check if any other tables contained a row with a zero primary key, but couldn't work out a neat way to do it. All of our primary keys are named "ID", but not all tables have them (don't ask).

The following code gets a list of all the tables, but I'm not sure if I can then use this to query the data...

Mapping.GetTables()
    .Where(at => at.RowType.DataMembers
                            .Where(dm => dm.DbType != null)
                            .Any(dm => dm.Name == "ID"))
    .Select(t => t.TableName.Replace("[", "").Replace("]", ""))

Where do I go from here? Can I even do this?

Thanks

nescafe · December 2019

A SQL solution (assuming ID is numeric if it exists)

sp_msforeachtable 'IF COL_LENGTH(''?'', ''ID'') IS NOT NULL EXEC(''SELECT TOP 10 * FROM ? WHERE ID = 0'')'

Or LINQPad's ExecuteQueryDynamic:

var tables =
  from t in Mapping.GetTables()
  where t.RowType.DataMembers.Any(dm => dm.DbType != null && dm.Name == "ID")
  select t.TableName;

foreach (var table in tables)
{
  ExecuteQueryDynamic($"SELECT TOP 10 * FROM {table} WHERE ID = 0")
    .Dump(table.TrimStart('[').TrimEnd(']'));
}

Yossu · December 2019

Thanks for the reply. However, when I try it, I get an exception... "There is already an open DataReader associated with this Command which must be closed first."

Any ideas?

Thanks again

Yossu · December 2019

Actually, I found that if I changed it to...

Mapping.GetTables()
    .Where(at => at.RowType.DataMembers
                   .Where(dm => dm.DbType != null)
                   .Any(dm => dm.Name == "ID"))
    .Select(t => t.TableName.Replace("[", "").Replace("]", ""))
    .Where(t => ExecuteQueryDynamic($"select count(*) from {t} where ID = 0").First() > 0)

...it worked without throwing an exception, and gave me what I want.

Thanks

Can we dynamically query tables?

Comments