Disable dapper.rainbow from using ID while inserting

Question

I am using dapper.rainbow for inserting a record into MSSQL db. Following is my code

int? id  = db.RoomTypes.Insert(roomType)

When i run my app, i am getting the below exception.

Cannot insert explicit value for identity column in table 'RoomTypes' 
when IDENTITY_INSERT is set to OFF.

I think that dapper.rainbow is using the value (default value for int) for my Identity Column during the insert. This is what causing this exception. My identity column for the table RoomTypes is auto incremented and it is also the primary key of my table.

Now, how do i stop the dapper.rainbow from using the ID column during insert.

Answer 1

Looking at the Dapper.Rainbow.Insert method, the method is "dumb" to the type passed in as the data parameter:

public virtual int? Insert(dynamic data)
{
    var o = (object)data;
    List<string> paramNames = GetParamNames(o);

    string cols = string.Join(",", paramNames);
    string cols_params = string.Join(",", paramNames.Select(p => "@" + p));
    var sql = "set nocount on insert " + TableName + " (" + cols + ") values (" + cols_params + ") select cast(scope_identity() as int)";

    return database.Query<int?>(sql, o).Single();
}

In other words, if you include the ID as a property in the parameter, then Dapper will try to insert that value into the database. And as you've seen, if you have IDENTITY_INSERT set to Off, then the insert will fail.

This means you'll need to create a new type that doesn't include your ID property to pass into the method. A few of ideas come to mind:

1. Create a base class without the ID for RoomTypes and then just pass in the base class to the Insert method.
2. Create a dynamic parameter and pass that to the Insert method with only the properties you want to insert into the database.
3. Override the Insert method and remove the ID parameter before sending to the Dapper