时间戳

首页 > 解决方案 > 使用 Excel PowerQuery 查询时有效的 OData 失败

excel - 使用 Excel PowerQuery 查询时有效的 OData 失败

问题描述

我正在尝试在 ASP Core 中构建 OData 服务以在 Excel 中使用，但在尝试使用它时遇到了问题。这很奇怪，因为其他类似的桌子似乎消费得很好。

这是响应（使用 PostMan 获得）：

{
    "@odata.context": "https://localhost:44349/odata/$metadata#ScenarioYearValues",
    "value": [
        {
            "ScenarioYearValueId": 46
        },
        {
            "ScenarioYearValueId": 47
        },
        {
            "ScenarioYearValueId": 48
        },
        {
            "ScenarioYearValueId": 49
        },
        {
            "ScenarioYearValueId": 50
        },
        {
            "ScenarioYearValueId": 51
        },
        {
            "ScenarioYearValueId": 52
        },
        {
            "ScenarioYearValueId": 161
        },
        {
            "ScenarioYearValueId": 162
        },
        {
            "ScenarioYearValueId": 178
        }
    ]
}

这是元数据：

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
        <Schema Namespace="Backend.ImpPlanner.Models" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="ScenarioYearValue">
                <Key>
                    <PropertyRef Name="ScenarioYearValueId" />
                </Key>
                <Property Name="ScenarioYearValueId" Type="Edm.Int32" Nullable="false" />
            </EntityType>
        </Schema>
        <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityContainer Name="Container">
                <EntitySet Name="ScenarioYearValues" EntityType="Backend.ImpPlanner.Models.ScenarioYearValue" />
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

通过本地运行的 OData 验证器 ( https://archive.codeplex.com/?p=odatavalidator )，它告诉我它是有效的 OData，但 Excel 返回：

详细信息：“我们无法将类型名称 'ScenarioYearValues' 解析为 EdmType”

谁能发现我哪里出错了？

更新：

下面是一个不同的控制器的例子：

{
    "@odata.context": "https://localhost:44349/odata/$metadata#ScenarioRows",
    "value": [
        {
            "ScenarioRowId": 317,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 29,
            "SchoolId": 22,
            "DepartmentId": 2692,
            "FundId": null,
            "Description": "Budget setting 20/21  ",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": null
        },
        {
            "ScenarioRowId": 318,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 18,
            "SchoolId": 22,
            "DepartmentId": 361,
            "FundId": null,
            "Description": "B/fwd from PSB 2",
            "Enabled": false,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": ""
        },
        {
            "ScenarioRowId": 319,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 28,
            "SchoolId": 22,
            "DepartmentId": 369,
            "FundId": null,
            "Description": "  Jan 32 Oct 25 (7 year 6 leavers)",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": null
        },
        {
            "ScenarioRowId": 320,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 30,
            "SchoolId": 22,
            "DepartmentId": 373,
            "FundId": null,
            "Description": "B/fwd from PSB 4",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": ""
        },
        {
            "ScenarioRowId": 321,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 20,
            "SchoolId": 22,
            "DepartmentId": 2819,
            "FundId": null,
            "Description": "Budget setting 20/21  ",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": null
        },
        {
            "ScenarioRowId": 322,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 43,
            "SchoolId": 22,
            "DepartmentId": 374,
            "FundId": null,
            "Description": "Lettings income 20/21 budget",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": null
        },
        {
            "ScenarioRowId": 323,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 61,
            "SchoolId": 22,
            "DepartmentId": 375,
            "FundId": null,
            "Description": "B/fwd from PSB 7",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": null
        },
        {
            "ScenarioRowId": 324,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 36,
            "SchoolId": 22,
            "DepartmentId": 368,
            "FundId": null,
            "Description": "B/fwd from PSB 8",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": null
        },
        {
            "ScenarioRowId": 325,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 269,
            "SchoolId": 22,
            "DepartmentId": 712,
            "FundId": null,
            "Description": "B/fwd from PSB 9",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": ""
        },
        {
            "ScenarioRowId": 326,
            "ScenarioId": null,
            "ContractId": null,
            "FinanceCodeId": 29,
            "SchoolId": 28,
            "DepartmentId": 2692,
            "FundId": null,
            "Description": "B/fwd from PSB 10",
            "Enabled": true,
            "CalculatorId": 37,
            "MonthProfileId": 17,
            "CashFlowProfileId": null,
            "MatEditOnly": false,
            "Notes": ""
        }
    ]
}

元数据：

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
        <Schema Namespace="Backend.ImpPlanner.Models" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="ScenarioRow">
                <Key>
                    <PropertyRef Name="ScenarioRowId" />
                </Key>
                <Property Name="ScenarioRowId" Type="Edm.Int32" Nullable="false" />
                <Property Name="ScenarioId" Type="Edm.Int32" />
                <Property Name="ContractId" Type="Edm.Int32" />
                <Property Name="FinanceCodeId" Type="Edm.Int32" Nullable="false" />
                <Property Name="SchoolId" Type="Edm.Int32" Nullable="false" />
                <Property Name="DepartmentId" Type="Edm.Int32" Nullable="false" />
                <Property Name="FundId" Type="Edm.Int32" />
                <Property Name="Description" Type="Edm.String" Nullable="false" />
                <Property Name="Enabled" Type="Edm.Boolean" Nullable="false" />
                <Property Name="CalculatorId" Type="Edm.Int32" />
                <Property Name="MonthProfileId" Type="Edm.Int32" Nullable="false" />
                <Property Name="CashFlowProfileId" Type="Edm.Int32" />
                <Property Name="MatEditOnly" Type="Edm.Boolean" Nullable="false" />
                <Property Name="Notes" Type="Edm.String" MaxLength="5000" />
            </EntityType>
        </Schema>
        <Schema Namespace="Backend.ImpPlanner.ViewModels" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="ScenarioRowDetailModel" BaseType="Backend.ImpPlanner.Models.ScenarioRow">
                <Property Name="Editable" Type="Edm.Boolean" Nullable="false" />
            </EntityType>
        </Schema>
        <Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityContainer Name="Container">
                <EntitySet Name="ScenarioRows" EntityType="Backend.ImpPlanner.Models.ScenarioRow" />
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

标签： excelodatapowerquery

解决方案

经过数小时的相同“我们无法将类型名称'[EntityName]'解析为 EdmType”后，我发现 Excel 的 Power Query 似乎缓存了 OData 元数据。刷新它所需要做的就是单击功能区主页选项卡上的刷新预览按钮，然后错误消失。

推荐阅读