CODEWITHSUNDEEP
c#linq
Darren Young
Darren Young

Reputation: 11090

C# LINQ Ignoring empty values in datatable

I have a datatable that I have grouped as follows:

var result = from data in view.AsEnumerable()
                     group data by new {Group = data.Field<string>("group_no")}
                     into grp
                     select new
                     {
                         Group = grp.Key.Group,
                         PRAS = grp.Average(c => Convert.ToDouble(c.Field<string>("pAKT Total")))

                     };

Now, the average function is also counting the empty cells in it's calculation. For example, there are 10 cells with only 5 populated with values. I want the average to be the sum of the 5 values divided by 5.

How can I ensure that it does what I want?

Thanks.

Upvotes: 1

Views: 3290

Answers (2)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174309

To my knowledge, that's not possible with the Average method.
You can however achieve the result you want to, with the following substitute:

PRAS = grp.Sum(c => Convert.ToDouble(c.Field<string>("pAKT Total"))) / grp.Count(c => !c.IsDBNull)

This only makes sense, when you want to select the "empty" rows in the group, but just don't want to include them in your average. If you don't need the "empty" rows at all, don't select them in the first place, i.e. add a where clause that excludes them.

Upvotes: 0

Kobi
Kobi

Reputation: 138017

Maybe something like this:

PRAS = grp.Select(row => row.Field<string>("pAKT Total"))
          .Where(s => !String.IsNullOrEmpty(s))
          .Select(Convert.ToDouble)
          .Average()

Upvotes: 6

Related Questions