Linq Query Handling Null Values

Question

From r In ReceiptLines
Where 
r.RECEIPT.RECEIPTDATE >= _reportStartDate 
And r.RECEIPT.RECEIPTDATE <= _reportEndDate
Let amount = r.QUANTITY * r.PRICE
Let discount = r.RECEIPTDISCOUNTs.Sum(Function(d) d.DISCOUNT) 
where discount > 0
Group By Department = r.ITEMSTYLE.ITEM.CATEGORY.DEPARTMENT.DEPARTMENTNAME
Into Sales = Sum(amount - discount), 
Average = Average(amount - discount), 
Count = Count()

I am fetching all departments and their sales, average, count from the ReceiptLine, Receipt, ReceiptDiscount tables. The problem i am facing is, if i remove where discount > 0, I am getting null exception. But if I include that, then I only get sales that has discount. How would I write query that bring all sales less discount (if it has one). Any help is highly appreciated.

It looks like your code is VB.NET. I added that tag, if that is wrong you can correct that. — Albin Sunnanbo
– Albin Sunnanbo, Commented Nov 12, 2011 at 18:29

Albin Sunnanbo · Accepted Answer · 2011-11-12 18:19:32Z

8

This is a common pitfall with LINQ2SQL.

The function SUM in SQL returns null if there are no items in the collection, but the signature of Enumerable.Sum() returns an int. This gives a runtime exception when the SQL query return null where the LINQ2SQL provider expects an integer.

The solution is to cast the result of the sum to a nullable integer and use GetValueOrDefault to convert the null-case to 0.

Replace

Let discount = r.RECEIPTDISCOUNTs.Sum(Function(d) d.DISCOUNT)

with

Let discount = CType(r.RECEIPTDISCOUNTs.Sum(Function(d) d.DISCOUNT), Integer?).GetValueOrDefault(0)

answered Nov 12, 2011 at 18:19

Albin Sunnanbo

47.2k8 gold badges72 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kamyar · Accepted Answer · 2011-11-12 20:25:16Z

1

Have you tried:

...
Let amount = r.QUANTITY * r.PRICE
Let nDiscount = r.RECEIPTDISCOUNTs.Sum(Function(d) d.DISCOUNT) 
Let discount = IIf(nDiscount == Nothing, 0, nDiscount)
Group By Department = r.ITEMSTYLE.ITEM.CATEGORY.DEPARTMENT.DEPARTMENTNAME
...

edited Nov 12, 2011 at 20:25

answered Nov 12, 2011 at 18:16

Kamyar

18.8k9 gold badges101 silver badges174 bronze badges

3 Comments

Albin Sunnanbo Over a year ago

I think Muthu's code looks like VB.NET and the ternary operator is pronounced IF(Condition, TrueValue, FalseValue) in VB.NET.

Kamyar Over a year ago

Yep! got used to code in c# for a long time. Haven't noticed that. thanks.

Albin Sunnanbo Over a year ago

and... null is spelled Nothing in VB.NET. Writing VB.NET when you are used to c# feels like you have poured two cups of coffee in you keyboard. You just type along as usual, but you get red squiggles everywhere.

Collectives™ on Stack Overflow

Linq Query Handling Null Values

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related