EF Core LINQ转Dapper查询报错：多部分标识符无法绑定

问题场景 #

将EF Core的LINQ分页查询转换为Dapper实现时，运行SQL出现以下报错：

The multi-part identifier "ec.endpoint_id" could not be bound.
The multi-part identifier "ec.payload" could not be bound.

原EF Core LINQ代码：

var endpointCInBatch = await (from ec in _appDbContext.EndpointC
                              join ept in _appDbContext.EndpointP on ec.endpoint_id equals ept.endpoint_id
                              join od in _appDbContext.ObjDetails on ept.obj_id equals od.obj_id
                              where od.obj_id == 1
                              select new EndpointCacheTrim { endpoint_id = ec.endpoint_id, payload = ec.payload })
                             .Skip((currentPage - 1) * batchSize)
                             .Take(batchSize)
                             .ToListAsync();

原Dapper错误代码：

var endpointCInBatch = await connection.QueryAsync<EndpointCTrim>(@"
                SELECT ec.endpoint_id, ec.payload
                FROM (
                    SELECT ec.endpoint_id, ec.payload,
                           ROW_NUMBER() OVER (ORDER BY ec.endpoint_id) AS RowNum
                    FROM endpointc AS ec
                    JOIN endpointp AS ept ON ec.endpoint_id = ept.endpoint_id
                    JOIN obj_details AS od ON ept.obj_id = od.obj_id
                    WHERE od.obj_id = 1
                ) AS Sub
                WHERE Sub.RowNum BETWEEN @StartRow AND @EndRow",
            new { StartRow = (currentPage - 1) * batchSize + 1, EndRow = currentPage * batchSize });

错误原因 #

外层SELECT语句中使用了ec.endpoint_id和ec.payload，但ec是子查询内部的表别名，外层查询无法访问子查询内部的别名，只能通过子查询的别名Sub来获取其返回的列。

修正后的Dapper代码 #

var endpointCInBatch = await connection.QueryAsync<EndpointCTrim>(@"
                SELECT Sub.endpoint_id, Sub.payload
                FROM (
                    SELECT ec.endpoint_id, ec.payload,
                           ROW_NUMBER() OVER (ORDER BY ec.endpoint_id) AS RowNum
                    FROM endpointc AS ec
                    JOIN endpointp AS ept ON ec.endpoint_id = ept.endpoint_id
                    JOIN obj_details AS od ON ept.obj_id = od.obj_id
                    WHERE od.obj_id = 1
                ) AS Sub
                WHERE Sub.RowNum BETWEEN @StartRow AND @EndRow",
            new { StartRow = (currentPage - 1) * batchSize + 1, EndRow = currentPage * batchSize });

修改说明 #

将外层SELECT中的ec.endpoint_id、ec.payload改为Sub.endpoint_id、Sub.payload，外层通过子查询的别名Sub来引用子查询返回的列，即可解决标识符无法绑定的问题。

内容的提问来源于stack exchange，提问作者Steve