Using LINQ to filter on a variable number of parameters in c#

Question

I have a DataSet with one DataTable and now I want a general way of checking if different LINQ queries returns any matches.

For example. Lets say i have a table that is called MyTable with the following data.

Col1   Col2   Col3   Col4

A1     B1     C1     D1

A2     B2     C2     D2

A3     B3     C3     D3

I now want a function Conflicting that I can call with any number of parameters representing columns and values as such. Conflicting should return true if all the the parameters match any row.

Conflicting(MyTable, (Col1,A1)) //returns True
Conflicting(MyTable, (Col1,A1), (Col2,B1),(Col3,C1), (Col4,D1)) //returns True
Conflicting(MyTable, (Col1,A1), (Col2,D1)) //returns False

This is what code I have right now, the problem is that the code only works for checking if there exists a row with ONE column set to a specific value.

public static bool Conflicting(string table, string colum, string value)
{
    DataTable dt = state.Tables[table];

    var lines =
        from s in dt.AsEnumerable()
        where s.Field<string>(parameter) == value
        select new
        {
            ID = s.Field<Int32>(table + "ID")
        };

    var count = lines.Count();

    return count > 0;
}

Answer 1

It's not clear to me from your example whether you want to return true if any of the inputs are found in any row, or if they all have to be found in some row.

The former is simpler, so here's an example of that:

static bool Conflicting(string table, params Tuple<string, string>[] columnValuePairs)
{
    DataTable dt = state.Tables[table];

    return dt.AsEnumerable().Any(
        row => columnValuePairs.Any(p => row.Field<string>(p.Item1) == p.Item2));
}

You can call the above like this:

Conflicting("MyTable",
    Tuple.Create("Col1", "A1"),
    Tuple.Create("Col2", "B1"),
    Tuple.Create("Col3", "C1"),
    Tuple.Create("Col4", "D1"));

EDIT:

Based on your updated question, in which you indicate you want the latter behavior, i.e. that each of the given column/value pairs must be found in some row, the following should work instead:

static bool Conflicting(string table, params Tuple<string, string>[] columnValuePairs)
{
    DataTable dt = state.Tables[table];
    var remainingPairs = new List<Tuple<string, string>>(columnValuePairs);

    foreach (var row in dt.AsEnumerable())
    {
        int i = 0;

        while (i < remainingPairs.Count)
        {
            Tuple<string, string> columnValuePair = remainingPairs[i];

            if (row.Field<string>(columnValuePair.Item1) == columnValuePair.Item2)
            {
                remainingPairs.RemoveAt(i);
                continue;
            }

            i++:
        }

        if (remainingPairs.Count == 0)
        {
            return true;
        }
    }

    return false;
}

The above inspects each row to find any match column/value pairs for that row, removing that pair from the list of pairs to find if it does match. If and when the list of column/value pairs to look for becomes empty, the method returns true, having found all of the requested pairs. If all of the rows in the table are enumerated without emptying the list, then some pair did not match any of the rows, and the method returns false.

Note that the above is hardly the only way to implement this. Indeed, if you expected to have a very large number of column/value pairs, for performance you might want something different, such as to use a hash set or dictionary (it is possible to use either, but they each have their specific pros and cons). But for a relatively small number (say dozens at the most) of column/value pairs, a list should suffice and it does keep the implementation simple.

Answer 2

(You can use something else to hold params instead of Tuple, e.g. KeyValuePair)

Columns can be of any type, plus the values of fields can be null.

public static bool Conflicting(this DataTable dt, params Tuple<string, object>[] columnWithValue)
{
    return dt.AsEnumerable().Any(row =>
    {
          for (int i = 0; i < columnWithValue.Length; i++)
          {
                // check for null values
                if (row.IsNull(columnWithValue[i].Item1))
                {
                    if (columnWithValue[i].Item2 != null) 
                       return false;
                }
                else
                {
                   if (!row[columnWithValue[i].Item1].Equals(columnWithValue[i].Item2)) 
                       return false;
                }
           }
           return true;
      });
}

Using the code:

        DataTable dt = new DataTable();
        dt.Columns.Add("ColA", typeof(string));
        dt.Columns.Add("ColB", typeof(string));
        dt.Columns.Add("ColC", typeof(string));
        dt.Columns.Add("ColD", typeof(string));

        dt.Rows.Add("A", null, "C", "D");

        for (int i = 1; i < 10; i++)
            dt.Rows.Add("A" + i, "B" + i, "C" + i, "D" + i);

        bool res =  dt.Conflicting(
            Tuple.Create<string,object>("ColA", "A1"),
            Tuple.Create<string,object>("ColB", "B1")
            );

        Console.WriteLine(res); //true

        res = dt.Conflicting(
            Tuple.Create<string, object>("ColA", "A1"),
            Tuple.Create<string, object>("ColB", "B11")
            );

        Console.WriteLine(res);//false

        res = dt.Conflicting(
           Tuple.Create<string, object>("ColA", "A2"),
           Tuple.Create<string, object>("ColB", "B2"),
           Tuple.Create<string, object>("ColC", "C2"),
           Tuple.Create<string, object>("ColD", "D2")
           );

        Console.WriteLine(res);//true

        res = dt.Conflicting(
            Tuple.Create<string, object>("ColA", "A"),
            Tuple.Create<string, object>("ColB", null)
            );

        Console.WriteLine(res);//true

The same result can also be accomplished with a single Linq statement:

 public static bool Conflicting(this DataTable dt, params Tuple<string, object>[] columnWithValue)
 {
    return dt.AsEnumerable().Any(row => columnWithValue.All(col => row.IsNull(col.Item1) ? col.Item2 == null : row[col.Item1].Equals(col.Item2)));
 }