15

I have a LINQ query that looks like this...

var duration = Level3Data.AsQueryable().Sum(d => d.DurationMonths);

If all the d.DurationMonths values are null the Sum returns 0. How can I make the Sum return null if all the d.DurationMonths are null? Or do I need to run a separate query first to eliminate this situation before performing the sum?

GSerg
  • 76,472
  • 17
  • 159
  • 346
Simon Keep
  • 9,886
  • 9
  • 63
  • 78

5 Answers5

14

Along with the previous suggestion for an extension method - you could use a ternary operator...

var duration = Level3Data.AsQueryable().Any(d => d.DurationMonths.HasValue) 
               ? Level3Data.AsQueryable().Sum(d => d.DurationMonths) 
               : null;
Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • 2
    Although this does enumerate the results twice, My collections are small and I find this the most readable. So Have accepted it. Thanks everyone. – Simon Keep Aug 24 '09 at 14:28
6

You can use Aggregate to provide custom aggregation code :

var items = Level3Data.AsQueryable();
var duration = items.Aggregate<D,int?>(null, (s, d) => (s == null) ? d.DurationMonths : s + (d.DurationMonths ?? 0));

(assuming the items in Level3Data are of type D)

Thomas Levesque
  • 286,951
  • 70
  • 623
  • 758
3
var outputIndicatorSum = (from OutputIndicatorTable in objDataBaseContext.Output_Indicators
                                          where OutputIndicatorTable.Output_Id == outputId
                                          select (int?)OutputIndicatorTable.Status).Sum();
                int outputIndicatorSumReturn = Convert.ToInt32(outputIndicatorSum);
                return outputIndicatorSumReturn;

You can explicitly type cast non-nullable varaible into nullable type. i.e, select (int?)OutputIndicatorTable.Status).Sum();

Rahul vats
  • 88
  • 7
Vijay Singh
  • 105
  • 1
  • 2
  • 3
    This answer is wrong. Casting to `(int?)` before summing makes sense in the context of Linq2Sql, where `Sum` can return `null` (directly from the SQL query) even when the compiler infers non-nullable result which leads to an exception. With Linq2Objects, `Sum` always returns `0` even if all input elements were `null` or there were no input elements at all. – GSerg Dec 08 '16 at 10:15
1

Using Sum alone, this is impossible. As you indicated in your question, you will need to check for this situation before you call Sum:

var q = Level3Data.AsQueryable();
var duration = q.All(d => d.DurationMonths == null)
                   ? null
                   : q.Sum(d => d.DurationMonths);
Erik Forbes
  • 35,357
  • 27
  • 98
  • 122
-1

If you would like the result without two queries try:

var duration = Level3Data.AsQueryable().Sum(d => (double?)d.DurationMonths);

If you want zero instead of null as the result of this query use:

var duration = Level3Data.AsQueryable().Sum(d => (double?)d.DurationMonths) ?? 0;

C Tierney
  • 1,061
  • 1
  • 8
  • 14
  • I personally like this option the most. – TravisWhidden Nov 25 '13 at 23:49
  • 4
    If [`DurationMonths` is `int?`](http://stackoverflow.com/questions/1322544/how-to-make-a-linq-sum-return-null-if-the-summed-values-are-all-null#comment1156228_1322544) and all values are `null`, Linq2Sql will return `null` for `Sum`, and Linq2Objects will return `0`. The OP has the `0` returned, so they are using Linq2Objects, in which case casting to `double?` before summing [will not work](http://stackoverflow.com/questions/1322544/how-to-make-a-linq-sum-return-null-if-the-summed-values-are-all-null#comment69281108_16441725) and the result will still be `0`. – GSerg Dec 08 '16 at 10:36