A Comprehensive Guide to Performing Inserts and Returning Identity Values with Dapper

Problem Context and Common Errors

When using Dapper for database operations, many developers encounter scenarios requiring insert operations with immediate retrieval of generated identity values (such as IDENTITY columns in SQL Server). A typical erroneous implementation is shown below:

string sql = "DECLARE @ID int; " +
             "INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); " +
             "SELECT @ID = SCOPE_IDENTITY()";

var id = connection.Query<int>(sql, new { Stuff = mystuff}).First();

This implementation leads to a System.InvalidCastException: Specified cast is not valid exception, with the stack trace pointing to Dapper's SqlMapper.QueryInternal method. The root cause lies in the mismatch between Dapper's default query processing mechanism and the return type of variable assignment statements.

Solution 1: SCOPE_IDENTITY() with Explicit Type Casting

The most straightforward and widely compatible solution involves using the SCOPE_IDENTITY() function with explicit type casting:

var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff);
SELECT CAST(SCOPE_IDENTITY() as int)", new { Stuff = mystuff});

The key aspects of this approach are:

1. Using QuerySingle<int> instead of Query<int>.First() - the former is specifically designed for returning single values, offering clearer semantics and better performance
2. Ensuring explicit data type specification through CAST(SCOPE_IDENTITY() as int), preventing Dapper's type inference errors
3. Combining INSERT and SELECT statements in a single query to guarantee atomic operation

The SCOPE_IDENTITY() function returns the last identity value generated in the current session and scope, representing the standard method for retrieving auto-increment identifiers across all SQL Server versions.

Solution 2: OUTPUT Clause (SQL Server 2005+)

For SQL Server 2005 and later versions, the more modern OUTPUT clause can be utilized:

var id = connection.QuerySingle<int>( @"
INSERT INTO [MyTable] ([Stuff])
OUTPUT INSERTED.Id
VALUES (@Stuff);", new { Stuff = mystuff});

Advantages of this method include:

• More concise and intuitive syntax, directly outputting specified columns of inserted rows
• Ability to return multiple column values simultaneously
• More stable performance in complex insertion scenarios (such as tables with triggers)
• Closer alignment with SQL standards, facilitating cross-database portability

Technical Principles Deep Dive

Understanding these solutions requires mastery of several key technical concepts:

Dapper's Parameter Handling Mechanism: Dapper processes query results through dynamic type inference. When queries return untyped values (like raw SCOPE_IDENTITY()), type inference may fail. Explicit use of CAST or the OUTPUT clause provides clear type information, enabling Dapper to correctly map to the int type.

Scope and Session Identity: SCOPE_IDENTITY(), @@IDENTITY, and IDENT_CURRENT() are three related functions with different scopes: SCOPE_IDENTITY() is limited to the current scope (stored procedure, trigger, or batch) and represents the safest choice; @@IDENTITY returns the last identity value generated in any scope within the current session; IDENT_CURRENT() returns the last identity value generated for a specified table in any session.

Transaction Consistency: Both solutions complete insertion and identity retrieval within a single database command, ensuring atomic operation. When used within transactions, they adhere to transaction isolation levels, guaranteeing that committed identity values are read.

Performance and Compatibility Considerations

From a performance perspective, both solutions show minimal differences, though the OUTPUT clause demonstrates higher efficiency when returning multiple column values. Regarding compatibility:

For applications requiring cross-database compatibility, the SCOPE_IDENTITY() approach is recommended, with adaptation to database-specific identity retrieval functions.

Advanced Application Scenarios

In complex applications, scenarios may require batch insertion with retrieval of all generated identities:

var items = new[] { new { Stuff = "item1" }, new { Stuff = "item2" } };
var ids = connection.Query<int>( @"
INSERT INTO [MyTable] ([Stuff])
OUTPUT INSERTED.Id
VALUES (@Stuff);", items).ToList();

Dapper can automatically expand object array parameters into multiple row insertions, returning all generated identity values through the OUTPUT clause.

For scenarios requiring input/output parameters, Dapper's DynamicParameters class can be used, though the two solutions presented above offer greater simplicity and efficiency for basic identity retrieval.

Best Practices Summary

Based on the analysis above, the following best practices are recommended:

1. Prefer QuerySingle<T> over Query<T>.First() for retrieving single values
2. Explicitly specify return value types in SQL statements (via CAST or explicit column names)
3. For SQL Server 2005+ environments, consider using the OUTPUT clause for better readability and extensibility
4. When cross-database compatibility is required, use the SCOPE_IDENTITY() approach with adaptation to database-specific functions
5. Leverage Dapper's parameter expansion capabilities and the OUTPUT clause for efficient batch insertion handling

By following these practices, developers can avoid common type casting exceptions and write robust, efficient code for database insertion and identity retrieval operations.