Unable to cast object of type 'System.Int32' to type 'System.String' in LINQ query

Question

Hi I'm trying to remove a user from my database. I'm not sure why I'm receiving this error though since none of my variables (employeeName, departmentName) are of type int.

The debugger keeps stopping at this block of code with the "Unable to cast object of type 'System.Int32' to type 'System.String'." error.

//Note: employeeName type is navchar
        var empQuery =
            from dep in db.Department
            join emp in db.EmployeeDetails
            on dep.departmentId equals emp.departmentId
            where dep.departmentName == reqDep
            && emp.employeeName == reqUser
            select emp;

        //Debugger keeps pointing to the foreach with the error
        foreach (var element in empQuery)
        {
            Console.WriteLine("user: " + element + " has been deleted");
            db.EmployeeDetails.DeleteOnSubmit(element);
        }
        // Freeze the console window.
        Console.ReadLine();

If it helps, I've included the entire code below.

namespace runLinqSql
{
// Table<T> abstracts database details per table/data type. 
[Database]
public class FireEvacuation : DataContext
{
    public Table<Employee> EmployeeDetails;
    public Table<EmpDepartment> Department;

    public FireEvacuation(string connection) : base(connection) { }
}
  class Program
{
    static void Main(string[] args)
    {
        // Use a connection string.
        FireEvacuation db = new FireEvacuation
            (@"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\runSqlLinq\FireEvacuation.mdf");


        ////specify department to be removed
        string reqDep = "KOM";
        // Attach the log to show generated SQL.
        db.Log = Console.Out;

        ////specify user to be removed
        string reqUser = "Jason";


        //Note: employeeName type is navchar
        var empQuery =
            from dep in db.Department
            join emp in db.EmployeeDetails
            on dep.departmentId equals emp.departmentId
            where dep.departmentName == reqDep
            && emp.employeeName == reqUser
            select emp;

        //Debugger keeps pointing to the foreach with the error
        foreach (var element in empQuery)
        {
            Console.WriteLine("user: " + element + " has been deleted");
            db.EmployeeDetails.DeleteOnSubmit(element);
        }
        // Freeze the console window.
        Console.ReadLine();

        try
        {
            db.SubmitChanges();
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            // Provide for exceptions.
        }

What is data types of `dep.departmentId` and `emp.departmentId`? — Jenish Rabadiya, Mar 24 '15 at 06:06
Which line of code is the error? And what are the definitions of `EmpDepartment` and `Employee`? — Enigmativity, Mar 24 '15 at 06:07
Maybe one of your keys are string. Check departmentId in tables department and employes. — puko, Mar 24 '15 at 06:07
Hi Jenish they are both int of length 4. Do you think they are the cause? Thanks for helping. @Jenish — coder123, Mar 24 '15 at 06:20
Hi Enigmativity and Kyle, what do you guys mean by definition? Sorry, I'm still a little new to coding. Thanks for helping. — coder123, Mar 24 '15 at 06:22
@coder123 if they both are of value int then it can not be cause just make sure the database is also having column of type int. — Jenish Rabadiya, Mar 24 '15 at 06:37
Yup, I've checked my database too. Thanks for answering my post @Jenish — coder123, Mar 24 '15 at 08:20

score 2 · Answer 1 · answered Mar 24 '15 at 07:01

2

One method to find problems like this: (I call this hard-core-debugging)

Make your code smaller

Remove one element after the other form the piece of code which causes the problem.
Run the code after each modification.

This way you find out, which element causes the problem.

In your case, you could replace the query with a very simple one, then add the join and so forth.

answered Mar 24 '15 at 07:01

DrKoch

9,556
2
34
43

Hi DrKoch, yup I did that too. Thanks for answering my post – coder123 Mar 24 '15 at 08:19

score 1 · Accepted Answer · answered Mar 24 '15 at 06:58

Seems to me that the error has something to do with one of the following lines:

on dep.departmentId equals emp.departmentId
where dep.departmentName == reqDep
&& emp.employeeName == reqUser

(The reason the exception is being thrown at the loop, is that the expression will not have been run before that, which is the first time it is needed).

You are comparing values in pairs here. Now if one of the values in a pair is of type string, and the other is of type int, that will cause that error.

To fix this, double check that each pair you are comparing are really valid (i.e. that dep.departmentId and emp.departmentId are really what you want to compare), and that the types are the same.

If the types are not the same, but they can still be expected re return equivalent values (example: dep.departmentId could be an int value of 123, while emp.departmentId is the string "123"), you can probably compare them both as strings:

on dep.departmentId.ToString() equals emp.departmentId.ToString()

score 1 · Answer 3 · answered Dec 04 '15 at 10:31

I found that the problem here wasn't with my actual LINQ query in my code but instead a problem with the table that I was querying in my dbml file. The problem was the primary key of the table i.e. it had none! Once I set the primary key of the table and then added it again to my dbml file, my query worked.

Unable to cast object of type 'System.Int32' to type 'System.String' in LINQ query

3 Answers3