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