Query dynamic parameters. NotSupportedException

Question

I'm having small issue with using Dapper query parameters.

I have:

WebApi to receive request with all required data (entity name, columns, filters, etc). Filter in the deepest level has this structure (this model build from api model by ctor):

    public class QueryFilterItem
    {
        public string Name { get; }
        public string FieldName { get; }
        public object Value { get; }
        public ComparisonType ComparisonType { get; }
    }

Query executor, which handles api call

var arguments = new ExpandoObject();
// ...
arguments.TryAdd($"{filter.Name}Entity{filter.FieldName}", filter.Value);
// ... This part above is a part of another helper class, which is dynamically building filter query data

IEnumerable<dynamic> result;

using (var connection = DbConnectionFactory.CreateDbConnection())
{
    result = connection.Query(sqlQuery, arguments); // tried with wrapping into DynamicParameters
}

And from that code, I get this exception:

System.NotSupportedException: The member MyFilterItemEntityId 
of type System.Text.Json.JsonElement cannot be used as a parameter value
   at Dapper.SqlMapper.LookupDbType(Type type, String name, Boolean demand, ITypeHandler& handler) in /_/Dapper/SqlMapper.cs:line 418
   at Dapper.DynamicParameters.AddParameters(IDbCommand command, Identity identity) in /_/Dapper/DynamicParameters.cs:line 232
   at Dapper.DynamicParameters.Dapper.SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, Identity identity) in /_/Dapper/DynamicParameters.cs:line 151
   at Dapper.SqlMapper.<>c__DisplayClass165_0.<GetCacheInfo>b__0(IDbCommand cmd, Object obj) in /_/Dapper/SqlMapper.cs:line 1714
   at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action`2 paramReader) in /_/Dapper/CommandDefinition.cs:line 129
   at Dapper.SqlMapper.<QueryImpl>d__140`1.MoveNext() in /_/Dapper/SqlMapper.cs:line 1080
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 725
   at Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 652
   at DappertStorageTest.Cqrs.SelectQueryHandler.GetByFilters(SelectQuery query)
   at DappertStorageTest.Cqrs.SelectQueryHandler.Handle(SelectQuery query)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at MAS.DappertStorageTest.Cqrs.Infrastructure.QueryProcessor.Execute[TResult](IQuery`1 query)
   at MAS.DapperStorageTest.Controllers.DataController.Select(SelectRequest selectRequest)
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<<InvokeActionMethodAsync>g__Logged|12_1>d.MoveNext()

Arguments values (not part of exception):

sqlQuery: USE [DapperStorageTest];SELECT * FROM [Passenger] WHERE ([Id] = @MyFilterItemEntityId)

Arguments: {[MyFilterItemEntityId, ValueKind = String : "7d570abd-576d-425f-9d24-d84314d299ba"]} (raw)

Can someone help me with that?

Are you trying to create some sort of general "takes a string and an object and runs a dapper query and returns the result" method? Explain your use case more? — Caius Jard, Dec 28 '20 at 08:41
@CaiusJard yes. This is Select query, which will be called outside (from another service \ or frontend client). Case is simple - to provide uni-access to database. Client must able to configure it with dynamic filters \ filter groups. A little bit complex than EF Repository.. — bodynar, Dec 28 '20 at 08:51
@xanatos `filter.value` is object. Wait a sec I will provide more information about that in post — bodynar, Dec 28 '20 at 08:53
@bodynar yes and no... `object x = "Hello"` x is a `string` :-) ... Can you print us `filter.Value.GetType()`? — xanatos, Dec 28 '20 at 08:54
@xanatos ok. i got it. it appears that exception caused by filter builder. cuz `{Name = "JsonElement" FullName = "System.Text.Json.JsonElement"}` — bodynar, Dec 28 '20 at 09:06
@bodynar There are various `ToObject` implemented [here](https://stackoverflow.com/a/58193164/613130). You could try to solve do a `TryAdd(...., filter.Value.ToObject())`, or you could try to find what happens when `Value` is setted. — xanatos, Dec 28 '20 at 09:14
@xanatos Thank you for pointing on issue's heart. `JsonElement` type comes from web api, because model property have `object` type as you can see in post. Temporally switched to `string`. Will consider about returning to `object` and handling consequences of web api model mapping — bodynar, Dec 28 '20 at 09:18

score 1 · Accepted Answer · answered Dec 28 '20 at 09:23

Thanks to xanatos Exception caused by web api model mapping. According to api model filter value has object type and asp.net core mapped string into JsonElement. Switched from object to string type.

public class QueryFilterItem
    {
        public string Name { get; }
        public string FieldName { get; }
        public object Value { get; } // Here
        public ComparisonType ComparisonType { get; }
    }

Query dynamic parameters. NotSupportedException

1 Answers1