Linq排除列 [英] Linq Excluding a column
问题描述
全部 -
我试图从Linq查询中排除列时遇到一个小问题。我想要做的是找到除ID列之外的至少一列的所有行都有数据。 ID列设置为自动增量,因此它将始终具有数据。通常,数据表从其他数据存储(即Excel,Access,CSV等)加载,有时这些行中的一些不包含数据。我目前的工作是从数据表(dt1)中删除该ID列,然后将其添加回数据表并重新填充列。 注意:总共列数可以从10到多达100个不等,所以任何解决方案必须是动态的。
下面的示例仅在第1行到第4行包含所有列中的数据,其中行0和5仅包含ID列中的数据。在dt2中,我只想添加第1到第4行。所以我的问题是如何从下面的LINQ查询中删除ID列?
In my example below only rows 1 through 4 contain data in all columns where Rows 0 and 5 only contain data in the ID Column. In dt2 I only want Rows 1 through 4 added. So My Question is how would I go about removing the ID Column from my LINQ query below?
感谢任何建设性的建议!
Thanks for any constructive suggestions in advance!
private void LoadDataTable()
{
DataTable dt1 = new DataTable();
//Create three columns
DataColumn dc = new DataColumn("ID", typeof(Int32));
dc.AutoIncrement = true;
dc.AllowDBNull = false;
dc.AutoIncrementSeed = 1;
dt1.Columns.Add(dc);
dc.SetOrdinal(0);
dc = new DataColumn("Item", typeof(String));
dt1.Columns.Add(dc);
dc = new DataColumn("Quantity", typeof(Int32));
dt1.Columns.Add(dc);
//Create items Array
string[] items = new string[] { "Ford", "Chevy", "GMC", "Buick", "Toyota", "Honda", "Mercury"
,"Hyundai", "Rolls Royce", "BMW", "Mercedes Benz", "Bentley", "Porche"
,"Infinity", "Jaguar" };
//Load Dummy data
Random rnd = new Random();
for (int i = 0; i < 5; i++)
{
DataRow dr = dt1.NewRow();
if (i > 0 && i < 4)
{
dr[1] = items[rnd.Next(i, items.Count())];
dr[2] = rnd.Next(i, 10000);
//Remove previously used Item from Array
items = items.Where(w => w != dr[1].ToString()).ToArray();
}
dt1.Rows.Add(dr);
}
//dt2 should only contain rows who have data in at least one column with the exception of the ID Column
DataTable dt2 = dt1.Rows.Cast<DataRow>()
.Where(w => w.ItemArray.Any(field => !string.IsNullOrEmpty(field.ToString().Trim()) && !(field is DBNull)))
.CopyToDataTable();
}
更新
这是修复感谢Ivan Stoev
Update
Here is the fix thanks to Ivan Stoev
//dt2 should only contain rows who have data in at least one column with the exception of the ID Column
var columns = dt1.Columns.Cast<DataColumn>().Where(c => c.ColumnName != "ID").ToList();
DataTable dt2 = dt1.Rows.Cast<DataRow>()
.Where(w => columns.Select(c => w[c]).Any(field => !string.IsNullOrEmpty(field.ToString().Trim()) && !(field is DBNull)))
.CopyToDataTable();
推荐答案
您需要更换 ItemArray
内有一些替代的 DataRow
值访问方法,例如这样
You need to replace the ItemArray
inside the query with some alternative DataRow
value access method, for instance like this
var columns = dt1.Columns.Cast<DataColumn>().Where(c => c.ColumnName != "ID").ToList();
DataTable dt2 = dt1.Rows.Cast<DataRow>()
.Where(r => columns.Select(c => r[c]).Any(field => ...
这篇关于Linq排除列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!