LINQ To Dataset : Handling Null Values
January 19th, 2008Exception Occurs when aggregating a table with null values.
The following code currently causes an exception with a null value exception if there are null ItemAmountIncVAT values within the table InvoiceItem;
Dim totalIncVat = Aggregate InvoiceItem In Me.DataSetInvoice.InvoiceItem _
Where InvoiceItem.InvoiceID = row.InvoiceID AndAlso_
Not IsDBNull(InvoiceItem.ItemAmountIncVAT) _
Into Sum(InvoiceItem.ItemAmountIncVAT)
To get around this we have to either use:
Dim totalOrigTranlatedAttempt = Aggregate InvoiceItem In Me.DataSetInvoice.InvoiceItem _
Where InvoiceItem.Field(Of Decimal)(“ItemAmountIncVAT”) > 0 _
Into Sum(InvoiceItem.ItemAmountIncVAT)
Or set the nullValue to 0 for ItemAmountIncVAT within the dataset designer.
So we have a choice if we want to have intellisense with our LINQ to dataset we must set the nullValue to 0 (in dataSet designer) instead of throwing an exception (so we lose the ability to have nulls) or we lose the intellisense.
With the date field the nullValue cannot be changed in the dataset designer so we have to use;
Dim totalVat = Aggregate Invoice In Me.DataSetInvoice.Invoice _
Where Not IsDBNull(Invoice.Field(Of Nullable(Of Date))(“DateDue”)) _
Into Sum(Invoice.InvoiceVAT)