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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.