Filtering a Dictionary List

One important component I needed for my Systems Directorate application is the ability to filter rows from a dictionary list and get the matching and non-matching rows. This article will describe the set of extension methods I created to perform this filtering.

Read more for details!

How it Works

My application uses “List<Dictionary<string,object>>” as a main data structure almost everywhere. It is simply a way of representing a table of columns where the columns are the dictionary values and the rows are the list.

When the data collection systems collect data, they place it into a dictionary list and store it. When other components of the application need to analyze that data to see if the data matches specific conditions, I needed a way to make the comparison.

To do this, I created a set of Filter extensions on the List<Dictionary<string,object>> data type. They take a second Dictionary<string,object> of columns to compare.

public static void Filter(
    this List<Dictionary<string, object>> data,
    Dictionary<string, string> filterSet,
    List<Dictionary<string, object>> matchedRows,
    List<Dictionary<string, object>> unmatchedRows)

public static List<Dictionary<string, object>> Filter(
    this List<Dictionary<string, object>> data, 
    Dictionary<string, string> filterSet)

The filterSet dictionary should have one key/value pair for each column you need to compare in the dictionary list. For example, if you have a column key called “Age” in your dictionary list, you can have a key in the filterSet called “Age” and the value is the value to compare to the row in the main list. If the dictionary row has that column and the value matches, it is added to the matchedRows collection. If it does not match, it is added to the unmatchedRows collection.

Here is an example of using the filter.

List<Dictionary<string,object>> rows = new List<Dicionary<string,object>>();
Dictionary<string,object> row;

row = new Dictionary<string,object>();
row["Name"] = "Trevor";
row["Age"] = 43;
rows.Add(row);

row = new Dictionary<string,object>();
row["Name"] = "John";
row["Age"] = 50;
rows.Add(row);

Dictionary<string,string> filter = new Dictionary<string,object>();
filter["Age"] = "43";

List<Dictionary<string,object>> matched = new List<Dictionary<string,object>>();
List<Dictionary<string,object>> unmatched = new List<Dictionary<string,object>>();

rows.Filter(filter,matched,unmatched);

// matched will contain one row with Trevor.
// unmatched will contain one row with John.

One thing to notice is that the filter is actually a Dictionary<string,string> and not not an object value. This is because the comparisons are meant to handle more than just checking for equality.

Comparison Operators

By default, comparisons are done by equality but other operators can be specified in the filter value. For the operator to work, the filter string is converted to the same data type as the row value it is being compared to. If the row has an integer, the filter value is converted to an integer.

The operator should be placed on the front of the value. For example, if you need to filter on rows where Age is less than 50, use “<50”. Valid operators are <, <=, >, >=, =, ==, <>, !=, ~=, and !~. The last two are regular expression matches for when comparing strings. The = and == are the same as are the <> and !=.

filter["Age"] = "<45";

Forced Type Conversion

The value that the filter is being compared to can be converted to a string or a number by adding an additional prefix to the front of the comparison value. Adding a $ will convert the value to a string, whereas adding a # will convert the value to an Int64. In the example below, it will convert the number 43 to a string and then compare that it is less than “45”. Converting a non-number string to a number will result in an exception.

filter["Age"] = "$<45";

Conjunctions

Another option for the filter value is to AND and OR multiple expressions together. To AND expressions together, start the filter value with & and use additional & between each expression. To OR expressions, use a vertical bar |.

filter["Age"] = "|40|43|45|50";
filter["Name"] = "&~=Trev&John";

You can even combine conjunctions if needed but it depends on which one you start the filter with. You can have “(a or b) AND (c or d)” or “(a and b) OR (c and d)”.

filter["Age"] = "| >40 & <45 | >49 & <55";  // between 40 and 45 or between 49 and 55.
filter["Age"] = "& <40 | >50 & >20";        // less than 40 or greater than 50 but over 20.

Spaces are optional between all the operators in the filter values. You can include them or not and they sometimes can help readability. The only time spaces cannot be used is before the & or | operator at the start of the filter.

I hope to be posting the code for my entire Dennis-IT library sometime soon.