Hi, I’m encountering an issue with deserializing string of “[]” when using the Dapper functionality in SS.
Steps to replicate: from any SS service, run the following line of (contrived but demonstrative) code:
var myResults = Db.Query<UserAuth>("select * from UserAuth").ToList();
Background:
I’m using the embedded Dapper.Query command in OrmLite to execute custom generic query logic using TableValuedParameters in MSSQL in a generic method, in which I have relatively simple queries, that require large collections of values for the in condition. Broadly, this allows me to “select x,y,z from GenericTable where Pk in (select Id from LargeTabValParamWhichIsAnArrayWithOver2000Ids)”. Generally speaking this works great, however, fails when the target table is the inbuild SS UserAuth table, because of the way the “Roles” column is declared as a string in the db but in C# its a collection.
This shouldn’t have anything to do with OrmLite or ServiceStack.
Dapper in OrmLite are just using Dapper’s implementation with the namespaces changed to ServiceStack.OrmLite.Dapper to avoid collisions with other Dapper references. It sounds like Dapper doesn’t support the data that your query is returning. Why not use OrmLite to read from UserAuth instead?
Okay, I suspected that the issue might be deeper in Dapper but thought I’d confirm that there were no customisations as part of including it in OrmLite…
As for the “why”: The system design is such that we occasionally have a particularly “complex” initial query, for example, to traverse a (very) deep tree of parent-child relationships and look up all records nested under a particular node, and then once we have that initial ‘heavy logic’ result list, I can then load up all associated records using a custom extension method I wrote using TVP… idea being to have as much as possible in simple Ormlite queries and type-safe extension methods, rather than hand-crafting sql or repeating that custom heavy logic in the WHERE of the relations.
In this case, query potentially several thousand user accounts where userid in that initial result, as well as collections of other related records, before finally assembling the output using the .Merge() functionality.