search

tmsmith / Dapper-Extensions

Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs. For more advanced querying scenarios, Dapper Extensions provides a predicate system. The goal of this library is to keep your POCOs pure by not requiring any attributes or base class inheritance.
1.79k stars 586 forks source link

inserting into an keyType.Assigned column fails with "identity_insert is set to off" #214

Open AartBluestoke opened 5 years ago

AartBluestoke commented 5 years ago

I have a table with an identity column (normally handled by keyType=Identity). At the moment I am replicating data I already know the key for, and it can't change. If dapper tries to write an entity with a customMapper setting the identity column to keyType to Assigned. Inserts using the following mapper fail with a message like "Cannot insert explicit value for identity column in table 'Customer' when IDENTITY_INSERT is set to OFF"

foreach (var idProp in activeType.GetProperties()) { if (<some conditions for skipping>) continue; if (idProp. <is my key column i need to write>) { Map(idProp).Key(KeyType.Assigned); } else { Map(idProp); } }

in the case that you are on sql server, the code at https://github.com/tmsmith/Dapper-Extensions/blob/dd82ecc6f710bca6d5e5645cbff8da97e411257f/DapperExtensions/Sql/SqlGenerator.cs#L151 needs to access a new dialect method for sql server to include the prefix fragment: SET IDENTITY_INSERT . ON; , and probably also a post-fix fragment to set it back off again (sql server can only have 1 table set to IDENTITY_INSERT ON at a time).

AartBluestoke commented 5 years ago

alternatively, the classMapper could expose some protected strings that can be set to hold custom prefix and post-fix query fragments, so that this and many other quirky corner cases can be handled without the need for custom Dialect code for each case.

AartBluestoke commented 5 years ago

current workaround:

        public static void InsertThing(DbConnection conn,Thing thing, int? commandTimeout = nul)
        {
              var trans = conn.BeginTransaction();
                    try{
                        conn.Execute("SET IDENTITY_INSERT [schema].[thing]  ON  ",transaction:trans);
                        DapperExtensions.DapperExtensions.Insert<Thing>(conn,thing,trans,commandTimeout);
                        conn.Execute("SET IDENTITY_INSERT[schema].[thing]  OFF  ",transaction:trans);
                        trans.Commit();
                    } catch {trans.Rollback(); throw;}
        }

Not sure if that wrapping transaction is needed in the presence of parallel writes; only 1 table per session can have IDENTITY_INSERT set on...