C#7 Tuples (i.e. ValueType)

[troyji]

I would like to see NPoco support tuples from the upcoming c# 7 release.

https://github.com/dotnet/roslyn/blob/master/docs/features/tuples.md
https://github.com/dotnet/roslyn/blob/features/tuples/docs/features/ValueTuples.cs

It might look something like this:

(int count, int sum) = db.Single<ValueType<int, int>>("select count(1), sum(val) from values");

and this:

var summary = db.Fetch<ValueType<string,int,int>>("select name, count(1), sum(val) from values group by name");
(string name, int count, int sum) = summary.First();

Might as well add support for the existing Tuple type at the same time since the implementation would be similar.

It might look something like this:

Tuple<int, int> result = db.Single<Tuple<int, int>>("select count(1), sum(val) from values");

and this:

List<Tuple<string, int, int>> summary = db.Fetch<Tuple<string,int,int>>("select name, count(1), sum(val) from values group by name");

Allowing query strings formatting with ITuple would support at least ValueTuple, Tuple, and KeyValuePair.

It might look something like:

var record = (2, "Timmy", 5);
db.Execute("update kids set name = @1, age = @2 where id = @0", record);

and this:

Tuple<int, string, in> record = Tuple.Create(2, "Timmy", 5);
db.Execute("update kids set name = @1, age = @2 where id = @0", record);

and this:

var settings= new Dictionary<string, string>() { { "ItemsPerPage", "10" }, { "ShowLogo", "true" } };
foreach(var setting in settings) db.Execute("update settings set value = @1 where key = @0", setting);

[schotime]

Sounds like a good idea

[kcragin]

I second this idea. Note you could use the idiomatic syntax as well:

return Dao.Db.Fetch<(long, string)>("select col1, col2, ...");

Until someone implements this, the workaround is

return Dao.Db.Query<dynamic>("select col1, col2, ...").Select(x => ((long)x.col1, (string)x.col2)).ToList();

It may make sense to break this enhancement into several issues so others can tackle them in smaller bites.

[asztal]

It's already possible, to an extent, if you like massive hacks. It probably performs terribly, only supports basic data types, and doesn't support anything bigger than 8-tuples, but here is one way to do it!

    // var db = new Database(...);
    // MappingFactory.RowMappers.Insert(0, () => new ValueTupleRowMapper(new MapperCollection()));
    class ValueTupleRowMapper : IRowMapper
    {
        private Func<DbDataReader, object> mapper;
        private MapperCollection mappers;
        private static Cache<(Type, MapperCollection), Func<DbDataReader, object>> cache
             = new Cache<(Type, MapperCollection), Func<DbDataReader, object>>();

        public ValueTupleRowMapper(MapperCollection mappers) {
            this.mappers = mappers;
        }

        public void Init(DbDataReader dataReader, PocoData pocoData) {
            mapper = GetRowMapper(pocoData.Type, this.mappers);
        }

        public object Map(DbDataReader dataReader, RowMapperContext context) {
            return mapper(dataReader);
        }

        public static bool IsValueTuple(Type type) {
            if (!type.IsGenericType)
                return false;
                
            var baseType = type.GetGenericTypeDefinition();
            return (
                baseType == typeof(ValueTuple<>) || 
                baseType == typeof(ValueTuple<,>) || 
                baseType == typeof(ValueTuple<,,>) || 
                baseType == typeof(ValueTuple<,,,>) || 
                baseType == typeof(ValueTuple<,,,,>) || 
                baseType == typeof(ValueTuple<,,,,,>) || 
                baseType == typeof(ValueTuple<,,,,,,,>)
            );
        }

        public bool ShouldMap(PocoData pocoData) {
            return IsValueTuple(pocoData.Type);
        }

        private static Func<DbDataReader, object> GetRowMapper(Type type, MapperCollection mappers) {
            return cache.Get((type, mappers), () => CreateRowMapper(type, mappers));
        }

        private static Func<DbDataReader, object> CreateRowMapper(Type type, MapperCollection mappers) {
            var argTypes = type.GetGenericArguments();
            var ctor = type.GetConstructor(argTypes);
            var reader = Expression.Parameter(typeof(DbDataReader), "reader");
            var getValue = typeof(DbDataReader).GetMethod("GetValue")!;
            var isDBNull = typeof(DbDataReader).GetMethod("IsDBNull")!;

            // reader => (object)new ValueTuple<T1, T2, ...>(value1, value2, ...);
            var expr = Expression.Lambda(
                Expression.Convert(
                    Expression.New(ctor, argTypes.Select((argType, i) => {
                        // reader.IsDBNull(i) ? (T)null : converter(reader.GetValue(i))
                        return Expression.Condition(
                            Expression.Call(reader, isDBNull, new [] { Expression.Constant(i) }),
                            Expression.Convert(Expression.Constant(null), argType),
                            Expression.Convert(
                                Expression.Invoke(
                                    Expression.Constant(
                                        MappingHelper.GetConverter(mappers, null, null, argType)
                                    ),
                                    new[] {
                                        Expression.Call(reader, getValue, new[] { Expression.Constant(i) } )
                                    }
                                ),
                                argType
                            )
                        );
                    })),
                    typeof(object)
                ),
                new [] { reader }
            );
            return (Func<DbDataReader, object>)expr.Compile();
        }
    }

