Can I map a result to Tuple in Dapper?

Question

I am trying to select a list of two integer columns map the results to a Tuple<int,int>. For example:

connection.Query<Tuple<int, int>>("select id1, id2 from sometable").ToList();

does not work, but the same query does work if I create a class with two integers such as:

public class BogusClass {
    public int id1 { get; set; }
    public int id2 { get; set; }
}

connection.Query<BogusClass>("select id1, id2 from sometable").ToList();

My preference is not to have to create some bogus class just to get some data to work with. In this case it is two integer columns, but there are other use cases I could think of.

EDIT: I fixed this issue by changing

connection.Query<Tuple<int, int>>("select id1, id2 from sometable").ToList();

to

connection.Query<int, int, Tuple<int, int>>("select id1, id2 from sometable", Tuple.Create, splitOn: "*").ToList();

Answer 1

This works starting from C# 7. This is a Value Tuple

public (int Id, DateTime? PublishDate) GetItem(string id)
{
    const string sqlCommand = "select top 1 Id, PublishDate from Item where Id = @id";

    return _connection.Query<(int, DateTime?)>(sqlCommand, new { id }).FirstOrDefault();
}       

Using the method

var item = GetItem(123);
Console.WriteLine($"The publish date of item [{item.Id}] is [{item.PublishDate.Value}]");

Make sure you have installed Dapper 1.50.4 or later.

Answer 2

Here is a working example:

public class DapperTests
{
    [Test]
    public void TuppleTest()
    {
        var conn = new SqlConnection(@"Data Source=.\sqlexpress; Integrated Security=true; Initial Catalog=mydb");
        conn.Open();

        var result = conn.Query<int, int, Tuple<int, int>>(
            "select 1,2 union all select 4,5", Tuple.Create, splitOn: "*").ToList();

        conn.Close();

        Assert.That(result.Count, Is.EqualTo(2));
    }
}

Answer 3

You can like so

string query = "Select value1 as Item1,value2 as Item2 from #sometable";
var data = db.Query<Tuple<int,int>>(query);

Answer 4

Tuple is one option, I prefer using a dynamic result whenever I do not want to create a class, i.e.,

string sql = "Select 'f' as Foo, 'b' as Bar";

var result = connection.Query<dynamic>(sql).Single();

string foo = result.Foo;
string bar = result.Bar

The name of the field returned from the result will be the name of the dynamic property.

In your case, you are wanting to return a list and not assign to single variables, so a Tuple would be more appropriate:

string sql = "select id1, id2 from sometable";

List<Tuple<int, int>> result = conn.Query<int, int, Tuple<int, int>>( // *1
    sql,
    Tuple.Create, // *2
    splitOn: "*" ) // *3
    .AsList(); // *4

*1 = <int,int, Tuple<int, int>> tells dapper that there will be two integers that will return a Tuple

*2 = tells dapper to use a Tuple to return the result

*3 = tells dapper that every field returned is used to return a result for each property of the Tuple.

*4 = Dapper extension method to cast Dapper's internal result to a List; by default, Dapper returns a list under the covers so the cast will be faster than copying to a new list.