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

zi8p0yeb 于 2023-06-28 发布在其他

关注(0)|答案(1)|浏览(112)

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.

sql-server

来源：https://stackoverflow.com/questions/76570103/the-multi-part-identifier-xxx-could-not-be-bound-the-multi-part-identifier-xxx

1条答案

按热度按时间

i2byvkas1#

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

赞(0）回复(0）举报 2023-06-28

我来回答

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

1条答案

相关问题

热门标签

最新问答