Convert static params to Hashtable and use with lambda search

Viewed 189

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)?

6 Answers

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

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!

How about something like this:

    public static bool DoesRecordExist(DataTable dt, List<KeyValuePair<string, string>> keyValuePairs)
    {
        if (dt != null && dt.Rows.Count > 0)
        {
            bool exists = dt.AsEnumerable().Where(r =>
            {
                foreach (var kvp in keyValuePairs)
                {
                    if (string.Equals(SafeTrim(r[kvp.Key]), kvp.Value, StringComparison.CurrentCultureIgnoreCase))
                    {
                        return true;
                    }
                }

                return false;
            }).Any();
            return exists;
        }
        else
        {
            return false;
        }
    }

something like this would help

public static bool DoesRecordExist(DataTable dt, params string[] parameters)
{
    bool exists = false;

    if (parameters.Length % 2 != 0 )
        throw new ArgumentException();

    if (dt == null || dt.Rows.Count == 0)
        return false;

    var query = dt.AsEnumerable()

    for (int i = 0; i < parameters.Length; i += 2)
        query = query.Where(r => string.Equals(SafeTrim(r[parameters[i]]),
            parameters[i + 1], StringComparison.CurrentCultureIgnoreCase)) ; 

    return exists;

}

You can pass in an enumerable of your search items and loop over them. For each item just chain an extra call to Where onto your result.

I've created some sample code to hopefully illustrate what I mean. I've not fully tested it but hopefully if it doesn't work as is it should give you the right idea. It basically works on the principle that .Where(A&B) is basically the same as .Where(A).Where(B)

public static bool DoesRecordExist(DataTable dt, Dictionary<string,string> searchDetails) 
{
    if (dt != null && dt.Rows.Count > 0) {
        var items = dt.AsEnumerable();
        foreach(var searchItem in searchDetails)
        {
            items = items.Where(r=>string.Equals(SafeTrim(r[searchItem.Key]), searchItem.Value, StringComparison.CurrentCultureIgnoreCase)
        }
        return items.Any();
    } 
    else 
    {
        return false;
    }
}

Here is your recipe

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

  • Zip simply pair two sequences together.
  • All will be like doing && on all booleans.

side notes:

  • I don't think you need this dt.Rows.Count > 0 check since Any returns false on empty collection.

  • use IEnumerable<string> instead of string[] if possible.

  • this solution can be actually slow because of nested loops, O(N*Rows) search speed where N is length of given arrays. it must be tested however.

Related