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

问题描述

您好我已经有了2个数据表（bannedlist，countrylist），均包含在列CC和国国名和鳕鱼的清单。我试图做一个查询在那里我可以选择countrylist表不在bannedlist表，以创造一个第三表的国家。

任何想法？

我还没有这让太远了。

  VAR ccList = DS .Tables [2] .AsEnumerable（）; 
变种bannedCCList = ds.Tables [1] .AsEnumerable（）; 
 VAR的查询=从ccList  -  [R ... 
  

..

尝试后

  VAR bannedCCList = ds.Tables [1] .AsEnumerable（） ; 
 VAR的查询=从ccList r其中bannedCCList.Any！（B = GT; B [CC] ==  -  [R [CC]）选择R; 
  

我仍然得到同样的国家列表。取缔那些没有被删除。这里是为了解释更详细。不知道我做错了。

 保护无效BindCountryBan（字符串SUBD）
 {
的DataSet DS =新的DataSet（）; 
 DS =新的DB（）CountryBan_GetSiteSettings（）; 
 
 BannedCountryListBox.DataSource = ds.Tables [1]; 
 BannedCountryListBox.DataValueField =CC; 
 BannedCountryListBox.DataTextField =国家; 
 BannedCountryListBox.DataBind（）; 
 
 //绑定的国家列表
 VAR ccList = ds.Tables [2] .AsEnumerable（）; 
变种bannedCCList = ds.Tables [1] .AsEnumerable（）; 
 VAR的查询=从ccList r其中bannedCCList.Any！（B = GT; B [CC] ==  -  [R [CC]）选择R; 
 // VAR的查询= ccList.Except（bannedCCList）; 
 
 
 
 
 //CountryListBox.DataSource = ds.Tables [2]; 
的DataTable boundTable = query.CopyToDataTable<&的DataRow GT;（）; 
 CountryListBox.DataSource = boundTable; 
 CountryListBox.DataValueField =CC; 
 CountryListBox.DataTextField =国家; 
 CountryListBox.DataBind（）;如果你使用
} 
  

解决方案

除了将工作它在国家的序列：

 使用System.Linq的; 
 ... 
 
 VAR ccList =从C在ds.Tables [2] .AsEnumerable（）
选择c.Field<串GT;（国家）; 
 VAR bannedCCList =从C在ds.Tables [1] .AsEnumerable（）
选择c.Field<串GT;（国家）; 
 VAR exceptBanned = ccList.Except（bannedCCList）; 
  

如果你需要一个国家没有明令禁止整行，你可以尝试一个左外连接

  VAR ccList = ds.Tables [2] .AsEnumerable（）; 
变种bannedCCList = ds.Tables [1] .AsEnumerable（）; 
 VAR exceptBanned =从C在ccList 
加入bannedCCList 
 B上c.Field<串>（国家）等于b.Field<串>（国家）成J个$从X b $ b在j.DefaultIfEmpty（）
，其中x == NULL 
选择C; 
  

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

解决方案

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屋！