Linq 不在数据表上的选择中 [英] Linq not in select on datatable

问题描述

我有 2 个数据表(bannedlist、countrylist)，它们都包含在 cc 和 country 列中的国家名称和鳕鱼列表.我正在尝试做一个查询，我可以从countrylist 表中选择不在bannedlist 表中的国家，以创建第三个表.

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.

有什么想法吗?

我还没有走得太远.

        var ccList = ds.Tables[2].AsEnumerable(); 
        var bannedCCList = ds.Tables[1].AsEnumerable();
        var query = from r in ccList....

...

尝试后

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

推荐答案

如果您在国家/地区的序列上使用它，则除外:

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;

这篇关于Linq 不在数据表上的选择中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！