Dapper casting issue in stored procedure with conditional select queries

user_5718148 0
Viewed 349

I am faced with casting issue when calling a stored procedure that has two select queries where only one of the query will be executed based on an if else condition. BOTH the select queries have the same values being selected, but in different order. Just to give an idea,

IF (some condition)
 SELECT
  t1.Id,
  t1.Title,
  t1.Description
 FROM Table1 t1
ELSE
 SELECT
  t1.Title,
  t1.Id,
  t1.Description
 FROM Table1 t1

Scenario:

  1. Method that executes this stored procedure is called, condition is satisfied.
  2. Expected data is returned.
  3. Method that executes this stored procedure is called, condition is not satisfied.
  4. Casting error thrown in code.

Error parsing column 0 (Id=123 - Int64)
System.InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.String'.
at Deserializec2c12182-6e8d-4a6e-8f54-e60138f070ee(IDataReader )

Changing them to the same selection order will bypass the issue, but can anybody tell if this is some caching issue with Dapper, or anything else?

Any help is appreciated. Thanks.

dapper sql-server asp.net c#
2 Answers

Looking at the source code for Dapper, it appears that the generated mapping code uses the index to read the data from DbDataReader. I would imagine that accessing by index is faster than by name, which is why it is done this way.

This mapper is cached (for performance) and used for identical runs of the same command text, so Dapper expects the columns to be in the same order.

I personally feel that this is a bug, and that the cache logic should take into account the column ordering when checking for an existing mapper. Feel free to file a bug report.

As mentioned by @RoarS. in the comments, a workaround is to switch off caching, by using an explicit CommandDefinition

var cmdDef = new CommandDefinition("select ... blah ...", new { a, b }, commandType: CommandType.Text, flags: CommandFlags.NoCache);

This shouldn't be necessary, Dapper should check whether the reader is the same before trying to reuse it.

user_148689970

Seems like the ordering of the columns in the first and second statement is incorrect. So when dapper queries the stored proc by index of the column there is a mismatch in the type of the columns in both statements. In the first statement the datatype of the first column is INT 64 whereas in the second statement it is String. Hence the issue. So the correct SQL Procedure would look like this:

IF (some condition)
 SELECT
  t1.Id,
  t1.Title,
  t1.Description
 FROM Table1 t1
ELSE
 SELECT
  t1.Id,
  t1.Title,
  t1.Description
 FROM Table1 t1
user_163255010
Related