Duplicate field name problem in a nested multi-mapping Dapper pagination query

Question

I've ran into an issue when trying to do multi-mapping using Dapper, for pagination queries.

Because I am using a nested query in this pagination scenario, there are multiple tables within the nested query that I must join to get my multi-mapped data, but some of these tables will share some fields of the same name which you can see in my example query below (e.g. id, displayname and email):

q = @"select * from (select p.id, p.title, p.etc...,
u1.id, u1.displayname, u1.email,
u2.id, u2.displayname, u2.email,
t.id, t.name,
row_number() over (order by " + sort.ToPostSortSqlClause() + ") as rownum" +
" from posts p" +
" join users u1 on p.owneruserid = u1.id" +
" join users u2 on p.lastediteduserid = u2.id" +
" join topics t on p.topicid = t.id" +
") seq where seq.rownum between @pLower and @pUpper";

In the example above you can see that within the nested query, there are going to be problems with the fields id (appears in the posts table, both users table joins and the topics table join), and also displayname and email (appear in both users table joins).

The only workaround I have thought of so far involves casting each of these 'problem' fields as a different name, but this then involves the very messy process of creating dummy properties in the affected models, so multimapping can map into these, and editing the 'real' properties in my models to also check the dummy property for a value if the real value has not been set.

Also, in the above scenario I would have to create x dummy properties where x is the number of joins I may have on the same table within a query (in this example, 2 joins on the same Users table, therefore requiring 2 uniquely named dummy properties just for Dapper mapping purposes).

This is obviously not ideal and I'm sure would have knock on problems and more untidyness as I created more of these multi-mapping pagination queries.

I'm hoping there is nice, clean solution to this problem?

Answer 1

There are 2 options I can think of:

option 1: join back to your extended properties outside of your nested query:

select s.*, t1.*, t2.* from 
(
select s.*, ROW_NUMBER() OVER (order by somecol) AS RowNumber from Something s
) as X 
left join Table t1 on Id = x.SomeId
left join Table t2 on Id = x.SomeOtherId

---

option 2: Extend SqlBuilder to handle column aliasing:

select s.*, /**unalias(Table,t1)**/, /**unalias(Table,t2)**/ from 
        (
        select s.*, /**alias(Table,t1)**/, /**alias(Table,t2)**/ ROW_NUMBER() OVER (order by somecol) AS RowNumber from Something s
        left join Table t1 on Id = x.SomeId
        left join Table t2 on Id = x.SomeOtherId
        ) as X 

Then define the alias macro to query and cache a list of columns from the db using INFORMATION_SCHEMA.COLUMNS and simply add a 'column as column_t1` string for each column.

Unalias can do the reverse quite simply.