[RESOLVED] LINQ To Dataset - How to handle DBNull?

[Pradeep1210]

This is what I'm doing to select and display 3 columns of my datatable in GridView:

        Dim myCustomers = From Cust In myDataset.Customers _
                          Select CustomerId = Cust.CustomerId, _
                                 CustomerName = Cust.CustomerName, _
                                 ExpiryDate = Cust.ExpiryDate
        DataGridView1.DataSource = myCustomers.ToList

The expiry date has null values in many records whose expiry date is not set.
On executing the query I get runtime error:

System.Data.StrongTypingException
"The value for column 'ExpiryDate' in table 'Customers' is DBNull."


Obviously, my LINQ query is wrong or incomplete, but I am not able to figure the way to do it correctly.

Pradeep

[mendhak]

Run through the dataset before querying it. Or have your stored procedure perform ISNULL() checks and return values.

[Pradeep1210]

Run through the dataset before querying it. Or have your stored procedure perform ISNULL() checks and return values.

That was the old ADO.NET way. How should I do that using LINQ query?
I found many examples of this on the internet but all are in C#, not a single example in VB.

[kevchadders]

What code are you using to populate the dataset before you query it?

[mendhak]

It wasn't "the old ADO.NET way", it was a programming practice. Using LINQ does not necessarily imply that you use it as an excuse for bad practices.

What you can do, if you wish to do this the "new LINQ way" is to declare ExpiryDate as Nullable(Of Datetime) so that it then holds null.

[Pradeep1210]

It wasn't "the old ADO.NET way", it was a programming practice. Using LINQ does not necessarily imply that you use it as an excuse for bad practices.

What you can do, if you wish to do this the "new LINQ way" is to declare ExpiryDate as Nullable(Of Datetime) so that it then holds null.

By "the old ADO.NET way", I just meant what I have been doing till now. But now I'm trying to learn doing it with new technology

declare ExpiryDate as Nullable(Of Datetime)

This is exactly what I'm looking for.
Can you please help me with an example in VB?

Pradeep

[Pradeep1210]

What code are you using to populate the dataset before you query it?

Fill Method.

[Pradeep1210]

[jmcilhinney]

The problem is not your LINQ code at all. It's the fact that you're access the Cust.ExpiryDate property when your row contains a null value. Cust is a typed DataRow so each property is typed to the data it contains. Your ExpiryDate column contains Date values so the type of the ExpiryDate property is Date. A Property of type Date cannot return a value of type DBNull.

Typed DataRows always provide a method for columns that can contain nulls so you can test the value first, e.g.

If Cust.IsExpiryDateNull() Then
    'The ExpiryDate column contains null so accessing the ExpiryDate property would throw an exception.
Else
    'You can safely access the ExpiryDate property because you know it has a value.
End If

As far as I'm aware you cannot allow for this in LINQ using the typed properties of your DataRow. Of course, my knowledge of LINQ is far from complete, but if you use the row as though it's untyped then null values will not hurt you:

        Dim myCustomers = From Cust In myDataset.Customers _
                          Select CustomerId = Cust.CustomerId, _
                                 CustomerName = Cust.CustomerName, _
                                 ExpiryDate = Cust("ExpiryDate")
        DataGridView1.DataSource = myCustomers.ToList

[Pradeep1210]

Great Info.
Thanks.

[jmcilhinney]

As I now know, the Fields extension method on the DataRow class can handle null values. I worked that out by reading the documentation but I think that doco contains an error because it doesn't actually use nullable types in the code example. That original code should be redone like this:

        Dim myCustomers = From Cust In myDataset.Customers _
                          Select CustomerId = Cust.CustomerId, _
                                 CustomerName = Cust.CustomerName, _
                                 ExpiryDate = Cust.Field(Of Date?)("ExpiryDate")
        DataGridView1.DataSource = myCustomers.ToList

Because the generic type of the Field method is nullable it can handle null values.

[Pradeep1210]

Great info. Really enlightening.

Thanks.

[jmclej]

That was the old ADO.NET way. How should I do that using LINQ query?
I found many examples of this on the internet but all are in C#, not a single example in VB.

Hi,
I have exactly the same issue in C#, but I don't find all the examples you are talking about, can you give me some please?
I have a dataset containing a datatable containing columns that are set to DateTime or double, but impossible to set the nullable types "DateTime?" or "double?". And eventhough AllowDBNull is set to True, I have the same error as you when retrieving rows having null values. I see that I have access to the method IsColNameNull(), but I don't know how to use it in my LINQ query. And the thing is that I don't want to do something else when I have null values, I do want to insert null.
Or if you could give me the syntax of your vb code in c#, that would be great.
Thanks.

[jmclej]

As I now know, the Fields extension method on the DataRow class can handle null values. I worked that out by reading the documentation but I think that doco contains an error because it doesn't actually use nullable types in the code example. That original code should be redone like this:

        Dim myCustomers = From Cust In myDataset.Customers _
                          Select CustomerId = Cust.CustomerId, _
                                 CustomerName = Cust.CustomerName, _
                                 ExpiryDate = Cust.Field(Of Date?)("ExpiryDate")
        DataGridView1.DataSource = myCustomers.ToList

Because the generic type of the Field method is nullable it can handle null values.

I have managed to write this in c#. It solves indeed the linq problem.