Linq: DataTable to List. Cast issue

Question

I'm getting the Specified cast is not valid. and I can't find why. Here's my code.

Object Layer.

public class Supervisor
{
    public long ID { get; set; }
    public string stringField1 { get; set; }
    public string stringField2 { get; set; }
    public string stringField3 { get; set; }
    public int intField1{ get; set; }        
    public int intField2 { get; set; }

}

C# Method.

[WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public static string GetPend(string value1FromjqGrid, string value2FromjqGrid)
    {
        string query = "GetPend";
        string supervisor = "";
        Supervision _query = new Supervision();             
        DataTable dt = _query.GetSupervisorQuery(value1FromjqGrid, value2FromjqGrid, supervisor, query );
        List<Supervisor> lines = (from dt1 in dt.AsEnumerable()
                               select new Supervisor()
                               { 
                                   ID = dt1.Field<long>("ID"),                                                                              
                                   stringField1 = dt1.Field<string>("Linea"),
                                   intField1 = dt1.Field<int>("Tiempo"),
                                   intField2 = dt1.Field<int>("TIPOACTIVIDAD_ID"),
                                   stringField2 = dt1.Field<string>("ACT_ID"),
                                   stringField3 = dt1.Field<string>("OBS")
                               }).ToList();
        var grid = new
        {
            page = 1,
            records = lines.Count(),
            total = lines.Count(),
            rows = from item in lines
                   select new
                   {
                       id = item.ID,
                       cell = new string[]{                               
                           item.stringField1,
                           item.intField1.ToString(),
                           item.intField2.ToString(),
                           item.stringField2,
                           item.stringField3
                       }
                   }
        };
        return JsonConvert.SerializeObject(grid); 
    }   

That's it more or less. When the LinQ iteration starts it crashes. The DataTable is filled correctly as I've checked, and dt1 contains the fields correctly. I see the "" for the string columns and the numbers for the int's (I've done the SQL Stored Procedure myself so I did the checking there too.) With this I'm also assuring that the 2 parameters from the jqGrid are OK, but still I placed some alerts just right before the calling and yes, they are fine.

I've pasted the code it seems to be relevant since the error comes when the codes trying to parse the information from the DataTable to the List, if you'd need to check the javascript involved here just let me know but I don't think that's needed. Clearly I'm not seeing something so hopefully you can guide me in the right direction. Thanks.

PS. I've tried to check it out at LINQPad4 but I can't give it a try since I don't know how to represent the original DataTable variable there.

Update.

This is what VS gives to me when I expand the error.:

   at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)
   at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName)
   at WEB.Supervisor.<GetPend>b__b(DataRow dt1) in       E:\Dev\VS\WEB\Supervisor.aspx.cs:line 110
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at WEB.Supervisor.GetPend(String value1FromjqGrid, String value2FromjqGrid) in E:\Dev\VS\WEB\Supervisor.aspx.cs:line 109

Lines 109 and 110 are these

List<Supervisor> lines = (from dt1 in dt.AsEnumerable()
                           select new Supervisor()

It crashes at the beggining of the convert process.

UPDATE 2

According to comments I did as follows.

Took the SELECT and turned into a SELECT INTO for generating a trash table. Then checked its design and for my honest surprise, the field CAST(ACT_ID AS NVARCHAR(50)) was still decimal and not nvarchar as I expected.

So, seems like I have to handle this in LinQ as a decimal, or could I do something else? I've tried working with decimals in the past and didn't succed.

Answer 1

It should be type mismatch between column type in the database and the type of data used in List<Supervisor> lines = (from dt1 in dt.AsEnumerable() select new Supervisor() {...}).ToList();.

After some conversation in comments we could see that the type of "ACT_ID" is decimal in the database. So to fix the exception problem one can do something like the following:

List<Supervisor> lines = (from dt1 in dt.AsEnumerable()
                          select new Supervisor {
                              ID = dt1.Field<long>("ID"),
                              stringField1 = dt1.Field<string>("Linea"),
                              intField1 = dt1.Field<int>("Tiempo"),
                              intField2 = dt1.Field<int>("TIPOACTIVIDAD_ID"),
                              stringField2 = dt1.Field<decimal>("ACT_ID").ToString(),
                              stringField3 = dt1.Field<string>("OBS")
                          }).ToList();