Our tables (well, most of them, see below) have a primary key named ID, and we make the (foolish it seems, see below part II) assumption that if an entity has a zero ID, then it is new.
I was trying to work out the cause of a bug today, whereby a new entity was being treated as if it already existed, and discovered that the problem was caused by there being a row in the database that had a zero ID.
Hmm, bleah and other such words of exasperation.
As it happened, the particular row in question was marked as (soft) deleted, so I just altered the code to exclude such entities (which it probably should have done in the first place) and all was well.
Well, not quite. You see this got me wondering. How many other tables have a row with a zero ID. Obviously, any table could only have one such row, as this is the primary key, but really, we don’t want any.
As the database has 253 non-audit tables, I didn’t fancy querying each one individually. This looks like a job for… puts on mask and cloak… Linqman!
OK, so this isn’t Linqman, it’s actually Calvin (from the rather excellent Calvin and Hobbes cartoons) as his alter-ego Stupendous Man, about to terrorise the hapless Susie Derkins, but I couldn’t find any pictures of Linqman, probably because I only just made him up!
Those who have read my rambling articles before will know that I’m a huge fan of Linq, and use it for many things that other people wouldn’t consider. You should see my shed. OK, maybe you shouldn’t, but I digress.
As I had already explored the possibilities of querying a database schema in LinqPad, it seemed that I should be able to extend this to query the tables found by the query.
This turned out to be a little more difficult than I thought, and was only made possible by the ExecuteQueryDynamic extension method that comes with LinqPad. This allows you to pass arbitrary SQL (ugh) to the database as part of your Linq query. I know, who writes SQL nowadays? Well, some people I know do, but I’ve given up trying to convert them! ExecuteQueryDynamic returns an IEnumerable<Object>, which you can then manipulate in the succeeding Linq.
So, after some fiddling, I ended up with the following…
Mapping.GetTables()
.Where(at => !at.TableName.EndsWith("_Audit]")
&& 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)
The first six lines just get the names of the non-audit tables, and were copied from the previous article. The last line is the one that does the work. ExecuteQueryDynamic takes a lambda that specifies the query to run. In our case, the SQL is only ever going to return one row, so we use First() to get the number, and then filter out results that didn’t have any zero IDs.
This turned up a few more dubious tables.
If we were only querying one table, we would use its little brother ExecuteQuery, which allows you to specify the type of the results, which makes life easier if you want to do anything with the data afterwards. For example, if we wanted to get the actual data, not just a count, we could do the following…
ExecuteQuery(typeof(Contacts), "SELECT * FROM Contacts WHERE ID = 0")
This produces an IEnumerable<Contacts>, which can be used in subsequent parts of your Linq.
Not the sort of thing you’d use every day, but pretty powerful for those rare occasions when you don’t fancy querying 253 tables by hand!
Be First to Comment