Linq not in select on datatable

c#, datatable, linq

Solution

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;

Problem

Hi i've got 2 data tables (bannedlist,countrylist), both contains list of country names and cods in columns cc and country. I am trying to do a query where i can select countries from countrylist table that are not in bannedlist table in order to create a 3rd table. Any ideas? I haven't got too far with this. ``` var ccList = ds.Tables[2].AsEnumerable(); var bannedCCList = ds.Tables[1].AsEnumerable(); var query = from r in ccList.... ``` .. after trying ``` var bannedCCList = ds.Tables[1].AsEnumerable(); var query = from r in ccList where !bannedCCList.Any(b => b["cc"] == r["cc"])select r; ``` i still get same country list. banned ones haven't been removed. here is more detail in order to explain more. not sure what i am doing wrong ``` protected void BindCountryBan(string subd) { DataSet ds = new DataSet(); ds = new DB().CountryBan_GetSiteSettings(); BannedCountryListBox.DataSource = ds.Tables[1]; BannedCountryListBox.DataValueField = "cc"; BannedCountryListBox.DataTextField = "country"; BannedCountryListBox.DataBind(); //bind country list var ccList = ds.Tables[2].AsEnumerable(); var bannedCCList = ds.Tables[1].AsEnumerable(); var query = from r in ccList where !bannedCCList.Any(b => b["cc"] == r["cc"])select r; //var query = ccList.Except(bannedCCList); //CountryListBox.DataSource = ds.Tables[2]; DataTable boundTable = query.CopyToDataTable<DataRow>(); CountryListBox.DataSource = boundTable; CountryListBox.DataValueField = "cc"; CountryListBox.DataTextField = "country"; CountryListBox.DataBind(); } ```

Original source