使用Excel PowerQuery查询时，有效的OData失败 [英] Valid OData failing when queryed using Excel PowerQuery
本文介绍了使用Excel PowerQuery查询时，有效的OData失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试在ASP Core中构建一个OData服务，以便在Excel中使用，但在尝试使用它时遇到了问题。这很奇怪，因为其他类似的表似乎都被消费得很好。
回复如下(邮递员获取)：
{
"@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返回：
Details: "We were unable to resolve the type name 'ScenarioYearValues' to an 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>
推荐答案
经过几个小时的努力，我们无法将类型名称‘[EntityName]’解析为EdmType&Quot；，我发现Excel的Power查询似乎缓存了OData元数据。您只需在功能区的主页选项卡上单击刷新预览按钮即可刷新，然后错误就会消失。
这篇关于使用Excel PowerQuery查询时，有效的OData失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
