c# Using LiNQ to remove multiple rows in Datatable

Question

This should be simple but i am not able to figure this out. I have a Datatable that has Name, Age where there are rows that have student names, but then multiple rows of special students' Name 'Chris's, and John's. the end result should have a table with all the rows except Chris and John to be combined into one row called Other with the sum of their age.

ie.

NAME | AGE
Tom | 20
John | 15
Peter | 5
John | 2
Tom | 33
Chris | 20

End Result:

NAME | AGE
Tom | 20
Peter | 5
Tom | 33
Other | 37

This is what im doing right now:

var others = table.AsEnumerable().Where(x => x["NAME"].ToString().Equals("Chris") || x["NAME"].ToString().Equals("John"));
var tmpOthers = 0;
foreach (DataRow otherRow in others)
            tmpOthers += decimal.Parse(otherRow["AGE"].ToString());
table = table.AsEnumerable().Where(x => !x["NAME"].ToString().Equals("Chris") || !x["NAME"].ToString().Equals("John")).CopyToDataTable();
table.Add(//OTHER-ROW-Here)

This works but i know there must be an easier way to do this in a single LiNQ statement. also i could not put the table.Rows.Remove in the same for loop because of iteration change.

RemoveRange? msdn.microsoft.com/zh-tw/library/…

MichaelMao
– MichaelMao

2016-07-11 18:48:21 +00:00
Commented Jul 11, 2016 at 18:48 — MichaelMao
– MichaelMao, Commented Jul 11, 2016 at 18:48
Does a name always starts with upper case?

Maciej Los
– Maciej Los

2016-07-11 19:35:07 +00:00
Commented Jul 11, 2016 at 19:35 — Maciej Los
– Maciej Los, Commented Jul 11, 2016 at 19:35

Maciej Los · Accepted Answer · 2016-07-11 20:06:10Z

3

I'd do that using Linq + delegate. Why? A solution with delegate is more elegant and more specific for such of case, because a name may starts with big or small letter. Check below solution:

//delegate to return "Other" for {"Chris","John"} - ignoring case
Func<string, string> GetOtherName = delegate(string s)
    {
        if(string.Equals(s, "Chris", StringComparison.OrdinalIgnoreCase) ||
                string.Equals(s, "John", StringComparison.OrdinalIgnoreCase))
            return "Other";
        else
            return s;
    };

//get total age for "Other"
var otherAge = dt.AsEnumerable()
            .Where(x=>GetOtherName(x.Field<string>("NAME")) == "Other")
            .Select(x=>x.Field<int>("AGE")).Sum();

//remove "Other" rows
dt.AsEnumerable()
    .Where(x=>GetOtherName(x.Field<string>("NAME")) == "Other")
    .ToList()
    .ForEach(r=>r.Delete());

//finally add "other" row
dt.Rows.Add(new object[]{"Other", otherAge});

Result:

NAME  AGE
Tom   20 
Peter 5 
Tom   33 
Other 37

For further details, please see: Func Delegate

edited Jul 11, 2016 at 20:06

answered Jul 11, 2016 at 19:52

Maciej Los

8,6211 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

civic.sir Over a year ago

Super! This is so much cleaner! Love the coding style

Rahul · Accepted Answer · 2016-07-11 18:48:32Z

1

Well you can use a for loop for that purpose like

for(int i=0; i<others.Count(); i++)
    table.Rows.Remove(others[i]);

answered Jul 11, 2016 at 18:48

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

6 Comments

civic.sir Over a year ago

It works with a for loop but the code i gave is a little messy and i know there should be a way to write all of that logic into one single LiNQ statement. Something like

table.AsEnumerable().Where(x => x["NAME"].ToString().Equals("Chris") || x["NAME"].ToString().Equals("John")).(sum up the AGE in some local variable).Removes these rows.CopyToTable()

a logic like that

dckuehn Over a year ago

@ civic.sir In my opinion that giant linq statement is messier than your original code. You could do a .ForEach() on the .Where() result set, but I'm not sure how to RemoveRange on that Where() again after that.

civic.sir Over a year ago

@dckuehn Yes that is what im looking for .. .ForEach().Where and then I can simply do a copytoDataTable() at the end no?

dckuehn Over a year ago

@civic.sir I don't think you can have a single LINQ statement that both adds a new entry to the list, and removes entities at the same time. Even if you can, I think that would be messier than your current solution.

civic.sir Over a year ago

Ahh i see. kk makes sense Thanks a lot

|

Collectives™ on Stack Overflow

c# Using LiNQ to remove multiple rows in Datatable

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related