Use linq to find DataTable(Name) in a DataSet using unique list of Column Names

Question

I got roped into some old code, that uses loose (untyped) datasets all over the place.

I'm trying to write a helper method to find the DataTable.Name using the names of some columns.....(because the original code has checks for "sometimes we have 2 datatables in a dataset, sometimes 3, sometimes 4)..and its hard to know the order. Basically, the TSQL Select statements conditionally run. (Gaaaaaaaaaaaaaahhh).

Anyway. I wrote the below, and if I give it 2 column names, its matching on "any" columnname, not "all column names".

Its probably my linq skillz (again), and probably a simple fix.

But I've tried to get the syntax sugar down..below is one of the things I wrote, that compiles.

    private static void DataTableFindStuff()
    {

        DataSet ds = new DataSet();

        DataTable dt1 = new DataTable("TableOne");
        dt1.Columns.Add("Table1Column11");
        dt1.Columns.Add("Name");
        dt1.Columns.Add("Age");
        dt1.Columns.Add("Height");

        DataRow row1a = dt1.NewRow();
        row1a["Table1Column11"] = "Table1Column11_ValueA";
        row1a["Name"] = "Table1_Name_NameA";
        row1a["Age"] = "AgeA";
        row1a["Height"] = "HeightA";
        dt1.Rows.Add(row1a);

        DataRow row1b = dt1.NewRow();
        row1b["Table1Column11"] = "Table1Column11_ValueB";
        row1b["Name"] = "Table1_Name_NameB";
        row1b["Age"] = "AgeB";
        row1b["Height"] = "HeightB";
        dt1.Rows.Add(row1b);


        ds.Tables.Add(dt1);

        DataTable dt2 = new DataTable("TableTwo");
        dt2.Columns.Add("Table2Column21");
        dt2.Columns.Add("Name");
        dt2.Columns.Add("BirthCity");
        dt2.Columns.Add("BirthState");


        DataRow row2a = dt2.NewRow();
        row2a["Table2Column21"] = "Table2Column1_ValueG";
        row2a["Name"] = "Table2_Name_NameG";
        row2a["BirthCity"] = "BirthCityA";
        row2a["BirthState"] = "BirthStateA";
        dt2.Rows.Add(row2a);

        DataRow row2b = dt2.NewRow();
        row2b["Table2Column21"] = "Table2Column1_ValueH";
        row2b["Name"] = "Table2_Name_NameH";
        row2b["BirthCity"] = "BirthCityB";
        row2b["BirthState"] = "BirthStateB";
        dt2.Rows.Add(row2b);

        ds.Tables.Add(dt2);

        DataTable dt3 = new DataTable("TableThree");
        dt3.Columns.Add("Table3Column31");
        dt3.Columns.Add("Name");
        dt3.Columns.Add("Price");
        dt3.Columns.Add("QuantityOnHand");


        DataRow row3a = dt3.NewRow();
        row3a["Table3Column31"] = "Table3Column31_ValueM";
        row3a["Name"] = "Table3_Name_Name00M";
        row3a["Price"] = "PriceA";
        row3a["QuantityOnHand"] = "QuantityOnHandA";
        dt3.Rows.Add(row3a);

        DataRow row3b = dt3.NewRow();
        row3b["Table3Column31"] = "Table3Column31_ValueN";
        row3b["Name"] = "Table3_Name_Name00N";
        row3b["Price"] = "PriceB";
        row3b["QuantityOnHand"] = "QuantityOnHandB";
        dt3.Rows.Add(row3b);

        ds.Tables.Add(dt3);


        string foundDataTable1Name = FindDataTableName(ds, new List<string> { "Table1Column11", "Name" });
        /* foundDataTable1Name should be 'TableOne' */

        string foundDataTable2Name = FindDataTableName(ds, new List<string> { "Table2Column21", "Name" });
        /* foundDataTable1Name should be 'TableTwo' */

        string foundDataTable3Name = FindDataTableName(ds, new List<string> { "Table3Column31", "Name" });
        /* foundDataTable1Name should be 'TableThree' */


        string foundDataTableThrowsExceptionName = FindDataTableName(ds, new List<string> { "Name" });
        /* show throw exception as 'Name' is in multiple (distinct) tables */

    }

    public static string FindDataTableName(DataSet ds, List<string> columnNames)
    {
        string returnValue = string.Empty;
        DataTable foundDataTable = FindDataTable(ds, columnNames);
        if (null != foundDataTable)
        {
            returnValue = foundDataTable.TableName;
        }
        return returnValue;
    }

    public static DataTable FindDataTable(DataSet ds, List<string> columnNames)
    {
        DataTable returnItem = null;

        if (null == ds || null == columnNames)
        {
            return null;
        }

        List<DataTable> tables =
                ds.Tables
                .Cast<DataTable>()
                .SelectMany
                    (t => t.Columns.Cast<DataColumn>()
                        .Where(c => columnNames.Contains(c.ColumnName))
                    )
                .Select(c => c.Table).Distinct().ToList();

        if (null != tables)
        {
            if (tables.Count <= 1)
            {
                returnItem = tables.FirstOrDefault();
            }
            else
            {
                throw new IndexOutOfRangeException(string.Format("FindDataTable found more than one matching Table based on the input column names. ({0})", String.Join(", ", columnNames.ToArray())));
            }
        }

        return returnItem;
    }

I tried this too (to no avail) (always has 0 matches)

        List<DataTable> tables =
                ds.Tables
                .Cast<DataTable>()
                .Where
                    (t => t.Columns.Cast<DataColumn>()
                        .All(c => columnNames.Contains(c.ColumnName))
                    )
                .Distinct().ToList();

Answer 1

To me sounds like you're trying to see if columnNames passed to the method are contained within Column's name collection of Table. If that's the case, this should do the work.

List<DataTable> tables =
                ds.Tables
                .Cast<DataTable>()
                .Where(dt => !columnNames.Except(dt.Columns.Select(c => c.Name)).Any())
                .ToList();

(Below is an append by the asker of the question)

Well, I had to tweak it to make it compile, but you got me there.. Thanks.

Final Answer:

        List<DataTable> tables =
                        ds.Tables.Cast<DataTable>()
                        .Where
                            (dt => !columnNames.Except(dt.Columns.Cast<DataColumn>()
                                .Select(c => c.ColumnName))
                                .Any()
                            )
                        .ToList();

Final Answer (which is not case sensitive):

        List<DataTable> tables =
                        ds.Tables.Cast<DataTable>()
                        .Where
                            (dt => !columnNames.Except(dt.Columns.Cast<DataColumn>()
                                .Select(c => c.ColumnName), StringComparer.OrdinalIgnoreCase)
                                .Any()
                            )
                        .ToList();