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