Dapper and anonymous Types

Question

Is it possible to use anonymous types with Dapper?

I can see how you can use dynamic i.e.

connection.Query<dynamic>(blah, blah, blah) 

is it then possible to do a

.Select(p=> new { A, B ,C }) 

or some variation of that afterwards?

Edit

I thought I'd show you how I am using Dapper at the moment. I tend to cache (using an InMemoryCache) data so I just do one big query at the beginning (which is super quick using Dapper) then I use Linq to sort it all out in my Repository.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Linq;
using Dapper;

namespace SomeNamespace.Data
{
public class DapperDataContext : IDisposable
{
    private readonly string _connectionString;
    private readonly DbProviderFactory _provider;
    private readonly string _providerName;

    public DapperDataContext()
    {
        const string connectionStringName = " DataContextConnectionString";
        _connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
        _providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
        _provider = DbProviderFactories.GetFactory(_providerName);
    }

    public IEnumerable<MyDataView> MyData1 { get; private set; }
    public IEnumerable<MyDataView> MyData2 { get; private set; }

    protected string SqlSelectMyTable1Query
    {
        get
        {
            return @"SELECT Id, A, B, C from table1Name";
        }
    }   


protected string SqlSelectMyTable2Query
{
    get
    {
    return @"SELECT Id, A, B, C from table2Name";
    }
    }

    public void Dispose()
    {
    }

    public void Refresh()
    {
        using (var connection = _provider.CreateConnection())
        {
            // blow up if null
            connection.ConnectionString = _connectionString;
            connection.Open();

            var sql = String.Join(" ",
                            new[]
                                {
                                    SqlSelectMyTable1Query,
                                    SqlSelectMyTable2Query
                                });

            using (var multi = connection.QueryMultiple(sql))
            {
                MyData1 = multi.Read<MyDataView>().ToList();
                MyData2 = multi.Read<MyDataView>().ToList();
            }
        }
    }

    public class MyDataView
    {
        public long Id { get; set; }
        public string A { get; set; }
        public string B { get; set; }
        public string C { get; set; }
    }      
}
}

The InMemoryCache looks like this

namespace Libs.Web
{
public class InMemoryCache : ICacheService
{
    #region ICacheService Members

    public T Get<T>(string cacheId, Func<T> getItemCallback) where T : class
    {
        var item = HttpRuntime.Cache.Get(cacheId) as T;
        if (item == null)
        {
            item = getItemCallback();
            HttpContext.Current.Cache.Insert(cacheId, item);
        }
        return item;
    }

    public void Clear(string cacheId)
    {
        HttpContext.Current.Cache.Remove(cacheId);
    }

    #endregion
}

public interface ICacheService
{
    T Get<T>(string cacheId, Func<T> getItemCallback) where T : class;
    void Clear(string cacheId);
}
}

Answer 1

Is it possible to use anonymous types with Dapper?

Sure see the non-generic Query override, it return a dynamic IDictionary<string, object> this object is an expando that can either be cast or accessed with dot notation.

Eg:

var v = connection.Query("select 1 as a, 2 as b").First(); 
Console.Write("{0} {1}",v.a, v.b) // prints: 1 2

is it then possible to do a .Select

Sure, you get an IEnumerable<dynamic> ... you can run anything you want on that.

Answer 2

Here's another solution to use anonymous types with dapper:

public static class DapperExtensions
{
    public static IEnumerable<T> Query<T>(this IDbConnection connection, Func<T> typeBuilder, string sql)
    {
        return connection.Query<T>(sql);
    }
}

and use it like this:

var data = connection.Query(() => new 
{
    ContactId = default(int),
    Name = default(string),
}, "SELECT ContactId, Name FROM Contact");

Answer 3

Just a small improvement of Guillaume86's great solution:

public static IEnumerable<T> Query<T>(this IDbConnection connection, Func<T> typeBuilder,
    string sql, dynamic param = null, IDbTransaction transaction = null,
    bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
{
    return SqlMapper.Query<T>(connection, sql, param, transaction, buffered,
        commandTimeout, commandType);
}

Answer 4

An alternative approach is to use Tuple types that were added as part of C# 7.0

var users = dbConnection
    .Query<(Guid UserId, string Username)>("SELECT UserId, Username From MyUsers")
    .AsList();

Answer 5

Tried Guillaume86 approach, but I was getting 'object must implement IConvertible'.

So I went to another approach:

public static class DapperExtensions
{
    public static IEnumerable<T> QueryTyped<T>(
        this IDbConnection connection,
        string sql,
        Func<dynamic, T> typeBuilder)
    {
        var items = connection.Query<dynamic>(sql);
        var result = items.Select(typeBuilder);
        return result;
    }
}

Usage:

var rows = connection.QueryTyped(sql,
    typeBuilder: (d) => new
    {
        Code = d.Code,
        Name = d.Name,
        FullName = d.Name + " " + d.LastName,
    });

This allows you map even a C# function if you need to.