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();

MonkeyDreamzzz · Answer 1 · 2018-02-28T15:34:37.550

37

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.

edited Feb 28 '18 at 15:34

answered Jan 17 '18 at 11:12

MonkeyDreamzzz

3,978
1
39
36

1

Rubanov: prior to Dapper 1.50.4 all I would get is value tuples with default values. VS 2017 in a .net 4.7 project – JasonCoder Feb 27 '18 at 16:12

score 28 · Accepted Answer · edited Oct 05 '20 at 16:17

28

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));
    }
}

edited Oct 05 '20 at 16:17

Nick DeVore

9,748
3
39
41

answered Jan 19 '16 at 17:34

Void Ray

9,849
4
33
53

If you are using VB.Net, you will need to use "AddressOf Type.Create" instead of "Type.Create". – an phu Aug 23 '22 at 20:42

score 26 · Answer 3 · answered Jan 26 '17 at 22:31

26

You can like so

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

answered Jan 26 '17 at 22:31

Beffyman

261
3
2

Metro Smurf · Answer 4 · 2017-10-17T13:29:32.627

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.

Thanks Metro Smurf. This is a cool feature I did not know about and probably the better approach. I am accepting Void Ray's answer since that is technically the answer to the question I asked. — Tom Gerken, Jan 19 '16 at 18:50

score 4 · Answer 5 · answered Mar 28 '19 at 21:36

4

For those using async, this can be achieved by using ValueTuple.

var res = await conn.QueryAsync<(int Id1, int Id2)>(sql);

List<Tuple<int, int>> tuples = res.Select(x => new Tuple<int, int>(x.Id1, x.Id2)).ToList();

answered Mar 28 '19 at 21:36

judehall

884
12
27

score 4 · Answer 6 · answered Nov 11 '22 at 18:02

Decided to add another answer, b/c I've seen people make this mistake all the time.

Yes, you can, however! Be careful, when using named-tuple aka valuetuple, Dapper does not pay attention to the names, it maps the tuple sequentially.

For example this code:

cn.Query<(int ID, int Size)("SELECT Size, ID FROM Table")

will put the Size into ID and ID into Size.

Again: Dapper does not look at the valuetuple names (and that's actually awesome b/c no reflection - yay!) but pay extra attention

score 3 · Answer 7 · answered Feb 23 '21 at 04:22

this has worked for me in .NET core 3.1

string sql = "SELECT UserAcctId, MinAge, MaxAge FROM [MemberDetails]";

using IDbConnection dbConnection = new SqlConnection(_connectionString);
dbConnection.Open();

var results = dbConnection.Query<(int userAcctId, short minAge,  short maxAge)>(sql);
foreach (var item in results) {
   if (item.minAge > 0) { 
    // do something
   }
}

Can I map a result to Tuple in Dapper?

7 Answers7

Linked