Use like:

    var sql = "select 1, 'hello', 16 union all select 2, 'goodbye', 32 union all select 3, null, null";
    foreach (var (x, y, b) in db.Fetch<(int x, string y, byte? b)>(sql)) {
        Console.WriteLine($"Value: ({x}, {y}, {b})");
    }

[schotime]

Good job @asztal
Perf should be pretty good on this as my only concern was caching of the expression tree but you've done that.

[asztal]

Thanks for the feedback, I was worried about the performance of Expression.Compile() itself too, I guess, I don't know how quick that is. I saw elsewhere you've emitted IL directly but it seemed like that approach would be complex, especially generalising that to n-tuples -- with Expression it shouldn't be too hard to support 9-tuples or just nested tuples in general.

I'd be interested in improving it and submitting a PR if it would be useful. What I'm especially unsure of is how this would interact with other internals like PocoDataBuilder/PocoData/PocoMember/PocoColumn. Right now I don't think it's actually using the MapperCollection properly either since it passes null for dstType.

[schotime]

Will have a look at this for v5

[asztal]

For what it's worth, here's the code I'm currently using. I'm sure you'll probably want to take a different approach perhaps, to handle reference types inside tuples maybe, but I figured no harm in posting it here.

It supports nested tuples such as ((1, "abc"), ("def", 2)), which also allows it to support 8-tuples and beyond since they're just implemented as nested tuples.

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using NPoco;
using NPoco.RowMappers;

namespace Asztal.SuperAwesomeProject {
    class ValueTupleRowMapper : IRowMapper
    {
        private Func<DbDataReader, object> mapper = default!;
        private MapperCollection mappers;
        private static Cache<(Type, MapperCollection), Func<DbDataReader, object>> cache
             = new Cache<(Type, MapperCollection), Func<DbDataReader, object>>();

        public ValueTupleRowMapper(MapperCollection mappers) {
            this.mappers = mappers;
        }

        public void Init(DbDataReader dataReader, PocoData pocoData) {
            mapper = GetRowMapper(pocoData.Type, this.mappers, dataReader);
        }

        public object Map(DbDataReader dataReader, RowMapperContext context) {
            return mapper(dataReader);
        }

        public static bool IsValueTuple(Type type) {
            if (!type.IsGenericType)
                return false;

            var baseType = type.GetGenericTypeDefinition();
            return (
                baseType == typeof(ValueTuple<>) ||
                baseType == typeof(ValueTuple<,>) ||
                baseType == typeof(ValueTuple<,,>) ||
                baseType == typeof(ValueTuple<,,,>) ||
                baseType == typeof(ValueTuple<,,,,>) ||
                baseType == typeof(ValueTuple<,,,,,>) ||
                baseType == typeof(ValueTuple<,,,,,,>) ||
                baseType == typeof(ValueTuple<,,,,,,,>)
            );
        }

        public bool ShouldMap(PocoData pocoData) {
            return IsValueTuple(pocoData.Type);
        }

        private static Func<DbDataReader, object> GetRowMapper(Type type, MapperCollection mappers, DbDataReader dataReader) {
            return cache.Get((type, mappers), () => CreateRowMapper(type, mappers, dataReader));
        }

        private static Func<DbDataReader, object> CreateRowMapper(Type type, MapperCollection mappers, DbDataReader dataReader) {
            var reader = Expression.Parameter(typeof(DbDataReader), "reader");
            var (tupleExpr, _) = CreateTupleExpression(type, mappers, dataReader, reader, 0);

            // reader => (object)new ValueTuple<T1, T2, ...>(value1, value2, ...);
            var expr = Expression.Lambda(
                Expression.Convert(tupleExpr, typeof(object)),
                new [] { reader }
            );
            return (Func<DbDataReader, object>)expr.Compile();
        }

