Querying a database schema in LinqPad

As part of some overall auditing in one of my projects, we recently added a LastUpdatedByUserID column to all of the major tables. As the name implies, this column stores the ID of the user who last updated the row. In order to keep an audit trail of the changes, for each table in the database, we have a corresponding TableName_Audit table that is updated (by triggers on the main table) every time the main table is modified.

I noticed that for one of the tables that had had the LastUpdatedByUserID column added, the script to regenerate the audit table hadn’t been run, so the audit table was missing the LastUpdatedByUserID column. This would entirely nullify the point of the column.

This led me to wonder if there were any other tables in the same state. rather than check this manually, I decided to write a script to do it, as this would be useful for future tables. Being a Linq type of person (I rarely write SQL any more), I decided to see if I could do this in LinqPad.

First, I needed a way of querying the database schema. I discovered that LinqPad gives you a Mapping object on the data context. This allows you to do something like this…

Mapping.GetTables()

…to see a list of meta information about the tables.

After some fiddling, I came up with the following Linq to get all tables whose name did not end in _Audit (which are audit tables), and which have a LastUpdatedByUserID column…

var lastUpdatedByTables =
  Mapping.GetTables()
    .Where(t => !t.TableName.EndsWith("_Audit]")
              && t.RowType.DataMembers
                   .Where(dm => dm.DbType != null)
                   .Any(dm => dm.Name == "LastUpdatedByUserID"));

This looks a bit daunting at first, but isn’t that bad once you play around with the results and see what’s included in them.

So, armed with that, I was able to get a list of all audit tables that didn’t have a LastUpdatedByUserID column…

var auditTables =
  Mapping.GetTables()
    .Where(at => at.TableName.EndsWith("_Audit]")
              && at.RowType.DataMembers
                   .Where(dm => dm.DbType != null)
                   .Any(dm => dm.Name == "LastUpdatedByUserID"));

Putting the two together, I was able to list any tables whose corresponding audit table was missing the LastUpdatedByUserID column…

var auditMissingLastUpdatedBy =
  lastUpdatedByTables.Where(t => !auditTables
                                   .Any(a => a.TableName == t.TableName.Replace("]", "_Audit]")
                                          && a.RowType.DataMembers
                                               .Where(dm => dm.DbType != null)
                                               .Any(dm => dm.Name == "LastUpdatedByUserID")
                                        )
                            );

Just to see how unreadable it would be, I tried wrapping it all up into one Linq expression…

Mapping.GetTables()
  .Where(t => !t.TableName.EndsWith("_Audit]")
           && t.RowType.DataMembers.Where(dm => dm.DbType != null)
                .Any(dm => dm.Name == "LastUpdatedByUserID")
           && !Mapping.GetTables().Where(at => at.TableName.EndsWith("_Audit]")
                                            && at.RowType.DataMembers
                                                 .Where(dm => dm.DbType != null)
                                                 .Any(dm => dm.Name == "LastUpdatedByUserID"))
                                  .Any(a => a.TableName == t.TableName.Replace("]", "_Audit]")
                                         && a.RowType.DataMembers.Where(dm => dm.DbType != null)
                                                                 .Any(dm => dm.Name == "LastUpdatedByUserID"))
        )

Probably not the sort of thing you’d want to read too often!

Still, the idea of being able to query the database schema in Linq is very powerful. I can’t imagine me doing it regularly, but like many tools, it’s good to know you can do it or when you need to.

P.S. Yes, i know I could have done this in SQL, but to be honest, I think I would rather eat my own ear wax (not that I have any, but you get the idea!). Linq is soooo much more expressive than SQL.

Update: Following a challenge from a co-worker, I had a go at writing some Linq to find any audit tables that were missing columns on the base table. Not being one to let such a challenge go unheeded, I gave it a go. It turned out to be surprisingly easy!

First, we get all the audit tables, select their columns, and then select the columns in the corresponding base table…

Mapping.GetTables()
  .Where(t => t.TableName.EndsWith("_Audit]"))
  .Select(t => new {
    t.TableName,
    AuditColumns = t.RowType.DataMembers
                     .Where(dm => dm.DbType != null)
                     .Select(dm => dm.Name),
    BaseColumns = Mapping.GetTables()
                    .Single(tbase => tbase.TableName == t.TableName.Replace("_Audit", ""))
                    .RowType.DataMembers.Where(dm => dm.DbType != null)
                    .Select(dm => dm.Name),
  })

We can find the missing columns by using the Linq Except method…

Missing = Mapping.GetTables()
            .Single(tbase => tbase.TableName == t.TableName.Replace("_Audit", ""))
            .RowType.DataMembers.Where(dm => dm.DbType != null)
            .Select(dm => dm.Name)
            .Except(t.RowType.DataMembers.Where(dm => dm.DbType != null)
                      .Select(dm => dm.Name))

Then all we need to do is filter out any rows where Missing doesn’t contain anything (ie all columns on the base table are on the audit table). Putting it all together, we get the following…

Mapping.GetTables()
  .Where(t => t.TableName.EndsWith("_Audit]"))
  .Select(t => new {
    t.TableName,
    Missing = Mapping.GetTables()
                .Single(tbase => tbase.TableName == t.TableName.Replace("_Audit", ""))
                .RowType.DataMembers.Where(dm => dm.DbType != null)
                .Select(dm => dm.Name)
                .Except(t.RowType.DataMembers.Where(dm => dm.DbType != null)
                          .Select(dm => dm.Name))
  })
  .Where(t => t.Missing.Any())
  .Select(t => new {
    t.TableName,
    Missing = string.Join(", ", t.Missing)
  })

Guess what? This found two tables with missing audit columns!

This turned out to be very useful when I wanted to query a lot of tables, and didn’t want to write the queries manually.

Be First to Comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.