How to return JSV formatted collection types from SQL Server to ServiceStack.OrmLite

By October 28, 2013Uncategorized

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.

create function dbo.jsv_escape(
    @value nvarchar(max)
returns nvarchar(max)
    if not exists (
        select n
        from dbo.sequence
        where n between 1 and len(@value)
        and substring(@value, n, 1) in ('[', ']', '{', '}', ',', '"')
        return @value
    return '"' + replace(@value, '"', '""') + '"'


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.

select m.UserId, m.Email, m.Password, m.PasswordSalt,
    '[' + stuff((
        select ',' + dbo.jsv_escape(r.RoleName)
        from aspnet_Roles r
        join aspnet_UsersInRoles ur on ur.RoleId = r.RoleId
        where ur.UserId = u.UserId
        for xml path ('')
    ), 1, 1, '') + ']' as Roles
from aspnet_Membership m
join aspnet_Users u on u.UserId = m.UserId
join aspnet_Applications a on a.ApplicationId = m.ApplicationId
where u.UserName = @UserName
and a.ApplicationName = @ApplicationName

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.

private class AspnetMember {
    public Guid UserId { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string PasswordSalt { get; set; }
    public List<string> Roles { get; set; }

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”.

select ',{RoleId:' + convert(varchar(36), r.RoleId) + ',RoleName:' + dbo.jsv_escape(r.RoleName) + '}'


Sample output:

Next, create a POCO class for this new format and use it in the declaration of the Roles property.

private class AspnetRole {
    public Guid RoleId { get; set; }
    public string RoleName { get; set; }
private class AspnetMember {
    public Guid UserId { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public string PasswordSalt { get; set; }
    public List<AspnetRole> Roles { get; set; }

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:

  1. 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.

  2. 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.

The following two tabs change content below.