Can we dynamically query tables?

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

Comments

  • 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(']'));
    }
    
  • 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

  • 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

Sign In or Register to comment.