What to do when the columns in a query are to be determined dynamically

Not a common requirement, but as it can happen, and was an interesting one to solve, I thought it worth documenting.

The scenario is this: For a medical system, there are a number of treatment sites (ie bits of the human body) that must be checked. For each treatment site, there will be a number of check types that need to be done. We needed to display this information in a grid like this (this is a prototype, so only shows True and False, rather than having checkboxes that can be altered)…

The treatment sites are listed in the first column, and are pulled from a database table.

The interesting bit here is that the 2nd column onwards are not fixed, these are also pulled from a database table, making the grid dynamic. We don’t know in advance what columns will be needed (other than the first one of course).

There were two parts to this task, first to pull the data from the database in a manner suitable for building the grid dynamically, and second, building the grid. This article will only cover the first part, hopefully the second part will be written up when it has been completed.

Here are the three tables used in the scenario…

The TreatmentSites table holds the data you saw in the first grid column. The TreatmentChecktypes table holds the descriptions used for the grid column headers for the 2nd and subsequent grid columns, and the middle table joins the other two. For a specific system, and entry in the SystemTreatmentSitesChecked table is surfaced as a checked checkbox in the grid (or the word “True” in the prototype).

The server-side query

The first thing we need to do is make a cross join between the TreatmentSites table and the TreatmentCheckTypes table, in such a way that we get an indicator for each entry (or lack of entry) in the joining table. This was fairly straightforward…

int systemID = 210;
var cross = TreatmentSites.Select(ts => new {
  TreatmentSiteID = ts.ID,
  TreatmentSiteName = ts.Description,
  Checked = string
    .Join("", TreatmentCheckTypes
              .OrderBy(tct => tct.ID)
              .Select(cs => SystemTreatmentSitesCheckeds
                .Any(s => s.Active
                       && s.SystemID == systemID
                       && s.TreatmentSiteID == ts.ID
                       && s.TreatmentCheckTypeID == cs.ID) ? "y" : "n"))
})
.ToArray();

For each TreatmentSite, we select its ID and name, and then get all TreatmentCheckTypes for the TreatmentSite, building an array of “y” and “n” for the SystemTreatmentSitesChecked entries. We then use string.Join() to join the array into a single string.

Note that we order the TreatmentCheckTypes (in this case by ID, although that may change if they request a DisplayOrder column to be added), so that we know the order. This will be important later.

The result of this query is the following…

The string in the Checked column depends on what entries are in the SystemTreatmentSitesChecked table. I just added a few for testing, so the string are mostly “nnnn”.

Ideally, we would have written a query that created a results set that contained a separate column for each entry in the TreatmentCheckTypes table, but as we don’t know how many columns there are in advance, we wouldn’t be able to pass this out of the code block that created the query. Once you want to pass data out of your current code block, .NET requires static types, and that means you have to know what they are in advance (there are a few ways of getting around this, but they are massively complex and not the sort of code you’d want to look at on an empty stomach!).

The results set we have is a simple and compact way of sending the data out of the service to the client, with a known type. Once received, the client needs to decode this into objects with separate properties for each TreatmentCheckType in order to pass the data to the dynamically built grid.

The client-side manipulation

The client needs to know the TreatmentCheckTypes, which we’ll assume have been passed up already. Note that these must be ordered in the same way as they were in the first query, otherwise we won’t be able to match up the “y” and “n” characters with the correct TreatmentCheckType.

Here’s where it got fun. It was clear that we couldn’t use ordinary types, as we didn’t know how many properties there were. I played around with ExpandoObjects, which were introduced in .NET 4.0, and have the ability to have properties added and removed at run time. This eventually worked, but required the ExpandoObject to be cast as an IDictionary<string, object> in order to be able to manipulate the properties. After some fiddling, I realised that I didn’t actually need the ExpandoObject at all, I could just use a dynamic.

So, without further waffle, here is the code the client used to create the collection of anonymous types that would be passed to the grid…

Dictionary<String, dynamic>[] data = new Dictionary<string, dynamic>[cross.Count()];
for (int crossN = 0; crossN < cross.Count(); crossN++) {
  var d = new Dictionary<String, dynamic>();
  d["TreatmentSiteID"] = cross[crossN].TreatmentSiteID;
  d["TreatmentSiteName"] = cross[crossN].TreatmentSiteName;
  for (int checktypeN = 0; checktypeN < treatmentCheckTypes.Count(); checktypeN++) {
    d["C" + checktypeN] = cross[crossN].Checked[checktypeN] == 'y';
  }
  data[crossN] = d;
}

As mentioned above, the client will already have a list of TreatmentCheckTypes, which are stored in the wittily named treatmentCheckTypes variable above.

For each entry in the cross-joined data (produced by the first query above), we extract the TreatmentSiteID and TreatmentSiteName, and then loop though the string (the “Checked” property) and add a bool property named Cn (where n is the index) that is true if the character was ‘y’ and false otherwise. This is now suitable for use with the grid.

Just to demonstrate that this worked, we can dump out the results using the following code…

foreach (var d in data) {
  var p = d as IDictionary<string, object>;
  Console.Write(p["TreatmentSiteID"] + "\t" + p["TreatmentSiteName"] + "\t");
  for (int i = 0; i < treatmentCheckTypes.Count(); i++) {
    Console.Write(p["C" + i] + "\t");
  }
  Console.WriteLine("");
}

This of course would not be used in production code, it was just there for testing. It produces the following output…

1  FB Breast  True  False  True  False 
2  DIBH Breast  False  False  True  False 
3  Lung  False  False  False  False 
4  DIBH Lung  False  False  False  False 
5  Exhale Lung  False  False  False  False 
6  Head and Neck  False  False  False  False 
7  SRS  False  False  False  False 
8  Brain (non SRS)  False  False  False  False 
9  Extremity  False  False  False  False 
10  Abdomen  False  False  False  False 
11  Pelvis  False  False  False  False 
12  SBRT Lung  False  False  False  False 
13  SBRT Liver  False  False  False  False 
14  SBRT Other  False  False  False  False 
15  CNS  False  False  False  False 
16  Pediatrics  False  False  False  False 
17  Electron  False  False  False  False 
18  Face  False  False  False  False 
19  Spine  False  False  False  False

If you can be bothered, you can check and see that the bool values shown here correspond to the “y” and “n” values in the Checked string produced by the first query.

All that’s left to do is query the treatmentCheckTypes variable and add columns to the grid for each entry, then bind the data to the grid. As mentioned before, that will hopefully be covered by another article.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

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