Pretty simple glitch firing when trying to get a records from SQLite

System.InvalidCastException = "Specified cast is not valid."

That's the error I'm getting.

My code is simple enough:

public Staff FindStaff(int id)
{
    try
    {
        return db.Staffs.SingleOrDefault(c => c.ID == id);
    }
    catch (Exception e)
    {
        Debug.WriteLine(e.InnerException);
        throw;
    }            
}

I have used a breakpoint to check what value the 'id' parameter has on execution, and it has a numerical value of an existing record. For example, 4.

The error fires on the return statement.

What's weird is I have the exact same type of code for another entity on my project and it works fine. What could be causing this error?

Here's the TSQL I used to create the tables. Staff is the one that is causing the error, Student works 100% fine.

create table Student
(
    ID integer primary key autoincrement,
    ...other fields...
);

create table Staff
(
    ID integer primary key autoincrement,   
    IDStaffType integer references StaffType(ID),
    ...other fields...
);

As you can see, both ID fields are integers, why would开发者_运维百科 a conversion exception fire?

In SQLite, "integer" is a 64-bit int, which is a long in C#.

I suspect that's not the problem, though. SQLite does not enforce data types (except for integer primary key columns), so it's entirely possible that you have a record that has, say, a string stored in an integer column. This would explain the strange behavior where it sometimes works but sometimes does not (only the problem record would cause errors).

After running into this scenario myself, I've changed my edmx file to always treat SQLite data as strings, e.g., a DefiningQuery of

SELECT RowId,
CAST ([data1] AS TEXT) AS [data1],
CAST ([data2] AS TEXT) AS [data2],
CAST ([data3] AS TEXT) AS [data3]
FROM table1

with matching Insert/Update/Delete "stored procedures" defined in StorageModels.