        private static (NewExpression expr, int fieldsIndex) CreateTupleExpression(Type type, MapperCollection mappers, DbDataReader dataReader, ParameterExpression reader, int fieldIndex) {
            var argTypes = type.GetGenericArguments();
            var ctor = type.GetConstructor(argTypes);
            var getValue = typeof(DbDataReader).GetMethod("GetValue")!;
            var isDBNull = typeof(DbDataReader).GetMethod("IsDBNull")!;

            if (argTypes.Count() > dataReader.FieldCount)
                throw new InvalidOperationException("SQL query does not return enough fields to fill the tuple");

            var args = new List<Expression>();

            foreach (var argType in argTypes) {
                if (IsValueTuple(argType)) {
                    // It's tuples all the way down
                    var (expr, newFieldIndex) = CreateTupleExpression(argType, mappers, dataReader, reader, fieldIndex);
                    args.Add(expr);
                    fieldIndex += newFieldIndex;
                } else {
                    if (fieldIndex >= dataReader.FieldCount)
                        throw new InvalidOperationException($"SQL query does not return enough fields to fill the tuple (missing type: {argType.FullName})");

                    var rawValue = Expression.Call(reader, getValue, new[] { Expression.Constant(fieldIndex) } );
                    var converter = MappingHelper.GetConverter(mappers, null, dataReader.GetFieldType(fieldIndex), argType);

                    // reader.IsDBNull(i) ? (T)null : converter(reader.GetValue(i))
                    args.Add(Expression.Condition(
                        Expression.Call(reader, isDBNull, new [] { Expression.Constant(fieldIndex) }),
                        Expression.Convert(Expression.Constant(null), argType),
                        Expression.Convert(
                            converter != null
                                ? (Expression)Expression.Invoke(Expression.Constant(converter), new[] { rawValue })
                                : (Expression)rawValue,
                            argType
                        )
                    ));

                    fieldIndex++;
                }
            }

            return (Expression.New(ctor, args), fieldIndex);
        }
    }
}

[schotime]

Thats awesome, what would the sql look like for this result?
((1, "abc"), ("def", 2))

[asztal]

It would simply take the columns from left to right. The example below might be a little inaccurate but the idea should make sense hopefully.

var (order, customer) = await db.SingleAsync<((Id: number, Price: decimal), (Id: Guid, Name: string))>(@"
    select o.Id, o.Price, c.Id, c.Name
    from Order o
    join Customer c on c.Id = o.CustomerId
    where c.Email = @Email
", new { Email = "[email protected]" })

Console.WriteLine($"Dear {customer.Name}, the price for order #{order.Id} is €{order.Price}");

[asztal]

My usual use case is a simple tuple of types with mappings - I'll spare the gory details but I have some custom types so I use something like this:

class OrderKey { public OrderKey(string key) { ... } ... }
class CustomerKey { public CustomerKey(string key) { ... } ... }
class EntityKeyMapper: NPoco.DefaultMapper { ... }

await db.FetchAsync<(OrderKey, CustomerKey)>("select Order.Id, Customer.Id from ...");

The ultimate goal is that I can get strongly typed values from the database without defining special classes for each query.

[schotime]

This is how I integrated it in.

    public class MappingFactory
    {
        public static List<Func<MapperCollection, IRowMapper>> RowMappers { get; private set; } 
        private readonly PocoData _pocoData;
        private readonly IRowMapper _rowMapper;

        static MappingFactory()
        {
            RowMappers = new List<Func<MapperCollection, IRowMapper>>()
            {
                x => new ValueTupleRowMapper(x),
                _ => new DictionaryMapper(),
                _ => new ValueTypeMapper(),
                _ => new ArrayMapper(),
                _ => new PropertyMapper()
            };
        }

        public MappingFactory(PocoData pocoData, DbDataReader dataReader)
        {
            _pocoData = pocoData;
            _rowMapper = RowMappers.Select(mapper => mapper(_pocoData.Mapper)).First(x => x.ShouldMap(pocoData));
            _rowMapper.Init(dataReader, pocoData);
        }

        public object Map(DbDataReader dataReader, object instance)
        {
            return _rowMapper.Map(dataReader, new RowMapperContext()
            {
                Instance = instance,
                PocoData = _pocoData
            });
        }
    }

Just had to add the MapperCollection to the Func
See any issues?

[schotime]

Did you miss type this type syntax?

await db.SingleAsync<((Id: number, Price: decimal), (Id: Guid, Name: string))>

[asztal]

Yeah, I've been using TypeScript too much... 😅

await db.SingleAsync<((int Id, decimal Price), (Guid Id, string Name))>

In the above code sample is x => new ValueTupleRowMapper(x) the only change? It looks fine to me, though you're probably more likely to be able to find any edge case bugs than I am, I'm not too familiar with the inner workings of NPoco.

[schotime]

haha, phewww, thought I was losing it.
yeah, thats pretty much it. I've created 5 tests for it too

[asztal]

Awesome, thanks, hopefully somebody finds this helpful :)

Looking at the original issue description, tuples as parameters is also mentioned. I haven't tried it, but do you think this would work (for up to 7 items)?

var record = (2, "Timmy", 5);
db.Execute("update kids set name = @Item2, age = @Item3 where id = @Item1", record);

If it does, perhaps it's just something that can be mentioned in the wiki rather than needing any special implementation.

[schotime]

It currently only checks properties, so I could add this after the property check and it does indeed work.

    var fi =  type.GetField(param);
    if (fi != null)
    {
        arg_val = fi.GetValue(o);
        found = true;
        break;
    }

Most of the time the property would be there, but it depends whether doing a specific tuple check would be faster that trying to get the field

[schotime]

Checking the type is way slower so we'll go with that for now.

[tbasallo]

That's a slick implementation up there. need to keep m eye on this.

Adding it to the wiki?

[schotime]

https://github.com/schotime/NPoco/wiki/Tuples