Dapper Query() method fails with complex types

ServiceStack.OrmLite
1

Hello,

I am receiving an InvalidCastException when attempting to use the ServiceStack.OrmLite.Dapper.Query method when accessing a table that stores a complex type (for example a Dictionary stored by OrmLite in the DB table as a varchar). Works fine on simple types.

Is the Query API intended to support complex types? If so, I think there may be an issue. (See sample test below.)

I am using the Query method to force execution of SQL parameterized queries to protect against SQL injection. I’ve found an alternative approach using the Where method, but I wanted to raise the potential issue.

Thanks!

namespace ServiceStack.OrmLite.Tests
{
    using System.Collections.Generic;
    
    using NUnit.Framework;

    using ServiceStack.DataAnnotations;
    using ServiceStack.OrmLite.Dapper;

    [TestFixture]
    public class QueryTests
    {
        [Test]
        public void CanQueryComplexType()
        {
            var factory = new OrmLiteConnectionFactory(Config.SqliteMemoryDb, SqliteDialect.Provider);
            using (var db = factory.OpenDbConnection())
            {
                db.DropAndCreateTable<Recipe>();
                var id =
                    db.Insert(
                        new Recipe
                            {
                                Name = "Fruit Salad",
                                Ingredients =
                                    new Dictionary<string, string>
                                        {
                                            { "Item1", "Apple" },
                                            { "Item2", "Orange" },
                                            { "Item3", "Banana" }
                                        }
                            },
                        true);

                // Confirm standard OrmLite read API method returns complex types.
                var selectResults = db.Select<Recipe>();

                // Confirm OrmLite Dapper API works without complex types.
                // Note that the select statement excludes the "Ingredients" complex type column, and it succeeds.
                var simpleObjectQueryResults = db.Query<Recipe>(
                    "select Id, Name from Recipe where Name = @name",
                    new { name = "Fruit Salad" });

                // Demonstrate that OrmLite Dapper API Query method fails with complex types.
                // Note: This query includes the "Ingredients" complex type column, and it throws an exception.
                var complexObjectQueryResults = db.Query<Recipe>(
                    "select * from Recipe where Name = @name",
                    new { name = "Fruit Salad" });                
            }
        }
        
        private class Recipe
        {
            [AutoIncrement]
            public int Id { get; set; }

            public string Name { get; set; }

            public Dictionary<string, string> Ingredients { get; set; }
        }
    }
}
2

This is an embedded version of Dapper, you’ll need to refer to the upstream Dapper project about any issues or missing features. But it’s likely that Dapper just doesn’t support complex types, OrmLite is unique in supporting and transparently blobbing complex types.

I am using the Query method to force execution of SQL parameterized queries

OrmLite also provides API’s that use parameterized queries, e.g:

var complexObjectQueryResults = db.Select<Recipe>(
    "select * from Recipe where Name = @name",
    new { name = "Fruit Salad" });

All OrmLite API’s properly escape parameters so I’m not seeing the risk of SQL injection unless you’re concatenating the string values yourself.

3

Thanks for the quick reply and guidance!

I was going off apparently outdated guidance in a Stack Overflow message thread that only the Query and Where methods provided parameterized queries. Having this ability with the Select method is great.