Linq and DBNull - Getting error

Question

Hi All, I'm getting an error when selecting from a rows.AsEnumerable(). I am using the following code...

var rows = ds.Tables[0].AsEnumerable();
                trafficData = rows.Select(row => new tdDataDC
                {
                    CalculationCount = row.Field<Int64>("biCalculationCountSeqID")
                    , Zone = row.Field<Int16>("siFkZoneId")
                    , Miles = row.Field<decimal>("dcMiles")
                    , Plaza = row.Field<Int16>("siFkPlazaId")
                    , VehicleCount = row.Field<int>("iVehicleCount")


                });

Most of the time it works well, but when there are NULLS in the database I'm getting this error "Cannot cast DBNull.Value to type 'System.Int16'. Please use a nullable type.." How can I correct this? I don't want my datacontracts to have Nullable types, I'd like to use a ternary or something, and if a value is NULL, just use 0. Is this possible?

Thanks for any help,
~ck

Answer 1

Here is how you test for nulls...

Plaza = row.IsNull("siFkPlazaId") ? 0 : row.Field<int>("siFkPlazaId")

Answer 2

You could always add another extension method (untested):

   public static T FieldOrDefault<T>(this DataRow row, string columnName)
   {
       return row.IsNull(columnName) ? default(T) : row.Field<T>(columnName);   
   }

Then your callsite looks like:

var rows = ds.Tables[0].AsEnumerable();
                trafficData = rows.Select(row => new tdDataDC
                {
                    CalculationCount = row.FieldOrDefault<Int64>("biCalculationCountSeqID")
                    , Zone = row.FieldOrDefault<Int16>("siFkZoneId")
                    , Miles = row.FieldOrDefault<decimal>("dcMiles")
                    , Plaza = row.FieldOrDefault<Int16>("siFkPlazaId")
                    , VehicleCount = row.FieldOrDefault<int>("iVehicleCount")


                });