LINQ Sum of prices in each month in loop?

Question

So I'm trying to have a list of profits in each month from my database. I want to do this in loop, I thnik it will be the best soulution.

Here we have a loop, that I want to count a profit for each month.

        using (var context = new ModelContext("ConnectionStringDbMagazynier"))
        {
            for (int i = 0; i < 12; i++)
            {

                decimal q = (from ar in context.Archives
                             where ar.SalesDate <= DateTime.Now.AddYears(-1).AddMonths(i + 1) && ar.SalesDate >= DateTime.Now.AddYears(-1).AddMonths(i)
                             let sum = context.Archiwum.Sum(x => x.Price)
                             select sum);

                profits[i] = decimal.ToDouble(q);

            }
        }

from this query i get an error:

Error   2   Cannot implicitly convert type 'System.Linq.IQueryable<decimal>' to 'decimal'

My question is, how to make it witohut error? and is this solution ok? What in case, that i didn't sell anything in partiuclar month and the sum is null?

with .Any() i get "Cannot implicitly convert type 'bool' to 'decimal'" — johns, Nov 01 '13 at 21:49
Sorry. Try `.Single()` http://stackoverflow.com/questions/1544322/returning-a-single-value-with-linq-to-sql — Robert Harvey, Nov 01 '13 at 21:50
Better still, .SingleOrDefault() so the result will be 0 if there are no sales that month. — James Gaunt, Nov 01 '13 at 21:51
Hi, thanks, for help, i know it would be easier, the case is i have to do this in LINQ, that's the asigment — johns, Nov 01 '13 at 22:07
You're querying from two tables, but I don't see any coorelation defined between them. — James, Nov 01 '13 at 22:16

Rob Lyndon · Answer 1 · 2013-11-01T22:03:10.070

1

It's easier to use lambda syntax in this case.

var q = context.Archives
    .Where(ar => ar.SalesDate <= DateTime.Now.AddYears(-1).AddMonths(i + 1) && ar.SalesDate >= DateTime.Now.AddYears(-1).AddMonths(i))
    .Sum(x => x.Price);

Or if you really like the query syntax

var records = (from ar in context.Archives
    where ar.SalesDate <= DateTime.Now.AddYears(-1).AddMonths(i + 1) && ar.SalesDate >= DateTime.Now.AddYears(-1).AddMonths(i)
    select ar);

profits[i] = records.Sum(x => x.Price);

edited Nov 01 '13 at 22:03

answered Nov 01 '13 at 21:52

Rob Lyndon

12,089
5
49
74

i get this "The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type." – johns Nov 01 '13 at 21:56
Can the `Price` field be null? If it can, use `x => x.Price.GetValueOrDefault()`. – Rob Lyndon Nov 01 '13 at 21:58
it can't be, but what if i didn't sell anything that month? so mu sum get's nothing, right? – johns Nov 01 '13 at 22:03
1

In theory, Sum should give you zero if you don't have any records, but sometimes Linq to Sql fails to achieve this. Either put a `ToList()` at the end of your where clause (on the line before `.Sum(x => x.Price)` for the lambda syntax and at the end of the definition of `records` in the query syntax), or use `Sum(x => x.Price) ?? 0`. – Rob Lyndon Nov 01 '13 at 22:07
x => x.Price.GetValueOrDefault() I cant do sth like this, only GetType, CompareTo, Equals... – johns Nov 01 '13 at 22:08
You won't if `Price` can't be null. – Rob Lyndon Nov 01 '13 at 22:09

sh1ng · Answer 2 · 2013-11-02T19:09:24.710

var q = context.Archives
.Where(p => p.SalesDate <= DateTime.Now.AddYears(-1).AddMonths(i + 1) && p.SalesDate >= DateTime.Now.AddYears(-1).AddMonths(i))
.Sum(x => x.Price.HasValue ? x.Price.Value : 0);

It's possible get month stat without loop

  var grouped = context.Archives.Where(p=> p.SalesDate <= DateTime.Now.AddYear(-1))
.GroupBy(k => new {Year = k.Year, Month = k.Month})
.Select(p=> {Date = p.Key, Sum = p.Sum(x=> x.Price.HasValue ? x.Price.Value : 0)})

LINQ Sum of prices in each month in loop?

2 Answers2