使用linq在使用唯一列名列表的数据集中查找数据表(名称)

Question

我被一些旧代码所束缚，这些代码到处使用松散(未类型化)的数据集。

我正试图写一个助手方法来找到数据表。使用某些列的名称.....(因为原始代码有"有时我们在一个数据集中有2个数据表，有时3个，有时4个"的检查)..而且很难知道顺序。基本上，TSQL Select语句是有条件运行的。(Gaaaaaaaaaaaaaahhh)。

。我写了下面的代码，如果我给它两个列名，它匹配的是"任意"列名，而不是"所有列名"。

这可能是我的linq技能(再次)，可能是一个简单的修复。

但是我已经试着把语法简化了，下面是我写的一个东西，它可以编译。

    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;
    }

我也试过了(没有用)(总是有0匹配)

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

Answer 1

对我来说，听起来你想看看传递给方法的columnNames是否包含在Table的Column's name集合中。如果是这样的话，这个就可以了。

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

(下面是提问者的追加)

嗯，我不得不调整它使编译，但是你让我做到了。谢谢。

最终答案:

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

最终答案(不区分大小写):

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