DataTableExtensions.AsEnumerable() throws InvalidCastException

Question

I'm using the DataTableExtensions.AsEnumerable method to create a List . The following code is throwing an InvalidCastException:

SaleDiscount = (i.Field<decimal>("OnSalePercentAdjustment") * i.Field<decimal>("Price")), 

I don't have to use LINQ, With a few more keystrokes I can work through the data table with a foreach loop:

i.Price = Convert.ToDecimal(row["Price"]);
var saleDiscount = Convert.ToDecimal(row["SaleDiscount "]);
i.SalePrice = i.Price - (i.Price * saleDiscount );
i.SaleDiscount = i.Price - i.SalePrice;

Just curious, how can I fix the LINQ? And wondering whether using LINQ here delivers any advantages over some saved keystrokes.

Answer 1

You get cast exceptions in one case and no exceptions in the other because Convert.ToDecimal is a lot more lenient than a simple cast. Cast exception indicates that the underlying field is not a decimal, but it has a type that can be easily converted to decimal.

To use Convert.ToDecimal inside LINQ query, change your code as follows:

SaleDiscount = (
           Convert.ToDecimal(i.Field<object>("OnSalePercentAdjustment")) *
           Convert.ToDecimal(i.Field<object>("Price"))), 

Alternatively, you could figure out the correct type, use Field<correct-type>(...) to read it, and then do the cast to decimal.

Answer 2

It's because one of your columns is not decimal.

It may be e.g. int, and even it's still numeric, and you can cast int to decimal, because .Field<T> logic looks like that:

return (T)((object)value);

and because you can only unbox value into the same they were boxed from:

int value = 3;
object boxedValue = value;

// works fine
int unboxedValue = (int)boxedValue;
// throws exception
double unboxedValueAsDouble = (double)boxedValue;

yu get InvalidCastException. Convert.ToDecimal looks different:

return ((IConvertible)value).ToDecimal(null);

And that's why it works.