使用存储过程和OData数据源在C#中实现自定义分页，无需使用Entity Framework。

Question

我想知道是否有人能帮助我，我一直在试图弄清楚如何在OData feed (v4) Web API 2中实现自定义分页以供power bi feed使用，但一直没有成功。

数据是从数据库派生的，使用联接来组合5个表，这使得它不适用于Entity Framework，除了使用Entity Framework非常慢(一个控制器的45k条记录)。

我尝试过许多不同的方法，从设置记录的总数以欺骗框架并填充带有空列表成员的分页结果，到下面更基本的示例。然而，我仍然无法正确地获取客户端(Power BI)接受分页结果，而不返回控制器中极大数量的记录。请参见简化的查询和代码，任何帮助都将非常欢迎，因为似乎没有清晰的示例可以在不使用Entity Framework的情况下完成此操作。

以下代码可以工作，但我反复遇到同样的问题，即框架在返回之后对列表进行分页，而不管我在此之前做了什么

T-SQL存储过程：

CREATE PROCEDURE [dbo].[GetOrders] @CompanyID int,
                                   @Skip INT,
                                   @Take INT
AS

BEGIN 

SET NOCOUNT ON;

SELECT *
FROM Orders 
WHERE CompanyID = @CompanyID
ORDER BY t.OrderID
OFFSET @Skip ROWS FETCH NEXT @Take  ROWS ONLY

END

指向调用上述查询的存储库的控制器

[EnableQuery]
public async Task<PageResult<Order>> GetOrders(ODataQueryOptions<Order> queryOptions)
{
    int CompanyID = User.Identity.GetCompanyID().TryParseInt(0);

    ODataQuerySettings settings = new ODataQuerySettings()
    {
        PageSize = 100,
    };

    int OrderCount = _OrderRepo.GetOrderCount(CompanyID);
    int Skip = 0;

    if (queryOptions.Skip != null)
    {
        Skip =  queryOptions.Skip.Value;
    }

    IEnumerable<Order> results = await _OrderRepo.GetAll(CompanyID, Skip, 100);

    IQueryable result = queryOptions.ApplyTo(results.AsQueryable(), settings);

    Uri uri = Request.ODataProperties().NextLink;
    Request.ODataProperties().TotalCount =  OrderCount;

    PageResult<Order> response = new PageResult<Order>(
    result as IEnumerable<Order>,
    uri, Request.ODataProperties().TotalCount);

    return response;
}

此处的分页由框架在return response;之后完成。

- Jimbo Jones

---

您为什么不使用 OFFSET 和 FETCH 来编写 SQL 查询呢？ - Mark C.

@MarkC。没有特别的原因，我也可以使用OFFSET和FETCH。 - Jimbo Jones

Answer 1

在不了解您的完整需求的情况下，我假设这些数据的最终目的地将是 Power Bi 报告。因此，我建议您完全跳过特殊的分页代码，让 Power Bi Desktop 直接连接 SQL Server 来访问表格。

Power Bi Desktop 将尽其所能为您重新创建关系。如果您的原始表格和 ID 命名方式比较直观，Power Bi Desktop 可以相当不错地为您重新创建关系。您可以试一试。

导入完成后，您可以在 点击“关系视图” 时检查关系。如果关系有误，双击即可删除或编辑。

如果您担心每次运行报告都会影响数据库的性能，那么 Power Bi 的默认模式是导入数据的副本。如果数据小于 1GB，则建议使用此模式。

对于更大的数据集，可以尝试使用DirectQuery选项。 DirectQuery是为了加载更大的数据集而开发的，并且有其限制（单个源数据库，无法处理过于复杂的查询，数据库性能以及某些可视化功能受到限制）。

- Taterhead

---

出于安全考虑，我不能允许Power Bi直接连接到数据库，而且这不是单个客户端数据库。 - Jimbo Jones

愚蠢的问题：您的Power Bi报告是否需要浏览您的数据集？如果不需要，且您的数据集小于1GB，则我仍建议您使用导入选项将其导入到ODATA源中，而无需分页。 - Taterhead

数据集太大，无法通过web api2 odata分页默认方式加载，该方式是一次获取所有数据然后进行分页，除了返回请求的数量外。当处理许多大型表时，这种方式非常浪费。特别是在同时获取超过一个表格时。 - Jimbo Jones

你尝试过在Power BI中使用参数和自定义函数来调用这些参数吗？https://www.youtube.com/watch?v=iiNDq2VrZPY - nothingman

Answer 2

我最终找到的解决方案是通过扩展EnableQueryAttribute来中断框架（假设您关闭过滤等并设置了最大页面大小）。在分页查询中，您需要将页面向上移动一个以触发内部机制，这个解决方案只是一个解决方法。关键是在“ApplyTo”之前将Take设置为0。

IEnumerable<Order> results = await _OrderRepo.GetAll(CompanyID, Skip, Take + 1);

分页属性

public sealed class PagingAttribute : EnableQueryAttribute
{
    public override IQueryable ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)
    {
        var result = default(IQueryable);
        var originalRequest = queryOptions.Request;

        var skip = queryOptions.Skip == null ? 0 : queryOptions.Skip.Value;
        var take = queryOptions.Top == null ? PageSize : queryOptions.Top.Value;

        queryOptions = ODataQueryOptionsUtilities.Transform(queryOptions, new ODataQueryOptionsUtilitiesTransformSettings { Skip = (map, option) => default(int?) });

        if (queryOptions.Request.ODataProperties().TotalCount != null)
            originalRequest.ODataProperties().TotalCount = originalRequest.GetInlineCount();

        result = queryOptions.ApplyTo(queryable);

        if (skip + take <= originalRequest.ODataProperties().TotalCount)
            originalRequest.ODataProperties().NextLink = NextPageLink.GetNextNewPageLink(originalRequest, (skip + take));

        return result;
    }
}

我在控制器中设置了以下内容

        originalRequest.ODataProperties().TotalCount = Query.Item1; // Total size of all records to be returned
        originalRequest.SetInlineCount(Query.Item1);

- Jimbo Jones