LINQ GroupBy while converting from string to decimal and then back to string

Question

Is it possible to convert a string value to a decimal value within a LINQ expression that performs an aggregate function like SUM or AVERAGE?

Assume the example below where I have a collection of Bank Accounts where my goal is to obtain an average of each customers bank account if they have a balance. The data comes from an XML API where all the data is read in a strings.

public class BankAccount
{
  string Id{ get; set; }
  string CustomerId { get; set; }
  string Balance { get; set; }
}

Sample data ...

{ Id = "1", CustomerId = "Bob", Balance = "1" }
{ Id = "2", CustomerId = "Bob", Balance = "2" }
{ Id = "3", CustomerId = "Sam", Balance = "4" }
{ Id = "4", CustomerId = "Sam", Balance = "" }
{ Id = "5", CustomerId = "Alice", Balance = "" }

LINQ grouping expression. Is there a way to convert the value of Balance to a decimal so an average can be taken within the LINQ statement? I tried x => Decimal.Parse(x.Balance) but got an Input string was not in a correct format error. I only need to convert the Balance property to decimal for the Average calculation as the results would be rendered as a string in the XML.

At the same time, if an account does not have a balance listed (i.e. it's blank like Sams's first account and Alice's only account above) then I don't want the Average to take that entry included in the average, though I still want the account grouped in for display.

var groupedResults = allAccounts
                      .GroupBy(x => new {x.CustomerId, x.Balance})
                      .Select(g => new BankAccount {
                         CustomerId = g.Customer.Key.CustomerId,
                         Balance = g.Average(x => x.Balance)
                      }).ToList();

These are the results I am looking for:

{ CustomerId = "Bob", Balance = "1.5" }
{ CustomerId = "Sam", Balance = "4" }
{ CustomerId = "Alice", Balance = "" }

ocuenca · Accepted Answer · 2018-01-08T17:49:22.143

1

I think to achieve the result you are looking for you should try this:

var groupedResults = allAccounts
                      .GroupBy(x =>x.CustomerId)
                      .Select(g => new BankAccount {
                         CustomerId = g.Key,
                         Balance = g.Where(x =>!string.IsNullOrEmpty(x.Balance))
                                    .Select(x =>(decimal?)decimal.Parse(x.Balance))
                                    .DefaultIfEmpty(null)
                                    .Average().ToString()
                      }).ToList();

First just group by CustomerId, is not necessary to include the Balance there. Then, to get the average and avoid the error parsing include the condition to make sure the Balance is not empty.

Another way to do it using query syntax:

from e in allAccounts
group e by e.CustomerId into g
let temp=g.Where(x =>!string.IsNullOrEmpty(x.Balance))
select new BankAccount(){CustomerId = g.Key,
                         Balance =temp.Any()? 
                                  temp.Average(x =>Decimal.Parse(x.Balance)).ToString():""
                        };

edited Jan 08 '18 at 17:49

answered Jan 08 '18 at 01:42

ocuenca

38,548
11
89
102

average of 4 and 0 would result in 2, but there are few ways around it – Slai Jan 08 '18 at 02:26
@Slai - In this case if Balance is empty or blank I don't want to include it in the average. Basically I don't want to treat a blank value as zero. – webworm Jan 08 '18 at 02:41
@webworm, sorry, I misunderstood the part of you don't wan't to include the items with blank values, you will need to add a where to exclude them, see my update – ocuenca Jan 08 '18 at 03:54
When I try using the Lambda syntax I get an error `cannot implicitly convert type decimal? to string` – webworm Jan 08 '18 at 17:43
Did you put the `.ToString()` at the end? I tested yesterday in LinqPad and it worked – ocuenca Jan 08 '18 at 17:45
I did add the `.ToString` however what ended up making work was removing the extra right parens after Average and removing the semicolon after `.ToString()`. I had the advantage of intellisense :) – webworm Jan 08 '18 at 17:47
haha, its true, I should have copy the same query I wrote in LinqPad, sorry about the syntax errors ;) – ocuenca Jan 08 '18 at 17:50

Slai · Answer 2 · 2018-01-08T21:33:18.073

1

decimal d;
var groupedResults = allAccounts.GroupBy(a => a.CustomerId)
    .Select(g => new BankAccount { CustomerId = g.Key, Balance = g.Average(b => 
        decimal.TryParse(b.Balance, out d) ? (decimal?)d : null).ToString() }).ToList();

The .TryParse part results in (decimal?)null for strings that can't be parsed, which are then ignored by .Average. Also, the last average for Alice results in (decimal?)null and then in "".

edited Jan 08 '18 at 21:33

answered Jan 08 '18 at 02:11

Slai

22,144
5
45
53

@webworm that's what my answer is doing .. null values are not used in the average – Slai Jan 08 '18 at 02:52
Sorry, I see what your saying now. By treating blank as zero you would be throwing off the average. Your solutions avoids that. Very nice. – webworm Jan 08 '18 at 03:13
Would there be a problem when trying to apply `.ToString()` to a `null` value? – webworm Jan 08 '18 at 03:15
@webworm on most types yes, but in my case `null(decimal?).ToString()` returns `""` https://referencesource.microsoft.com/#mscorlib/system/nullable.cs,d83d2db767b43db7. I have an answer on avoiding `.ToString()` if you are interested https://stackoverflow.com/questions/5646145/how-can-i-extract-a-string-from-an-excel-cell/41127000#41127000 – Slai Jan 08 '18 at 03:22
can you pass in the `out var d` without `d` being declared? Compiler is giving me an error. – webworm Jan 08 '18 at 17:26
@webworm updated it for earlier versions C# before Visual Studio 2015 – Slai Jan 08 '18 at 21:38

LINQ GroupBy while converting from string to decimal and then back to string

2 Answers2