Convert static params to Hashtable and use with lambda search

Question

This question is not a duplicate. I am looking for an approach in which I can pass in a Hashtable for any number of key-value params and related where clauses, and that hashtable can be dynamically integrated into the lambda search. The other post doesn't answer this.

I have 3 overload methods:

public static bool DoesRecordExist(string keyColumn, string keyValue, DataTable dt) {
 if (dt != null && dt.Rows.Count > 0) {
  bool exists = dt.AsEnumerable().Where(r => string.Equals(SafeTrim(r[keyColumn]), keyValue, StringComparison.CurrentCultureIgnoreCase)).Any();
  return exists;
 } else {
  return false;
 }
}

public static bool DoesRecordExist(string keyColumn1, string keyColumn2, string keyValue1, string keyValue2, DataTable dt) {
 if (dt != null && dt.Rows.Count > 0) {
  bool exists = dt.AsEnumerable().Where(r => string.Equals(SafeTrim(r[keyColumn1]), keyValue1, StringComparison.CurrentCultureIgnoreCase) && string.Equals(SafeTrim(r[keyColumn2]), keyValue2, StringComparison.CurrentCultureIgnoreCase)).Any();
  return exists;
 } else {
  return false;
 }
}

public static bool DoesRecordExist(string keyColumn1, string keyColumn2, string keyColumn3, string keyValue1, string keyValue2, string keyValue3, DataTable dt) {
 if (dt != null && dt.Rows.Count > 0) {
  bool exists = dt.AsEnumerable().Where(r => string.Equals(SafeTrim(r[keyColumn1]), keyValue1, StringComparison.CurrentCultureIgnoreCase) && string.Equals(SafeTrim(r[keyColumn2]), keyValue2, StringComparison.CurrentCultureIgnoreCase) && string.Equals(SafeTrim(r[keyColumn3]), keyValue3, StringComparison.CurrentCultureIgnoreCase)).Any();
  return exists;
 } else {
  return false;
 }
}

These all work as expected, and as you can see, all 3 are identical, just with increasing numbers of params and corresponding where clause arguments. And now I need to add yet another overload with 5 key/value pairs. Clearly this is getting silly.

How can I convert all these overloads into a single function in which I simply pass in a Hashtable of key-value pairs (or some other collection that makes equal or better sense)?

Answer 1

This problem is much simpler if you break it into parts.

First write a function that checks a single record for a match:

private static bool IsMatch(DataRow row, Dictionary<string,object> filters)
{
    return filters.All( pair => row[SafeTrim(pair.Key)].Equals(pair.Value) );
}

Then pass this as the delegate in your DoesRecordExist logic:

public static bool DoesRecordExist(Dictionary<string,object> filters, DataTable dt)
{
    if (dt == null || dt.Rows.Count == 0) return false;
    return dt.AsEnumerable().Any(r => IsMatch(r, filters));
}

The above is all you need.

Here is an example use:

public static DataTable CreateTestData()
{
    var data = new []
    {
        new { ID = 1, Name = "John",  DOB = new DateTime(2018,1,1) },
        new { ID = 2, Name = "Paul",  DOB = new DateTime(2018,1,2) },
        new { ID = 3, Name = "Ringo", DOB = new DateTime(2018,1,3) },
        new { ID = 4, Name = "George",DOB = new DateTime(2018,1,4) }
    };
    var table = new DataTable();
    table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));
    table.Columns.Add("DOB", typeof(DateTime));
    foreach (var d in data)
    {
        var row = table.NewRow();
        row[0] = d.ID;
        row[1] = d.Name;
        row[2] = d.DOB;
        table.Rows.Add(row);
    }
    return table;
}

public static void Main()
{
    var table = CreateTestData();

    var filter1 = new Dictionary<string,object> { {"ID", 1 } };
    Console.WriteLine("Filter1 exists? {0}", DoesRecordExist(filter1, table));  //Should be true

    var filter2 = new Dictionary<string,object> { { "ID", 1 }, {"Name", "John" } };
    Console.WriteLine("Filter2 exists? {0}", DoesRecordExist(filter2, table));  //Should be true

    var filter3 = new Dictionary<string,object> { { "ID", 1 }, {"Name", "John" }, {"DOB", new DateTime(2018,1,31)} };
    Console.WriteLine("Filter3 exists? {0}", DoesRecordExist(filter3, table));  //Should be false

    var filter4 = new Dictionary<string,object> { { "ID", 1 }, {"Name", "Paul" }, {"DOB", new DateTime(2018,1,2)} };
    Console.WriteLine("Filter4 exists? {0}", DoesRecordExist(filter4, table));  //Should be false
}

Output:

Filter1 exists? True
Filter2 exists? True
Filter3 exists? False
Filter4 exists? False

DotNetFiddle

Answer 2

By separating the concerns a little you can see a composite design pattern will solve this issue. Your method is basically...

public static bool DoesRecordExist(IPredicate<DataRow> condition, DataTable dt) {
    if (dt != null && dt.Rows.Count > 0) {
        bool exists = dt.AsEnumerable().Any(r => predicate.Condition(r));
        return exists;
    } else {
        return false;
    }
}

Where IPredicate<T> is an interface with one method: bool Condition(T t).

Now you can define a implementation of that interface for a DataRow, to represent your column/value matching for a single column:

public class DataRowPredicate
    : IPredicate<DataRow>
{
    private readonly string _keyColumn;
    private readonly string _keyValue;

    public DataRowPredicate(string keyColumn, string keyValue)
    {
        _keyColumn=keyColumn;
        _keyValue=keyValue;
    }

    public bool Condition(DataRow r)
    {
        return string.Equals(SafeTrim(r[_keyColumn]), _keyValue, StringComparison.CurrentCultureIgnoreCase); 
    }
}

Now all you need is the ability to stack those conditions together - you need the composite pattern: a collection which implements the same interface as the thing it contains. Something like...

public class PredicateCollection<T> : List<IPredicate<T>>, IPredicate<T>
{
    public bool Condition(T t)
    {
        return this.All(x => x.Condition(t));
    }
}

So now you can stack up as many as you like by creating a new collection and adding as many predicates as you require to it. Then you pass that collection as the parameter to DoesRecordExist. If you need to reuse the same collection of predicates a lot, just keep the collection around. Something like...

var conditionA=new PredicateCollection<DataRow>
{
    new DataRowPredicate(keyColumn1, keyValue1),
    new DataRowPredicate(keyColumn2, keyValue2),
    new DataRowPredicate(keyColumn3, keyValue3),
    //... etc, as required
};

And use it like this...

bool result = DoesRecordExist(conditionA, dt);   

Disclaimer: I'm not at my PC, so cut me some slack if this doesn't compile, or contains any autocorrections!