Filtering DBNull With LINQ

dbnull, linq, null, vb.net

Solution

By default, in strongly typed datasets, properties throw that exception if the field is null. You need to use the generated `Is[Field]Null` method :

Dim query = From row As dbDataSet.conformalRow In dbDataSet.Tables("conformal") _
            Where Not row.IsCalNull() AndAlso tiCal_drop.Text = row.Cal _
            AndAlso Not row.IsTranNull() AndAlso tiTrans_drop.Text = row.Tran _
            AndAlso Not row.IsbarrelNull() _
            Select row.barrel
If query.Count() > 0 Then tiBarrel_txt.Text = query(0)

Or the DataRow.IsNull method :

Dim query = From row As dbDataSet.conformalRow In dbDataSet.Tables("conformal") _
            Where Not row.IsNull("Cal") AndAlso tiCal_drop.Text = row.Cal _
            AndAlso Not row.IsNull("Tran") AndAlso tiTrans_drop.Text = row.Tran _
            AndAlso Not row.IsNull("barrel") _
            Select row.barrel
If query.Count() > 0 Then tiBarrel_txt.Text = query(0)

Problem

Why does the following query raise the error below for a row with a `NULL` value for barrel when I explicitly filter out those rows in the `Where` clause? ``` Dim query = From row As dbDataSet.conformalRow In dbDataSet.Tables("conformal") _ Where Not IsDBNull(row.Cal) AndAlso tiCal_drop.Text = row.Cal _ AndAlso Not IsDBNull(row.Tran) AndAlso tiTrans_drop.Text = row.Tran _ AndAlso Not IsDBNull(row.barrel) _ Select row.barrel If query.Count() > 0 Then tiBarrel_txt.Text = query(0) ``` `Run-time exception thrown : System.Data.StrongTypingException - The value for column 'barrel' in table 'conformal' is DBNull.` How should my query / condition be rewritten to work as I intended?

Original source