Linq and DBNull - Getting Error

Question

I get an error when choosing from a row. 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") }); 

In most cases, this works well, but when there are NULLS in the database, I get this error. "You cannot use DBNull.Value to enter" System.Int16 ". Use a type with a null name .." How can I fix this? I don't want my datacontracts to be Nullable types, I would like to use ternary or something else, and if the value is NULL, just use 0. Is this possible?

Thanks for any help,
~ Ck

Answer 1

You can 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 caller looks like this:

 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") }); 

Answer 2

This is how you test zeros ...

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

Answer 3

If your property is nullable, you can also do this;

 Plaza = row.Field<int16?>("siFkPlazaId") 

Answer 4

I really love the operator ?? :

 CalculationCount = row.Field<Int64?>("biCalculationCountSeqID") ?? 0 , Zone = row.Field<Int16?>("siFkZoneId") ?? 0 , Miles = row.Field<decimal?>("dcMiles") ?? 0.0m , Plaza = row.Field<Int16?>("siFkPlazaId") ?? 0 , VehicleCount = row.Field<int>("iVehicleCount") 0;