Linq Query with Nullable Sum

Linq query with nullable sum

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points ?? 0).Sum() 
}

EDIT - ok what about this... (Shooting again since I don't know your model...):

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId)
              .Sum(v => v.Points) 
}

Linq GROUP BY and SUM nullable long

If any item is not null, then use Sum, otherwise null

SUM_JOB1 = g.Any(gs => gs.JOB1 != null) ? g.Sum(gs => gs.JOB1) : null        

LINQ SUM Nullable

The GROUP JOIN statement indicates that you're attempting a LEFT OUTER JOIN. This is why you're receiving a NULL issue. By using JOIN for an INNER JOIN, then you won't stumble on this, but it also means that you will only see those items that have values for interest.

(FROM a in db.Payments 
Join b in db.InterestCharges ON a.pid Equals b.Pid 
SELECT a, TotalInterestReceived = SUM(b.Interest) 
).toList()

It is possible to generate a sub-select which may get the values you're hoping for. The purpose here is that you get all payments, and then basically add in the Interest charges (or 0 if there are none).

(From a In db.Payments 
Select a 
, TotalInterestRecieved = (From b in db.InterestCharges 
                           Where b.pid = a.pid
                           select b.Interest).DefaultIfEmpty(0).Sum() 
).ToList

---

EDIT:

Another option would be to bypass EF entirely. Build a view in the database and query that view directly rather than attempting to access the underlying data via LINQ.

Most other suggestions I would have would involve iterating through the initial list of "Payments" and populating the values as needed. Which is fine for a small number of "Payments" but that is a O(n) solution.

Force linq sum to return null

Here is the implementation of Sum()

public static int? Sum(this IEnumerable<int?> source) {
if (source == null) throw Error.ArgumentNull("source");
int sum = 0;
checked {
    foreach (int? v in source) {
        if (v != null) sum += v.GetValueOrDefault();
    }
}
return sum;

The reason for not returning null is the way it's implemented - the usage of int sum = 0; as result can never return null.

Excluding NULL values when using SUM() in LINQ

You just need to cast to a nullable int. The error is occuring because you are trying to sum no records and the result of that is null, but an int cant be null

.Select(tr => tr.NetGallons).Cast<int?>().Sum(),

or

  .Select(tr => (int?)tr.NetGallons).Sum(),

LINQ .SUM() and nullable db values

I'm surprised that fails, but an alternative which might work is simply to sum the nullable values and then use the null coalescing operator:

return expense.Sum(x => x.Mileage) ?? 0d;

Certainly in LINQ to Objects this would do the right thing, ignoring null values and giving you a null result (before the null coalescing operator) if there were no non-null values in the sequence.

Linq with sum with nullable int

you can try out

(from surveyResult in
 surveyResultsFromChats
where surveyResult.FirstScoreNPS.HasValue && surveyResult.GroupID == gr && 
surveyResult.FirstScoreNPS >= 9
   select surveyResult.FirstScoreNPS).
Select(score => score.Value ?? 0).Sum();

How to force LINQ Sum() to return 0 while source collection is empty

Try changing your query to this:

db.Leads.Where(l => l.Date.Day == date.Day
            && l.Date.Month == date.Month
            && l.Date.Year == date.Year
            && l.Property.Type == ProtectedPropertyType.Password
            && l.Property.PropertyId == PropertyId)
         .Select(l => l.Amount)
         .DefaultIfEmpty(0)
         .Sum();

This way, your query will only select the Amount field. If the collection is empty, it will return one element with the value of 0 and then the sum will be applied.

Linq sum and null

You can correct this by not returning int?, but rather convert to an int directly:

.Sum(v => v.Plays ?? 0)

---

Related Topics