In my last blog post about migrating ASP.NET Membership users to ServiceStack, I mentioned that it was possible to return user roles in each result row rather than requiring a separate query to return roles. This time I’ll go into detail on how to do that both for collections of scalars and collections of complex types.
First of all, you need to be able to escape strings that contain JSV characters. The description of the format and escaping rules are here. Here is a sample implementation of JSV escaping in SQL that uses a numbers table.
So for the first example, I’ll demonstrate how to write a query that will return role names for a user, formatted in such a way that OrmLite will automatically deserialize the column into a collection type.
Here’s some saemple output:
A few words about the technique being used here to generate the Roles column: I am using what is generally referred to as the XML PATH method for generating comma-delimited lists in SQL. It is a fairly well-known pattern in the world of T-SQL. Without getting into the specifics, the inner query with the FOR XML PATH clause will result in a string composed of each row’s value preceded by a comma. The outer STUFF function takes this string and removes the leading comma. Note how the inner query’s WHERE clause references a value from the outer query, making it a correlated subquery that executes for every row in the outer query. As such, this query would not be recommended for large amounts of data, but it will perform perfectly fine for small amounts.
Now, let’s update the POCO used in the last blog to add a collection type named Roles. At minimum, you can use an array of string or a list of string, which should cover the majority of use cases. Here we will demonstrate use of a generic typed list.
And that’s all we need to do! When you execute the query with OrmLite, the string is automatically deserialized into the Roles property:
Pretty cool, huh?
Now, what if you want to deserialize a collection of complex types? Let’s extend this example by returning two columns from aspnet_Roles. First, alter the query so that the FOR XML PATH query returns two columns in the JSV format: surrounded by curly braces, properties and values in the format “property:value”.
Next, create a POCO class for this new format and use it in the declaration of the Roles property.
OrmLite uses String.Format internally, so take care to escape those curly braces in the SQL string! The image below shows both the modified query and the expanded Roles collection.
So, there it is; now you have the SQL pattern to return lists of both scalars and complex objects in the JSV format the ServiceStack.OrmLite automatically parses and maps to POCOs. A few words of warning about these techniques:
As mentioned before, there will be a performance penalty for the correlated subqueries and the additional processing required to create this format. I would not recommend using it for large data sets, but for cases where you have domain knowledge that few rows are expected, this can be a handy technique.
I strongly suggest to use these techniques for reading only. Data stored in this format is not easily accessed by SQL, and queries that attempt to search it will perform poorly.
Latest posts by Falafel Posts (see all)
- Matching Complex Query String Rewrite Rule in IIS - March 22, 2017
- Using Google Services in UWP C# Apps – Part 2 - February 7, 2017
- Using Google Services in UWP C# Apps – Part 1 - February 6, 2017
- Redis Caching in the Google Cloud Platform - February 3, 2017
- Entity Framework with Google Cloud SQL - February 2, 2017