The cast to value type 'Double' failed because the materialized value is null

Question

CODE:

double cafeSales = db.InvoiceLines
    .Where(x =>
        x.UserId == user.UserId &&
        x.DateCharged >= dateStart &&
        x.DateCharged <= dateEnd)
    .Sum(x => x.Quantity * x.Price);

ERROR:

The cast to value type 'Double' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

WHAT I HAVE SEEN ALREADY:

The cast to value type 'Int32' failed because the materialized value is null

The cast to value type 'Decimal' failed because the materialized value is null

WHAT I HAVE TRIED:

double cafeSales = db.InvoiceLines
    .Where(x =>
        x.UserId == user.UserId &&
        x.DateCharged >= dateStart &&
        x.DateCharged <= dateEnd)
    .DefaultIfEmpty()
    .Sum(x => x.Quantity * x.Price);

And:

double? cafeSales = db.InvoiceLines
    .Where(x =>
        x.UserId == user.UserId &&
        x.DateCharged >= dateStart &&
        x.DateCharged <= dateEnd)
    .Sum(x => x.Quantity * x.Price);

Neither of these work. I know the cause of the problem is that there are no rows in that table for the UserId I am passing in. In that case, I would prefer Sum() just returned a 0 to me. Any ideas?

You should mark as answer from any below solutions which is most suitable and efficient. — Jitendra Pancholi, Dec 29 '14 at 10:53
You should accept an answer from below for others to rely on. — Jitendra Pancholi, Jun 08 '15 at 04:26
Possible duplicate of [The cast to value type 'Int32' failed because the materialized value is null](http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) — Michael Freidgeim, Jul 13 '16 at 04:13

Jitendra Pancholi · Answer 1 · 2016-07-13T05:52:24.987

72

Best Solution

double cafeSales = db.InvoiceLines
                     .Where(x =>
                                x.UserId == user.UserId &&
                                x.DateCharged >= dateStart &&
                                x.DateCharged <= dateEnd)
                     .Sum(x => (double?)(x.Quantity * x.Price)) ?? 0;

edited Jul 13 '16 at 05:52

answered Oct 22 '13 at 06:26

Jitendra Pancholi

7,897
12
51
84

1

Great! That the way to go. – Mahmood Dehghan Dec 28 '13 at 12:27
1

Worked brilliantly !! Thanks @jitendra – Avishekh Bharati Oct 27 '15 at 16:32

score 8 · Answer 2 · answered Mar 08 '13 at 15:54

8

You can check if the collection has any correct results.

double? cafeSales = null;
var invoices = db.InvoiceLines
    .Where(x =>
        x.UserId == user.UserId &&
        x.DateCharged >= dateStart &&
        x.DateCharged <= dateEnd
    )
    .Where(x => x.Quantity != null && x.Price != null);
if (invoices.Any()) {
    cafeSales = invoices.Sum(x => x.Quantity * x.Price);
}

answered Mar 08 '13 at 15:54

Maarten

22,527
3
47
68

I did come up with something similar myself just now, but I was hoping there'd be a cleaner way to do it.. in one statement. – Matt Mar 08 '13 at 16:01
2

The problem with this approach is that 2 queries get send to the db, first for the Any() to see if there are any records and then the second record to do the actual sum(). – Peter Huber Jun 14 '15 at 08:10
There are no 2 queries but only one. The `Where` method does not actually make a query but prepares one and the call to `Any` makes the real db-query. Any way I prefer the @Jitendra Pancholi solution, which makes @Maarten two statements in one with the `??` operator. – David Silva-Barrera Aug 11 '18 at 03:44

score 4 · Answer 3 · edited Jun 07 '14 at 16:28

4

I know this is a bit old but just in case it helps anyone.

@Matt I guess the DefaultIFEmpty() method should work for you just in case you pass a default value for the column that you are applying Sum onto. This method has some overloads which you might want to check and I suggest type-casting if overloads do not support your requirement.

 (query).DefaultIfEmpty(0)

edited Jun 07 '14 at 16:28

