CODEWITHSUNDEEP
c#sql-serverentity-frameworklinq
user5283666
user5283666

Reputation:

How can I set a zero-value instead of null in LINQ Select Sum

Im trying to do a query with LINQ to my EF Code First database to retrieve sales for the whole year and return a new DTO object that consists of the Month-number and the total sum of sales that month.

My query works fine, but the months that contains no sales returns null instead of "0", which creates problems in my View.

I have tried the null coalescing ?? but get a compile error that its not valid for a decimal. I've also tried the DefaultIfEmpty but this does not seem to work together with the .Sum-method.

Any suggestions?

Model:

public class YearlySalesDto
{
    public string Month { get; set; }
    public decimal TotalSales { get; set; }
}

Query:

[AllowAnonymous]
public IEnumerable<YearlySalesDto> Index()
{
    var result = from r in db.Orders
                 group r by r.OrderDate.Month into g
                 select new YearlySalesDto { Month = g.Key.ToString() ?? "", TotalSales = g.Sum(p => p.OrderItems.Select(x => x.Quantity * x.UnitPrice).Sum()) };

    if (result == null)
    {
        throw new HttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
    }

    return result;
}

Result from this query, since I only have sales in the DB from months 3 and 4.

[
  {
    "Month": "3",
    "TotalSales": 158
  },
  {
    "Month": "4",
    "TotalSales": 373
  }
]

Upvotes: 0

Views: 2092

Answers (2)

Gilad Green
Gilad Green

Reputation: 37299

The compilation error you received is explained by @Scott in the comment above.

As for having a zero value for months what are not in the database left join the results with a collection of months and in case of null place 0:

var result = from m in Enumerable.Range(1,12)
             join g in db.Orders.GroupBy(r => r.OrderDate.Month) on m equals g.Key into joint
             from g in joint.DefaultIfEmpty()
             select new YearlySalesDto { 
                 Month = m.ToString(),
                 TotalSales = g == null ? 0 :  g.Sum(p => p.OrderItems.SelectMany(x => x.Quantity * x.UnitPrice))
             };

Upvotes: 3

Scott Chamberlain
Scott Chamberlain

Reputation: 127543

Your select has nothing to do with this problem, you could get rid of it and still see the issue, GroupBy does not return values where g.Count == 0, what you need to do is add placeholder entries for the months with no rows in db.Orders with 0 as a total and merge that in with your results.

var result = from r in db.Orders
             group r by r.OrderDate.Month
             into g
             select new YearlySalesDto { 
                Month = g.Key.ToString(), // The  ?? "" is unnecessary. 
                TotalSales = g.Sum(p => p.OrderItems.Select(x => x.Quantity * x.UnitPrice).Sum()) 
            };

var fixedResult = result.AsEnumerable()
                        .Union(Enumerable.Range(1,12)
                                         .Select(x=>new YearlySalesDto { Month = x.ToString(), TotalSales = 0 })
                               , new MonthComparer());


//Elsewhere
class MonthComparer : IEqualityComparer<YearlySalesDto>
{
    public bool Equals(YearlySalesDto x, YearlySalesDto y)
    {
        return x.Month == y.Month;
    }
    public int GetHashCode(YearlySalesDto x)
    {
        return x.Month.GetHashCode();
    }
}

Upvotes: 3

Related Questions