Servicestack ORMLite Query Multiple

Question

I was wondering if ORMLite had a QueryMultiple solution like dapper.

My use case is in getting paged results.

return new {
  Posts = conn.Select<Post>(q => q.Where(p => p.Tag == "Chris").Limit(20, 10))
  TotalPosts = conn.Count<Post>(q.Where(p => p.Tag == "Chris"))
};

I also have a few other cases where I'm calculating some other stats in addition to a main query, and I'm keen to avoid multiple roundtrips.

(Probably unrelated, but I'm using PostgreSQL)

Take a look at https://stackoverflow.com/questions/37416424/how-do-i-join-2-tables-in-servicestack-ormlite-and-select-both-classes/37420341#37420341 — labilbe, Jul 06 '18 at 13:09

score 4 · Answer 1 · answered May 09 '13 at 19:32

4

You can probably do something like this:

var bothThings = db.Exec(cmd => {

    cmd.CommandText = @"
        select * from TableA
        select * from TableB";

    var both = new BothAandB();

    using (var reader = cmd.ExecuteReader())
    {
        both.a = reader.ConvertToList<A>();
        reader.NextResult();
        both.b = reader.ConvertToList<B>();
    }

    return both;

});

It might be possible to wrap this up in an extension method, but nothing clever is coming to mind.

answered May 09 '13 at 19:32

Master Morality

5,837
6
31
43

1

This actually won't work in it's current implementation. `ConvertToList` applies a `using(reader)` internally which closes the reader upon dispose, preventing `NextResult()` from being accessible. – Jeremy Smith Nov 01 '13 at 15:07

score 2 · Answer 2 · answered Mar 13 '14 at 18:13

You can create some helper OrmLite extensions (works in v 3.9.55.0) pretty easily that will NOT wrap the reader. It is rather easy since the methods you need are public. Here is how I did it.

public static class MultiResultReaderOrmLiteExtensions
{
    public static IList CustomConvertToList<T>(this IDataReader dataReader)
    {
        var modelDef = ModelDefinition<T>.Definition;
        var type = typeof (T);
        var fieldDefs = modelDef.AllFieldDefinitionsArray;
        var listInstance = typeof(List<>).MakeGenericType(type).CreateInstance();
        var to = (IList)listInstance;
        var indexCache = dataReader.GetIndexFieldsCache(modelDef);
        while (dataReader.Read())
        {
            var row = type.CreateInstance();
            row.PopulateWithSqlReader(dataReader, fieldDefs, indexCache);
            to.Add(row);
        }
        return to;
    }

    public static Dictionary<string, int> GetIndexFieldsCache(this IDataReader reader, 
        ModelDefinition modelDefinition = null)
    {
        var cache = new Dictionary<string, int>();
        if (modelDefinition != null)
        {
            foreach (var field in modelDefinition.IgnoredFieldDefinitions)
            {
                cache[field.FieldName] = -1;
            }
        }
        for (var i = 0; i < reader.FieldCount; i++)
        {
            cache[reader.GetName(i)] = i;
        }
        return cache;
    }
}

Then you can call like something like this:

using (var db = _connectionFactory.OpenDbConnection())
{
    var cmd = db.api_GetSprocWithMultResults(id);
    using (IDataReader reader = cmd.DbCommand.ExecuteReader())
    {
        meta = reader.CustomConvertToList<Element_Media_Meta>().Cast<Element_Media_Meta>().ToList();
        reader.NextResult();
        queues = reader.CustomConvertToList<Element_Media_ProcessQueue>().Cast<Element_Media_ProcessQueue>().ToList();

    }
}

Servicestack ORMLite Query Multiple

2 Answers2