CODEWITHSUNDEEP
linq
Tim
Tim

Reputation: 1249

LINQ Filtering From a DataRow

I need to add an additional filter to this LINQ statement but I can't seem to get it correct

var _duplicateRows =
    dt.AsEnumerable().GroupBy(
        myRow =>
        new
            {
                accidentYear = myRow.ItemArray[1].ToString().Trim(),
                reviewLine = myRow.ItemArray[2].ToString().Trim()
            }).Where(grp => grp.Count() > 1).Select(grp => grp.Key);

What I need is to exclude and rows where accidentYear and reviewLine are Null Or Empty

TIA

--EDIT

Hey guys, I just wanted to update the post. I decided to go with this to clean the datatable before I even got to the grouping. 

public DataTable GetCleanDataTable(DataTable dt)
        {
            IEnumerable<DataRow> _query =
                dt.AsEnumerable().Where(
                    dt1 =>
                    (!Convert.IsDBNull(dt1.ItemArray[0].ToString()) &&
                     !Convert.IsDBNull(dt1.ItemArray[1].ToString()) &&
                     !Convert.IsDBNull(dt1.ItemArray[2].ToString())));

            return _query.CopyToDataTable<DataRow>();
        }

Upvotes: 1

Views: 2557

Answers (3)

Dan Abramov
Dan Abramov

Reputation: 268265

Sometimes LINQ syntax beats extension methods.

var _duplicateRows = from row in dt.AsEnumerable()
                     let accidentYear = row.Field<string>(0)
                     let reviewLine = row.Field<string>(1)
                     where (!string.IsNullOrWhiteSpace(accidentYear)
                           && !string.IsNullOrWhiteSpace(reviewLine))
                     group row by new { accidentYear, reviewLine } into g
                     where g.Count() > 1
                     select g.Key;

Upvotes: 1

Nix
Nix

Reputation: 58562

Add a where clause to your datatable.

var _duplicateRows =
    dt.AsEnumerable()
      .Where(x=>!String.IsNullOrEmpty(myRow.ItemArray[1]) &&
                 !String.IsNullOrEmpty(myRow.ItemArray[2])
       )
      .GroupBy(
        myRow =>
          new
          {
            accidentYear = myRow.ItemArray[1].ToString().Trim(),
            reviewLine = myRow.ItemArray[2].ToString().Trim()
           })
      .Where(grp => grp.Count() > 1)
      .Select(grp => grp.Key);

A cleaner approach:

var _duplicateRows =
    dt.AsEnumerable().Select(
            s=>  new {
                accidentYear = s.ItemArray[1].ToString().Trim(),
                reviewLine = s.ItemArray[2].ToString().Trim()
            }
     ).Where(x=>!String.IsNullOrEmpty(accidentYear) &&
                 !String.IsNullOrEmpty(reviewLine)
     )
      .GroupBy(
        myRow =>myRow
       )
      .Where(grp => grp.Count() > 1)
      .Select(grp => grp.Key);

Upvotes: 2

Shiroy
Shiroy

Reputation: 258

Have you looked at 101 LINQ Samples

Upvotes: 0

Related Questions