How to use Dapper with conditional StringBuilder query?

Question

I am trying to build a dynamic query string and apply it on dapper (somehow giving a similar "feel" of the Entity Framework optional ".include(x=>x.XXX)" functionality):

Build query string

var query = new StringBuilder();
query.Append(" select * from test_post p ");
if (validIncludesToPerform[0]) 
              query.Append(" left join sys_user u on u.Id = p.CreatorId ");
if (validIncludesToPerform[1]) 
              query.Append(" left join test_poststatus s on s.Id = p.StatusId ");
query.Append(" where p.Id = @Id; ");

var isQueryMultiple = false;
if (validIncludesToPerform[2])
{
    isQueryMultiple = true;
    query.Append(" select * from test_postnote n where n.PostId = @Id; ");
}
if (validIncludesToPerform[3])
{
    isQueryMultiple = true;
    query.Append(@" select * 
                   from test_post_tag pt 
                   left join test_tag t on t.Id = pt.TagId 
                   where pt.PostId = @Id; ");
}

Process query

Post pst;
if (isQueryMultiple)
{
    using (var multi = Connection.QueryMultiple(query.ToString(), new { Id = id }))
    {
        pst = multi.Read<Post, User, PostStatus, Post>((post, user, status) =>
        {
            if (post == null) return null;
            if (user != null) post.Creator = user;
            if (status != null) post.Status = status;
            return post;
        }).FirstOrDefault();

        if (pst != null && validIncludesToPerform[2])
            pst.Notes = multi.Read<PostNote>().ToList();

        if (pst != null && validIncludesToPerform[3])
            pst.PostTags = multi.Read<PostTag, Tag, PostTag>((pTag, tag) =>
                                    {
                                        if (pTag == null) return null;
                                        if (tag != null) pTag.Tag = tag;
                                        return pTag;
                                    }).ToList();
    }
}
else
{
    pst = Connection.Query<Post, User, PostStatus, Post>
                        (query.ToString(), (post, user, status) =>
                        {
                            if (post == null) return null;
                            if (user != null) post.Creator = user;
                            if (status != null) post.Status = status;
                            return post;
                        }, new { Id = id }).FirstOrDefault();
}

Important note: Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter. This means that in my case I do NOT have to specify "Id,Id" because this part is already implicit.

• The part that is processing the PostNotes or the PostTags is working fine.
• The Post when is "including" the User and the Status is working fine as well.
• If FAILS when I try to load the User "empty" or just with User OR Status...

Error message: "When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id\r\nParameter name: splitOn"

Reason: The use of the generics implies that the query is going to receive "3 parts" but it is just receiving a lower number....

Ugly solutions: I know that this would work if I simply would separate the Status and User into each independent query:

if (validIncludesToPerform[0])
   query.Append(@" select u.* from sys_user u right join test_post p on u.Id = p.CreatorId 
                   where p.Id = @Id;");
if (validIncludesToPerform[1])
   query.Append(@" select ps.* from test_poststatus ps right join test_post p on ps.Id = p.StatusId 
                   where p.Id = @Id;");

but since this information is like a 1 to 1 relation, I believe that a single query is better. So, I am trying to avoid using this solution.

I also do NOT want to have a list of all possible combinations of the generics signature like:

• Connection.Query<Post, User, PostStatus, Post>
• Connection.Query<Post, User, Post>
• Connection.Query<Post, PostStatus, Post>
• Connection.Query<Post>

This approach is simply not friendly as soon as I start adding more 1to1 relations to this class.

How can I fix this?

Answer 1

I was able to find a "somehow" less bad approach:

        var query = new StringBuilder();
        query.Append(" select * from test_post p ");

        query.Append(validIncludesToPerform[0]
            ? " left join sys_user u on u.Id = p.CreatorId "
            : " left join sys_user u on 1 = 0 ");

        query.Append(validIncludesToPerform[1]
            ? " left join test_poststatus s on s.Id = p.StatusId "
            : " left join test_poststatus s on 1 = 0 ");

        query.Append(" where p.Id = @Id; ");

I will leave this answer for now while I wait for better alternatives....

Answer 2

You need to tell dapper where to split on - where the boundary between the objects is in your select statement.That's what your error statement says. So modify your call to something like:

pst = multi.Read<Post, User, PostStatus, Post>((post, user, status) =>
{
   if (post == null) return null;
   if (user != null) post.Creator = user;
   if (status != null) post.Status = status;
   return post;
}, splitOn: "Id,Id").FirstOrDefault();

The split on tell Dapper where to stop mapping into one object and start mapping into the other. Since you have 2 joins you need to have two fields in split on. Make sure those two are placed in a single string separated by comma and that there is no space characters inside the string...

For better explanation see this post:

Correct use of Multimapping in Dapper