Support for dynamic SQL with variable parameters in AOT mode

[stalek71]

I have a very simple case, but I’m not sure how to implement it. I’ve prepared the project for AOT, and interceptors with hardcoded SQL (e.g., QueryAsync<T>("some sql")) work correctly and generate the expected code.

However, I also have dynamically generated SQL, where I use a similar operation. The number of parameters in the WHERE clause changes along with the generated query. The output/result type (<T>) remains the same, but the query text and the number of bound parameters vary with each call.

Is it possible to use Dapper.AOT in this scenario, or do I have to abandon Dapper entirely in this case - because it cannot be used with AOT when the number of query parameters changes dynamically? Should I fall back to using ADO.NET in such situations (as AOT is the goal)?

The sample is below:

var dynParams = query.GetDynamicParameters();

var command = new CommandDefinition(
    commandText: query.Query,
    parameters: dynParams,
    commandType: CommandType.Text
);

var metadatas = await connection.QueryAsync<FileInfoDao>(command);

When I run the code I receive an error like below (.Net 9):

  System.PlatformNotSupportedException
  HResult=0x80131539
  Message=Dynamic code generation is not supported on this platform.
  Source=System.Private.CoreLib

[DL444]

I don't think dynamic parameterization is inherently incompatible with Native AOT, just that someone needs to get it done.

For example, I imagine that we can take the generated command factory code quoted by the docs:

public override void AddParameters(in global::Dapper.UnifiedCommand cmd, object? args)
{
    var typed = Cast(args, static () => new { productId = default(int) }); // expected shape
    var ps = cmd.Parameters;
    global::System.Data.Common.DbParameter p;
    p = cmd.CreateParameter();
    p.ParameterName = "productId";
    p.DbType = global::System.Data.DbType.Int32;
    p.Direction = global::System.Data.ParameterDirection.Input;
    p.Value = AsValue(typed.productId);
    ps.Add(p);
}

and adapt it into this so dictionary-typed parameters could be supported:

public override void AddParameters(in global::Dapper.UnifiedCommand cmd, object? args)
{
    var typed = (IDictionary<string, object>)args;
    var ps = cmd.Parameters;
    global::System.Data.Common.DbParameter p;
    p = cmd.CreateParameter();
    p.ParameterName = "productId";
    p.DbType = global::System.Data.DbType.Int32;
    p.Direction = global::System.Data.ParameterDirection.Input;
    p.Value = typed["productId"];
    ps.Add(p);
}

Support for DynamicParameter can be added in a similar way. It won't and can't cover every parameter type possible, but should at least offer some ways to unblock users. I might see if it can actually be done later when I have time.

---

EDIT: I just noticed that the issue asked for not just dynamic parameters but dynamic SQL as well. This would be difficult since the query cannot be statically analyzed. If there are only a handful of combinations of queries possible, maybe splitting them each into a static SQL command would be the solution. Otherwise I don't really have a good idea.