The multi-part identifier xxx could not be bound. The multi-part identifier xxx could not be bound

Question

I am trying to convert this code from EF Core LINQ query

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();

to Dapper query

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 });

But I am getting errors for the Dapper query:

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

Side note: the row-number business is much better if it's just done using `OFFSET` eg `ORDER BY ec.endpoint_id OFFSET @StartRow - 1 ROWS FETCH NEXT @EndRow - @StartRow + 1 ROWS ONLY`. but it would be even better if you rethought how you were doing paging, and instead use Keyset Pagination. See https://stackoverflow.com/questions/70519518/is-there-any-better-option-to-apply-pagination-without-applying-offset-in-sql-se/70520457#70520457 — Charlieface, Jun 28 '23 at 10:11

score 1 · Accepted Answer · answered Jun 28 '23 at 04:55

The "outer" query in your Dapper code can only refer to the subquery which is referenced with the Sub alias - it does not see / have access to the ec table alias which is only available inside the subquery.

So use this code instead:

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 });

The multi-part identifier xxx could not be bound. The multi-part identifier xxx could not be bound

1 Answers1