Return duplicate values When use Dapper

Question

I call a stored procedure with Dapper. The stored procedure returns a pageable list. I have 30 records in the database.

But every series of repeated values is returned:

IEnumerable<TEntity> entities;
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@page", page, DbType.Int32, ParameterDirection.Input);
parameters.Add("@pageSize", pageSize, DbType.Int32, ParameterDirection.Input);
parameters.Add("@search", search, DbType.String, ParameterDirection.Input);

try
{
    _connection.Open();
    entities = await _connection.QueryAsync<TEntity>($"{storedProcedure}", parameters);
}
catch (Exception)
{
    return null;
}
finally
{
    _connection.Dispose();
    _connection.Close();
}

return entities.ToList();

This is the stored procedure:

ALTER PROCEDURE [dbo].[SP_GetCategories]
    @search nvarchar(300) = '',
    @page int = 1,
    @pageSize int = 10
AS
BEGIN
    SET @page = (@page - 1) * @pageSize;
    DECLARE @Sql Nvarchar(800)
    SET @Sql=' select * from Dy.Category As C With(Nolock) where  IsDeleted=0 ';

    IF @search IS NOT NULL AND @search <> ''
    BEGIN
        SET @Sql=@Sql+' AND C.Title like N''%''' +@search +'''%'' ' ;
    END

    SET @Sql = @Sql +' Order By CreateTime Desc OFFSET '+CAST(@page AS NVARCHAR(100))+

' ROWS FETCH NEXT ' +CAST(@pageSize AS NVARCHAR(100))+' ROWS ONLY';

    EXEC sp_executesql @Sql
END

Answer 1

I defined the command type:

CommandType.StoredProcedure

entities = await _connection.QueryAsync<TEntity>($"{storedProcedure}", parameters,commandType:CommandType.StoredProcedure);