Dapper with dynamic variables won't work without mapping. This breaks SQL. How to use Dapper with bigger SQL query?

Question

Let say that I had a stored proc in MS SQL. Now I have moved to MySQL and I want to keep all SQL queries inside of C# asp.net core app.

The issue starts with bigger queries where can be SQL dynamic internal variables.

I.e. Select @TypeId := TypeId, @StartDate := StartDate From Test_test Where Id = @Id

In this example @Id supposed to be mapped by Dapper (this variable is passed from a DAL to MySQL database), however @TypeId and @StartId shouldn't be mapped.

1. There is many more dynamic variables created only for SQL query

2. Mapping null will break query i.e. here is results from SQL

   NULL := StartDate instead of @StartDate := StartDate

How can I use dapper with SQL queries where query is creating dynamically variables that shouldn't be mapped by Dapper ?

Answer 1

There should be no problem at all with using SQL variables in a query with Dapper as long as you include AllowUserVariables=true in your connection string. This allows the client library to "ignore" @TypeId and @StartDate in your SQL and not require them to be specified in the MySqlCommand.Parameters collection.

As long as you don't include properties with those names in the anonymous object you pass to Dapper (i.e., don't do new { StartDate = DateTime.Now, TypeId = 1 }), Dapper won't create any parameters with those names, won't map any values to them, and will pass the SQL through to MySQL Server to be evaluated as server-side variables.