Help with the following linq query?

Question

Take the following linq query:

var summary = results.Select(r => 
                             new
                             {
                             TotalPopulation = results.Sum(s => s.Population),
                             TotalGross = results.Sum(s => s.Gross),
                             }).Distinct();

The above works fine, but is it the best way to do it. What does the r represent? Why do I need it?

Also, I would think that I could add the following to my query, but I can't:

GrossPerPop = TotalPopulation / TotalGross

The above says TotalPopulation and Total Gross do not exist in the current context.

I also tried:

GrossPerPop = results.sum(s => s.Population) / results.Sum(s => s.Gross), 

The above says / can't be applied to decimal? or double? Does this have anything to do with the fields being nullable? It just so happens I don't have any Population fields or Gross fields with a null value, so I changed these to non-nullable fields in the sql table designer. However, if they do contain a 0 value, how could I check that within the Linq code?

Answer 1

A better way is something like:

class Metrics {
    public int TotalPopulation { get; set; }
    public decimal TotalGross { get; set; }
    public decimal GrossPerPopulation {
        get {
            return TotalGross / TotalPopulation;
        }
    }
 }

Then:

var metrics = new Metrics {
                  TotalPopulation = results.Sum(s => s.Population), 
                  TotalGross = results.Sum(s => s.Gross)
              };

What you currently have is performing the above projection (albeit into an anonymous type) for every element of results and then throwing out all but one of the resulting projections. You could get by without creating an explicit type as I've done above and just use an anonymous type but that would be silly.

What does the r represent?

The r represents the name of the parameter in the anonymous method that you've defined.

Why do I need it?

When you write

results.Select(expression)

it is necessary that expression be a delegate that eats whatever the type of the elements of results are and returns some other type. That is IEnumerable<SomeType>.Select is for projecting a sequenece to another sequence. Therefore, you must pass a method that eats whatever the type of the elements of results are and returns some other type. One way of doing is that is by passing in an anonymous delegate. One way of defining an anonymous delegate is by using a lambda expression. When you say r => ... you are defining an anonymous method via a lambda expression.

Also, I would think that I could add the following to my query, but I can't:

GrossPerPop = TotalPopulation / TotalGross

That's right, you can't. Think of it like this. Say that you had an explicit type

class Metrics {
    public int TotalPopulation { get; set; }
    public decimal TotalGross { get; set; }
    public decimal GrossPerPopulation { get; set; }
    public Metrics(
        int totalPopulation,
        decimal totalGross,
        decimal grossPerPopulation
    ) {
            TotalPopulation = totalPopulation;
            TotalGross = totalGross;
            GrossPerPopulation = grossPerPopulation;
    }
}

Should the following be legal?

Metrics m = new Metrics(100, 100, m.TotalPopulation / m.TotalGross)

Of course not! But that's effectively what you are trying to do.

GrossPerPop = results.Sum(s => s.Population) / results.Sum(s => s.Gross)

The above says / can't be applied to decimal? or double?

I don't see any reason for you to be getting such a message. In C#, the above is legal assuming that both s.Population and s.Gross are numeric types; they will just be implicitly promoted to the "right" type. This is true even if s.Population or s.Gross are numeric types.

Do note however that gross per population should be

results.Sum(s => s.Gross) / results.Sum(s => s.Population)

Answer 2

It is not good query because Sum will be calculated for every row and then it will be "distinct"ed.

Just write

var summary = new
   {
      TotalPopulation = results.Sum(s => s.Population),
      TotalGross = results.Sum(s => s.Gross),
   };