Meryovi

6,121
5
42
65

answered Jul 23 '13 at 07:46

Jayant Shelke

137
1
7

It helped me. This is a great way to go. – Meryovi Jun 07 '14 at 16:25
It helps and sends only one query to the db, but a MOST ugly one ! Without DefaultIfEmpty it is just a simple SELECT statement, whereas with DefaultIfEmpty the generated query uses 4 SELECTs. Instead checking in the db if there are any request, it should be possible to test in the C# client to test if the query has returned any records. – Peter Huber Jun 14 '15 at 08:28

score 3 · Answer 4 · answered Mar 08 '13 at 15:59

This should do the trick (you may have to remove one of the conditions if either Quantity or Price are not nullable):

var cafeSales = db.InvoiceLines
    .Where(x =>
        x.UserId == user.UserId &&
        x.DateCharged >= dateStart &&
        x.DateCharged <= dateEnd &&
        x.Quantity != null &&
        x.Price != null);

double cafeSalesTotal = 0;

if (cafeSales.Any())
{
    cafeSalesTotal = cafeSales.Sum(x => x.Quantity * x.Price);
}

score 1 · Answer 5 · edited Nov 22 '16 at 08:17

join sim in ctx.EF.Collaterals on new { id = ini.cam.id, Type = 0 } equals new 
{ id = sim.CampaignId == null ? new Guid() : sim.CampaignId, sim.Type } 
into tempcoll
from sim in tempcoll.DefaultIfEmpty()

This solution works.Actually You need to use ternary operator to check the value and insert Guid if null in the second column and in the second table.and it will work. "The cast to value type 'Double' failed because the materialized value is null" will be solved Thanks

score 0 · Answer 6 · answered Mar 08 '13 at 15:56

0

 var cafeSales = db.InvoiceLines
.Where(x =>
    x.UserId == user.UserId &&
    x.DateCharged >= dateStart &&
    x.DateCharged <= dateEnd)
.Sum(x => x.Quantity * x.Price);

double i;
if(cafeSales==null) ? i=0 : i=(double)cafeSales.First();

answered Mar 08 '13 at 15:56

Lotok

4,517
1
34
44

1

Sum(...) will not return an enumerable, so calling a First() on the result of a Sum(...) will turn into a compile-time-error. – Maarten Mar 08 '13 at 16:00
does it make any sense? :o if(cafeSales==null) ? i=0 : i=(double)cafeSales.First(); – Michael Brennt May 20 '14 at 09:53

score 0 · Answer 7 · answered Jun 11 '15 at 08:47

0

The solutions above didn't work for me. My problem was similar. I was sure that no rows were being returned but Sum behaves in some strange way. So I decided to add a check just before calling the lambda expression where I check for count property of rows returned by the lambda. If it is greater than zero, then I call the sum expression. That worked for me.

answered Jun 11 '15 at 08:47

Bryida

482
6
9

TimeSheetRepository.GetAll().Where(t => t.UserID == theUserId). Where(t => t.Date >= startDate).Where(t => t.Date <= endDate).Sum(t => t.Hours); – Bryida Jun 16 '15 at 08:43
I had to check if there is a count like this TimeSheetRepository.GetAll().Where(t => t.UserID == theUserId). Where(t => t.Date >= startDate).Where(t => t.Date <= endDate).Count() > 0 Calling the .Sum for the hours which is a decimal always resulted in the above error when no rows were present in the resultset – Bryida Jun 16 '15 at 08:43

Mike · Answer 8 · 2020-02-23T16:47:25.360

.NET 4.0, Nullable has a "GetValueOrDefault()" method. So if you cast the query to Nullable, then you can end up with the correct Type when complete. This method will also generate the correct single SQL SELECT SUM query and is faster than other solutions that return the entire recordset to later sum via linq

decimal result = ((decimal?)query.Where(w => w.Customer =="ABC").Sum(s =>  (decimal?)s.Amount)).GetValueOrDefault();

The cast to value type 'Double' failed because the materialized value is null

8 Answers8

Linked