System.InvalidCastException: Specified cast is not valid. Error

Question

I am on a C# ASP.NET project.

I have a MySQL table with a userid field of type int.

Now I want to get the number of rows where value of userid equals certain value using LINQ.

To achieve this, I wrote the following method:

public int getCount(int usercode) {
    int count = 0;
    DataTable mytable = getAllRowsAndReturnAsDataTable(); // assigning a DataTable value to mytable. 
    if (mytable.Rows.Count > 0) { 
        count = (from x in mytable.AsEnumerable() where x.Field<Int32>("userid") == usercode select x).Count();
    } 
    return count;
}

but it is showing error System.InvalidCastException: Specified cast is not valid. showing count = (from x in mytable.AsEnumerable() where x.Field<Int32>("userid") == usercode select x).Count(); in red highlight area.

I don't know what I did wrong here. Please help.

Answer 1

The most likely cause of the InvalidCastException is the x.Field<Int32>("userid") line. The Field<T> extension method will throw an InvalidCastException in the case where the actual type of the data doesn't match the type which was passed to Field<T>. Hence if userid wasn't an Int32 this would throw.

EDIT

Based on your comments the type of userid is actually UInt32 and not Int32. This is what is causing the problem. Try using the following and it should work

x.Field<UInt32>("userid")

Answer 2

Without looking at the data coming back from your database I can only guess that the following part of your LINQ is at fault:

x.Field<Int32>("userid")

Your userid column value probably isn't an int, I would put my money on it being NULL?

UPDATE: Can you confirm it's not the Field call that is breaking? Simply change your code to something like this without the Field call:

public int getCount(int usercode){
  int count = 0;
  DataTable mytable = getAllRowsAndReturnAsDataTable(); // assigning a DataTable value to mytable. 
  if (mytable.Rows.Count > 0) { 
    count = mytable.AsEnumerable().Count();  // No WHERE function call so no casting.
  } 
  return count;
}

You could also inspect what the values are that are returned by mytable.AsEnumerable() in a watch window for example to ensure that everything looks correct. If the code above works then it's the Field call blowing up. Find out which row cannot be cast to an Int32 and go from there.

If it is in fact NULL, there are a number of ways you can solve this.

1. Make sure you don't return NULL from your database query, in MySQL you can use IFNULL.

2. Use a nullable type for the generic being passed into Field:

   where x.Field("userid") == (Int32?)usercode