CODEWITHSUNDEEP
c#linqdatatable
Mark Kram
Mark Kram

Reputation: 5842

Linq Excluding a column

All -- I am having a small problem trying to exclude a column from a Linq query. What I am trying to to do is to find all rows where at least one of the columns with the exception of the ID column have data. The ID column is set to Auto increment so it will always have data. Typically, the Data Table is loaded from some other data store (ie Excel, Access, CSV, etc) and sometimes a few of these rows contain no data. My current work around is to drop that ID Column from the Data Table (dt1) and then add it back to the data table and re-populate the column. Note: The total number of columns can vary from as little as 10 to as many as 100 so any solution has to be dynamic.

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

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

Upvotes: 0

Views: 3776

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205849

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 => ...

Upvotes: 2

Related Questions