Why does PetaPoco return from a db.Insert a ID field of 7 as a decimal

When doing a Insert the variable id is returned as an object. However in my database it is an int and in my POCO it is an int h开发者_Go百科owever when the method call to ExecuteScalar to return @@IDENTITY is called it returns the number 7 as an object but the debugger thinks its a decimal.

Therefore when I do int newID = (int)db.Insert(...) it throws a

InvalidCastException

Is this a framework bug or a PetaPoco bug?

Also remember that if you have set your class up and you insert like so.

[TableName("Users")]
[PrimaryKey("Id")]
public class User {
    public int Id {get;set;}
    public string Name {get;set;}
}

var user = new User() { Name = "My Name" };
db.Insert(user);
Assert.True(user.Id != 0);

user.Id will now change from 0 to the newly created identity value

It's going to depend on what RDBMS you are connecting to, which you didn't state.

Here's the relevant code from PetaPoco that retrieves the last id and returns it:

object id;
switch (_dbType)
{
    case DBType.SqlServerCE:
        DoPreExecute(cmd);
        cmd.ExecuteNonQuery();
        id = ExecuteScalar<object>("SELECT @@@IDENTITY AS NewID;");
        break;
    case DBType.SqlServer:
        cmd.CommandText += ";\nSELECT SCOPE_IDENTITY() AS NewID;";
        DoPreExecute(cmd);
        id = cmd.ExecuteScalar();
        break;
    case DBType.PostgreSQL:
        if (primaryKeyName != null)
        {
            cmd.CommandText += string.Format("returning {0} as NewID", EscapeSqlIdentifier(primaryKeyName));
            DoPreExecute(cmd);
            id = cmd.ExecuteScalar();
        }
        else
        {
            id = -1;
            DoPreExecute(cmd);
            cmd.ExecuteNonQuery();
        }
        break;
    case DBType.Oracle:
        if (primaryKeyName != null)
        {
            cmd.CommandText += string.Format(" returning {0} into :newid", EscapeSqlIdentifier(primaryKeyName));
            var param = cmd.CreateParameter();
            param.ParameterName = ":newid";
            param.Value = DBNull.Value;
            param.Direction = ParameterDirection.ReturnValue;
            param.DbType = DbType.Int64;
            cmd.Parameters.Add(param);
            DoPreExecute(cmd);
            cmd.ExecuteNonQuery();
            id = param.Value;
        }
        else
        {
            id = -1;
            DoPreExecute(cmd);
            cmd.ExecuteNonQuery();
        }
        break;
    default:
        cmd.CommandText += ";\nSELECT @@IDENTITY AS NewID;";
        DoPreExecute(cmd);
        id = cmd.ExecuteScalar();
        break;
}

Another two cents. As already answered PetaPoco returns a decimal for SQL Server Inserts. The question author is attemping to cast to an int which throws an InvalidCastException. However, a Type Conversion does work. So instead of this:

int newID = (int)db.Insert(...)

do this:

int newID = Convert.ToInt32(db.Insert(...))

It's a guess but worth a go. In PetePoco.cs try changing :

id = ExecuteScalar<object>("SELECT @@@IDENTITY AS NewID;");

to

id = ExecuteScalar<int>("SELECT @@@IDENTITY AS NewID;");