How to use Dapper with conditional StringBuilder query?

c# dapper

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?

Accepted Answer

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....



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why