c# - LINQ 和 NOT IN 子句

Question

我有 2 个数据表，一个是查找列表，另一个是数据。我在表中有很多列来保存数据，其中一列是查找数据表的外键列。

我想为所有未出现在包含基于外键列上的 Id 的数据的表中的查找行生成另一个列表。

我想使用 linq 查询，或适用于 2 个数据表的东西

就好像我在执行 SQL NOT IN。

干杯

这是一些数据。我希望新列表中包含类别 4 和 5。

           DataTable dtData = new DataTable( "Data" );
        dtData.Columns.Add( "Id", Type.GetType( "System.Int32" ) );
        dtData.Columns.Add( "CategoryId", Type.GetType( "System.Int32" ) );
        dtData.Columns.Add( "Qty", Type.GetType( "System.Int32" ) );
        dtData.Columns.Add( "Cost", Type.GetType( "System.Decimal" ) );
        dtData.Columns.Add( "TotalCost", Type.GetType( "System.Decimal" ) );
        dtData.Columns.Add( "TypeId", Type.GetType( "System.Int32" ) );

        dtData.Rows.Clear();

        DataRow row = dtData.NewRow();
        row["Id"] = 1;
        row["CategoryId"] = 1;
        row["Qty"] = 3;
        row["Cost"] = 237.00;
        row["TotalCost"] = 711.00;
        row["TypeId"] = DBNull.Value;
        dtData.Rows.Add( row );

        row = dtData.NewRow();
        row["Id"] = 2;
        row["CategoryId"] = 1;
        row["Qty"] = 5;
        row["Cost"] = 45.00;
        row["TotalCost"] = 225.00;
        row["TypeId"] = DBNull.Value;
        dtData.Rows.Add( row );

        row = dtData.NewRow();
        row["Id"] = 3;
        row["CategoryId"] = 3;
        row["Qty"] = 30;
        row["Cost"] = 1.00;
        row["TotalCost"] = 30.00;
        row["TypeId"] = 1;
        dtData.Rows.Add( row );

        row = dtData.NewRow();
        row["Id"] = 4;
        row["CategoryId"] = 2;
        row["Qty"] = 1;
        row["Cost"] = 15.00;
        row["TotalCost"] = 15.00;
        row["TypeId"] = 2;
        dtData.Rows.Add( row );

        row = dtData.NewRow();
        row["Id"] = 5;
        row["CategoryId"] = 1;
        row["Qty"] = 4;
        row["Cost"] = 3.00;
        row["TotalCost"] = 12.00;
        row["TypeId"] = 2;
        dtData.Rows.Add( row );

        DataTable dtlookup = new DataTable( "LookUp" );
        dtlookup.Columns.Add( "CategoryId", Type.GetType( "System.Int32" ) );
        dtlookup.Columns.Add( "Description", Type.GetType( "System.String" ) );

        dtlookup.Rows.Clear();

        DataRow lup = dtlookup.NewRow();
        lup["CategoryId"] = 1;
        lup["Description"] = "Category 1";
        dtlookup.Rows.Add( lup );

        lup = dtlookup.NewRow();
        lup["CategoryId"] = 2;
        lup["Description"] = "Category 2";
        dtlookup.Rows.Add( lup );

        lup = dtlookup.NewRow();
        lup["CategoryId"] = 3;
        lup["Description"] = "Category 3";
        dtlookup.Rows.Add( lup );

        lup = dtlookup.NewRow();
        lup["CategoryId"] = 4;
        lup["Description"] = "Category 4";
        dtlookup.Rows.Add( lup );

        lup = dtlookup.NewRow();
        lup["CategoryId"] = 5;
        lup["Description"] = "Category 5";
        dtlookup.Rows.Add( lup );


 var qqq = ( from r in dtlookup.AsEnumerable()
                    where !dtData.AsEnumerable().Any( b => b["CategoryId"] == r["CategoryId"] )
                    select r ).ToList();

Answer 1

我怀疑是 dahlbyk 的回答对您有所帮助，所以我将其粘贴在这里。

Linq not in select on datatable

---

如果您将它用于国家序列，Except 将起作用:

使用 System.Linq；...

var ccList = from c in ds.Tables[2].AsEnumerable()
             select c.Field<string>("Country"); 
var bannedCCList = from c in ds.Tables[1].AsEnumerable()
                   select c.Field<string>("Country");
var exceptBanned = ccList.Except(bannedCCList);

如果您需要未禁止国家/地区的完整行，您可以尝试左外连接:

var ccList = ds.Tables[2].AsEnumerable();
var bannedCCList = ds.Tables[1].AsEnumerable();
var exceptBanned = from c in ccList
                   join b in bannedCCList
                     on c.Field<string>("Country") equals b.Field<string>("Country") into j
                   from x in j.DefaultIfEmpty()
                   where x == null
                   select c;