LINQ query on datatable fails with speciifed cast invalid

      This works        
var fxRates = from rates in dsRatesDates.Tables[0].AsEnumerable()
                              group rates by new { BusinessDate = rates[Constants.BusinessDate], PositionID = rates[Constants.PositionID], FXRate = rates[Constants.ClosingFXRate], Currency = rates[Constants.Currency] } into groupedTable
                              where Convert.ToString(groupedTable.Key.PositionID) == row.PositionID && Convert.ToDateTime(groupedTable.Key.BusinessDate).Date == row.BusDate
                              select new
                              {
                                  FXRate = groupedTable.Key.FXRate,
                                  Currency = groupedTable.Key.Currency
                              };

Biut this fails with -Specified cast is invalid

 var fxRates = (from rates in dsRatesDates.Tables[0].AsEnumerable()
                               where Convert.ToString(rates.Field<string>(Constants.PositionID)) == row.PositionID && Convert.ToDateTime(rates.Field<DateTime>(Constants.BusinessDate)) == row.BusDate
                               select new
                               {
                                   FXRate = rates.Field<float>(Constants.ClosingFXRate),
                                   Currency = rates.Field<string>(Constants.Currency)
                               }).SingleOrDefault();

I have checked that the datatype are fine
                var fxRates = (from rates in dsRatesDates.Tables[0].AsEnumerable()
                               where Convert.ToString(rates.Field<string>(Constants.PositionID)) == row.PositionID && Convert.ToDateTime(rates.Field<DateTime>(Constants.BusinessDate)) == row.BusDate
                               select new
                               {
                                   FXRate = rates.Field<float>(Constants.ClosingFXRate),
                                   Currency = rates.Field<string>(Constants.Currency)
                               }).SingleOrDefault();
LVL 1
countrymeisterAsked:
Who is Participating?
 
Meir RivkinFull stack Software EngineerCommented:
instead of this line:
FXRate = rates.Field<float>(Constants.ClosingFXRate),

try this line instead:
FXRate = rates[Constants.ClosingFXRate],

the difference is there's no type checking here.
if you would have use foreach to loop through dsRatesDates.Tables[0] rather than using enumerable, you could detect the type of the field:

for example:

foreach (var item in dsRatesDates.Tables[0] .AsEnumerable())
            {
                var value = item[Constants.ClosingFXRate),
            }

if u debug this code and stop after getting the value, you would see in Watch view if using visual studio) that the value's type if "object {float}" (or whatever the field type is).
and then once u know the type of the field u can go back and use the syntax:
FXRate = rates.Field<float>(Constants.ClosingFXRate)



0
 
Meir RivkinFull stack Software EngineerCommented:
when i encounter errors like this i'm trying to simplify the linq in order to get into the source of the error.
so if this is the code which triggers the error:

  var fxRates = (from rates in dsRatesDates.Tables[0].AsEnumerable()
                               where Convert.ToString(rates.Field<string>(Constants.PositionID)) == row.PositionID && Convert.ToDateTime(rates.Field<DateTime>(Constants.BusinessDate)) == row.BusDate
                               select new
                               {
                                   FXRate = rates.Field<float>(Constants.ClosingFXRate),
                                   Currency = rates.Field<string>(Constants.Currency)
                               }).SingleOrDefault();

i'll first verify if the problem is in the "select new" part of the linq, so instead of this part replace it with something simple like:
  var fxRates = (from rates in dsRatesDates.Tables[0].AsEnumerable()
                               where Convert.ToString(rates.Field<string>(Constants.PositionID)) == row.PositionID && Convert.ToDateTime(rates.Field<DateTime>(Constants.BusinessDate)) == row.BusDate
                               select String.Empty;

if error occurs we need to simplify the 1st part, otherwise, simplify 2nd part.
the 1st part could be implemented uing the traditional foreach statement.
0
 
Meir RivkinFull stack Software EngineerCommented:
so convert the 1st part to foreach and see if it triggers any errors.
this approach enables u to debug line by line as oppose to linq usage which makes it easier to find the error.
0
 
countrymeisterAuthor Commented:
the error occurs on this statement,
FXRate = rates.Field<float>(Constants.ClosingFXRate),


because if I disable that line,
this code line gets the right data and all is good
  Currency = rates.Field<string>(Constants.Currency)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.