none
LINQ to Datatable: Specified Cast Is Not Valid RRS feed

  • Question

  • Hi folks,

     

    I am having a problem using

    LINQ to datatable to map values fetched from an ADO tableadapter which was used to construct a dataset. The code in question is as follows:


    var tbInventory = from s in ds.Tables[3].AsEnumerable()

                                          select new

                                          {

                                              id = s.Field<string>("id"),

                                              assocObjId = s.Field<string>("assocObjId"),

                                              sku = s.Field<string>("sku"),

                                              merchantSKU = s.Field<string>("merchantSKU"),

                                              status = s.Field<string>("status"),

                                              onHand = s.Field<int>("onHand"),

                                              onOrder = s.Field<int>("onOrder"),

                                              price = s.Field<decimal?>("Price"),

                                              productWeight = s.Field<decimal?>("productWeight"),

                                              weightUnit = s.Field<string>("weightUnit"),

                                              fulfillmentIdentifier = s.Field<string>("fulfillmentIdentifier")

                                          };

     

    The offending line is:

     

     productWeight = s.Field<decimal?>("productWeight"),

     

    This corresponds to a column from a db Object named Inventory, and here is the data column definition :

     

    ProductWeight        decimal(19, 4)        nullable

     

    The line yields the following error, and when commented allows the code to run as expected:

     

    Specified cast is not valid.

    at System.Data.DataRowExtensions.UnboxT`1.NullableField[TElem](Object value) at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName) at SandBox.b__6(DataRow s) in d:\web\mywebsite\SandBox.aspx.cs:line 587 at System.Linq.Enumerable.d__d`2.MoveNext() at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source) at SandBox.btnRun_Click(Object sender, EventArgs e) in d:\web\mywebsite\SandBox.aspx.cs:line 624

    Can anyone offer any insight as to why this might be? Any help is appreciated.

    Thursday, February 12, 2009 4:41 AM

Answers

  •  Hey Jesse,

    Sorry the late reply. Things are fairly busy, anyway I believe the issue that you may be encountering is a mismatch of DataTypes. Please review the DataType of the DataColumn in the DataTable by calling dataColumn.DataType.ToString(). Then review the database and see what the datatype is in the database. What could be happening is that the number is being converted to another type in the Adatper code from one to the other but the specific conversion is causing the rounding problem. I'm thinking if you investigate this you will determine the issue.

    Thanks
    Chris Robinson
    Program Manager - DataSet 
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 24, 2009 10:59 PM

All replies

  • Hey Jesse,

    I tried to write something to repro your error but for some reason it did not surface. This is the code that I wrote

    static void Main(string[] args)  
            {  
                DataTable t = new DataTable("foo");  
                t.Columns.Add("id", typeof(string));  
                t.Columns.Add("assocObjId", typeof(string));  
                t.Columns.Add("sku", typeof(string));  
                t.Columns.Add("merchantSKU", typeof(string));  
                t.Columns.Add("status", typeof(string));  
                t.Columns.Add("onHand", typeof(int));  
                t.Columns.Add("onOrder", typeof(int));  
                t.Columns.Add("Price", typeof(decimal));  
                t.Columns["Price"].AllowDBNull = true;  
                t.Columns.Add("productWeight", typeof(decimal));  
                t.Columns["productWeight"].AllowDBNull = true;  
                t.Columns.Add("weightUnit", typeof(string));  
                t.Columns.Add("fulfillmentIdentifier", typeof(string));  
     
                t.Rows.Add("3", "af", "sdf", "sdf", "grwe", 3, 4, 3.4, 3.2, "25432", "5225");  
                t.Rows.Add("4", "af", "sdf", "sdf", "grwe", 3, 4, 3.4, 3.2, "25432", "5225");  
                t.Rows.Add("4", "af", "sdf", "sdf", "grwe", 3, 4, DBNull.Value, DBNull.Value, "25432", "5225");  
     
                var tbInventory = from s in t.AsEnumerable()  
                select new  
                {  
                    id = s.Field<string>("id"),  
                    assocObjId = s.Field<string>("assocObjId"),  
                    ssku = s.Field<string>("sku"),  
                    merchantSKU = s.Field<string>("merchantSKU"),  
                    sstatus = s.Field<string>("status"),  
                    onHand = s.Field<int>("onHand"),  
                    onOrder = s.Field<int>("onOrder"),  
                    price = s.Field<decimal?>("Price"),  
                    productWeight = s.Field<decimal?>("productWeight"),  
                    weightUnit = s.Field<string>("weightUnit"),  
                    fulfillmentIdentifier = s.Field<string>("fulfillmentIdentifier")  
                };  
                foreach (object o in tbInventory)  
                {  
                    Console.WriteLine(o.GetType());  
                }  
     
            } 

    I think the issue may be something around decimal?. In a DataTable there is not support for a decimal? we do support decimal and then mark the column as  AllowDBNull=true. I'm thinking there could be some type of data in a particular row that it is unable to do anything about. Otherwise I think your example is okay. Perhaps we can make changes in the code sample above to repro the issue you have so that we can understand it.

    Oh just curious why are you creating a List of anonymus types with the select new? What are you doing with this data after your select it?

    Thanks
    Chris Robinson
    Program Manager- DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, February 13, 2009 5:10 PM
  • Chris,

    Thanks for delving so deeply. I think one difference between your sample and my issue is I am creating my dataset from the db directly rather than in memory by forming an SqlCommand cmd and doing the following:

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds);

    Regarding the usage question: The idea at play is to pull tables of. say, images that pertain to various skus, and reassemble them into objects in memory. The anonymous typing was to get the datatable returned into something that could be easily queried, so we could perform something like the following pseudocode:

    foreach (skurow in skutable)
    {
    mySkuObject = new skuObject;
    mySkuObject = skuRow;
    mySkuObject.Images = (select images from imageTable where the image belongs to the sku)
    }
    We use this technique to piece together some complex objects, each comprised of other objects and lists.

    This issue may or may not be related to another issue: decimals are being rounded to a whole number (11.9900 becomes 12.0000) pulled from the db using the same technique above:

     price = s.Field<decimal?>("price"),



    If there is a more reliable way to achieve this I am all ears.

    Thanks for the help so far.

    Jesse Pakin
    Tuesday, February 17, 2009 3:29 PM
  •  Hey Jesse,

    Sorry the late reply. Things are fairly busy, anyway I believe the issue that you may be encountering is a mismatch of DataTypes. Please review the DataType of the DataColumn in the DataTable by calling dataColumn.DataType.ToString(). Then review the database and see what the datatype is in the database. What could be happening is that the number is being converted to another type in the Adatper code from one to the other but the specific conversion is causing the rounding problem. I'm thinking if you investigate this you will determine the issue.

    Thanks
    Chris Robinson
    Program Manager - DataSet 
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 24, 2009 10:59 PM