Dapper splitOn error

Question

I am trying to have dapper return List<KeyValuePair<int, dynamic>>

I have made what I believe to be the absolute simplest possible query to illustrate the issue:

using (SqlConnection conn = new SqlConnection(_connectionString))
{
            return conn.Query<int, string, bool, KeyValuePair<int, dynamic>>("SELECT 1 AS 'SomeInt', 'A' AS 'SomeString', 'True' AS 'SomeBool'", (i, s, b) =>
                new KeyValuePair<int, dynamic>(
                    i,
                    new
                    {
                        TheString = s,
                        TheBool = b
                    }
                ), splitOn: "SomeString").AsList();
}

When I try to run this I get System.ArgumentException: 'When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id.

I think that I am specifying the splitOn parameter correctly as SomeString ... ?

I have looked at a number of other SO questions regarding this error including Dapper Multi-mapping Issue, When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id", "splitOn, and Correct use of Multimapping in Dapper, and if the answer is in one of those I can't find it.

I specifically tried to mirror what the answer here Correct use of Multimapping in Dapper, but it doesn't work for me.

I did notice that in the vast majority of other examples, the code looks like conn.Query<x, y, x> (i.e. the return value is listed as the first value as well). I'm not sure if this matters or not, or even if I'm able to do what I want. I'm pretty new to Dapper.

Thank you in advance!

Can you go into detail on why List<KeyValuePair<int, dynamic>> is the desired outcome? — Parrish Husband
@ParrishHusband What I really want is List<KeyValuePair<int, Tuple<string, bool>>>, but I want to name the string and the bool, not item1 and item2. I am aware that I could create a class with a string and a bool and have dapper map to that class, but this is the only place in the whole code base where I would reference that class, so it seems ... excessive I guess. (This is in an API, so after I execute the query I send the results as JSON to a front-end website, and I don't want the front-end to have to deal with item1 and item2.) — Brian
"this is the only place in the whole code base where I would reference that class". Right now that may be true, but if requirements ever change down the road you're stuck trying to manipulate a complicated structure instead of just basic encapsulation. Dapper's strength lies in wiring up your POCO classes easily and not needing to dig in the weeds as you are now. — Parrish Husband
You need a splitOn value for each of your int, string, bool objects, the final type is your return object — Richard Hubley

Carlo Bos Carlo Bos · Accepted Answer · 2018-08-21T18:55:35

You'll need to add the other fields in the splitOn value because each is a distinct class. Since each could be mapped to a separate POCO class, but in your case each is a distinct value.

using (SqlConnection conn = new SqlConnection(_connectionString))
{
    return conn
        .Query<int, string, string, KeyValuePair<int, dynamic>>(
            "SELECT 1 AS 'SomeInt', 'A' AS 'SomeString', 'True' AS 'SomeBool'",
            (i, s, b) =>
                new KeyValuePair<int, dynamic>(
                    i,
                    new
                    {
                        TheString = s,
                        TheBool = Convert.ToBoolean(b)
                    }),
            splitOn: "SomeInt,SomeString,SomeBool")
        .AsList();
}

Also I had to fix the boolean, since it was not converted correctly. Maybe in your case it's read from an actual database table and would work.

To illustrate the power of splitOn, take the following modification to the previous answer. This one splits the 3 values returned from the SQL query as 2 objects, the 1st into the int object and the 2nd and 3rd into a single dynamic object:

conn
    .Query<int, dynamic, KeyValuePair<int, dynamic>>(
        "SELECT 1 AS 'SomeInt', 'A' AS 'SomeString', 'True' AS 'SomeBoolean'",
        map: (i, d) => new KeyValuePair<int, dynamic>(i, new
        {
            TheString = d.SomeString, 
            TheBool = d.SomeBoolean
        }),
        splitOn: "SomeInt,SomeString")
   .AsList();

Dapper splitOn error

1 Answers