Duplicate field name issue in a nested multi-page Dapper pagination request

Question

I ran into a problem while trying to do multi-mapping using Dapper for paginated queries.

Since I use a subquery in this pagination scenario, there are several tables inside the subquery that I have to join to get my data with multiple mappings, but some of these tables will share some fields with the same name that you can be seen in my example 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 above example, you can see that inside the subquery there will be problems with id fields (displayed in the posts table, both the join of the users table and the join of the topics table), as well as displayname and email (appear in both users tables).

The only workaround I’ve been thinking about so far involves casting each of these “problem” fields as a different name, but the very complicated process of creating dummy properties in the affected models is the same, so multimodal objects can be displayed in them. and editing the “real” properties in my models to also check the property of a dummy value if the real value has not been set.

In addition, in the above scenario, I would need to create the properties of the dummy properties x, where x is the number of joins that I can have in one table in the query (in this example, 2 joins in one user table, so this requires 2 uniquely named dummy properties for Dapper mapping purposes only).

This is obviously not perfect, and I'm sure you would run into problems and more sloppiness as I created more of these multi-page pagination requests.

I hope there is a good, clean solution to this problem?

Answer 1

There are two options that I can think of:

option 1 : join your advanced properties outside of your subquery:

 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 : SqlBuilder extension to handle column aliases:

 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 an alias macro to query and cache the list of columns from db using INFORMATION_SCHEMA.COLUMNS and just add the line "column as column_t1" for each column.

Unalias can do the opposite quite simply.