LINQ and NOT IN Clause

Question

I have 2 datatables one is a lookup list the other is the data. I have many columns columns in the table holding the data and one of these columns is the foreign key column to the lookup datatable.

I want to produce another list for all the lookup rows which do NOT appear in the table holding the data based on the the Id on the foreign key column.

I want to use a linq query, or something that works on the 2 datatables

As if I was doing a SQL NOT IN.

Cheers

Here is some data. I expect the new list to have category 4 and 5 in it.

           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

I suspect it was dahlbyk's answer that helped you, so I'll paste that here.

Linq not in select on datatable

---

Except would work if you use it on sequences of the countries:

using 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);

If you need the full rows where the countries aren't banned, you could try a left outer join:

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;

Answer 2

You can use the DataTable.AsEnumerable() extension method to return an enumerable collection of your data. From there you can use LINQ to effectively query your data.

var myData = myDataTable.AsEnumerable()

Edit: If you need to determine if an ID is contained in your data table, you will have to select out the list of IDs first (since you cannot compare entire DataRow objects).

bool idExists = dtData
                    .AsEnumerable()
                    .Select(item => (int) item["Id"])
                    .Contains(myId);