为什么我不能在 Entity Framework 4.0 中使用包含 Union 的视图？
我有一个看起来与此类似的视图：
SELECT Id, Name
FROM Users
UNION ALL
SELECT NULL as [Id], NULL as [Name]
当我尝试映射到实体框架中的此视图时，它失败了。我没有收到错误，但该视图在我的数据存储中不存在。这是为什么呢？有办法解决吗？
I have a View which looks similar to this:
SELECT Id, Name
FROM Users
UNION ALL
SELECT NULL as [Id], NULL as [Name]
When I try to map to this view in Entity Framework, it just fails. I don't get an error, but the view does not exist in my data store. Why is this? Is there a way around it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我知道这是一个已标记为已回答的旧问题，但我想发布编辑 edmx 的替代方案。我通过大量的 Google 搜索和费了好几个小时的时间尝试不同的选项才学会了这一点......
对于视图，EF 尝试通过识别不可为空的列来推断主键
和非二进制（请参阅在没有密钥时创建实体密钥推断）。
使用用于展平相关数据以进行查找的视图，这可能会导致许多列（或错误的列）被推断为键。
对于具有 UNION 的视图，它可能会导致相反的问题，因为可能没有真正的标识列可以安全地包含为键。
要强制 EF 将列标识为键，您可以使用 ISNULL() 来确保该值不可为空：
ISNULL(column_name, replacement_value)要强制 EF 不将列标记为键，您可以使用 NULLIF() 使其可为空：
NULLIF(column_name, value_not_equal_to_parameter_1)如果您需要确保返回一个值，但又不这样做想要将其标记为键，我相信
COALESCE(column_name, replacement_value)将完成 ISNULL 的工作，而无需 EF 将列标记为键如果确实没有可用的可用列< /strong> 作为主键（如在 UNION 视图中），您可以通过在 SELECT 语句中结合使用 ISNULL() 和 ROW_NUMBER() 来伪造不可为空的 PK：
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY sort_criteria), 0) as 'ALIASNAME'作为替代方案，edmx 绝对可以按照 Marcos Prieto 的建议直接编辑，但您可能会面临这些更改被覆盖下一个的风险运行“从数据库更新模型”时。
希望这对将来遇到这种情况的人有所帮助！
I know this is an older question already marked as answered, but I wanted to post an alternative to editing the edmx. I learned this one the hard way, after tons of Google searches and pulling my hair out for hours, trying different options...
For a view, EF attempts to to infer a primary key by identifying columns that are non-nullable
and non-binary (see Create an Entity Key When No Key Is Inferred).
With a view used to flatten related data for lookup purposes, this can result in many columns (or the wrong ones) being inferred as keys.
For a view with a UNION, it can cause the opposite problem, because there may be no true identity column that can be safely included as a key.
To force EF to identify columns as a key, you can use ISNULL() to ensure the value is non-nullable:
ISNULL(column_name, replacement_value)To force EF to not mark a column as a key, you can use NULLIF() to make it nullable:
NULLIF(column_name, value_not_equal_to_parameter_1)If you need to ensure a value is returned, but don't want to have it marked as a key, I believe
COALESCE(column_name, replacement_value)will do the job of ISNULL without EF marking the column as a keyIf there is truly no viable column available as a primary key (as in a UNION view), you can fake a non-nullable PK through the use of ISNULL() combined with ROW_NUMBER() in your SELECT statement:
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY sort_criteria), 0) as 'ALIASNAME'As an alternative, the edmx can absolutely be edited directly as Marcos Prieto suggested, but you run the risk of having those changes overwritten the next time you run "Update Model from Database".
Hope this helps anyone who encounters this in the future!
这是因为 Visual Studio 无法推断视图的主键。
您可以通过使用 XML 编辑器打开 edmx 文件并查看 SSDL 部分来查看其中的错误消息。
以下是我的模型产生的错误消息（我在数据库中创建了一些像您的视图一样的视图只是为了模拟）：
EF 4 中不支持 Union 是不正确的。
但我认为问题在于 Visual Studio 将您的视图视为奇怪的视图。
您可以通过创建另一个视图并比较它们来进行一些实验（使用模型设计器中的数据库菜单中的更新模型）。
您可以手动修改模型（手动键入 edmx 文件）来定义主键来解决此问题。
It is because Visual Studio cannot infers the Primary Key of your View.
You can see the error message within edmx file by open it with XML editor and see the SSDL section.
Here is error message that results from my Model(which I created some View like yours within my Database just to emulate) :
It is not true that Union is not supported in EF 4.
But I think the problem is that Visual Studio saw your view as the odd View.
You can doing some experiment by create another View and compares them (using update model from database menu within model designer).
And you can modify the Model by hand (manual typing the edmx file) to define the Primary Key to resolve this.
我有一个完美的观点。我修改了它（更改了两个表的联合视图），从数据库更新了模型，出现了这个问题。
我通过 3 个步骤修复了该问题：
取消注释视图的 EntityType XML (edmx:StorageModels > Schema) 代码并添加密钥：
确保 EF 没有删除 edmx:StorageModels > 中的视图。架构> EntityContainer（如果您有代码存储库，请从那里复制代码）：
I had a view that worked perfectly. I modified it (changed the view on a union of 2 tables), updated the model from database and this problem appeared.
I fixed it in 3 steps:
Uncomment the view's EntityType XML (edmx:StorageModels > Schema) code and add the Key:
Be sure that EF didn't erased the view in edmx:StorageModels > Schema > EntityContainer (if you have code repository, copy the code from there):
我知道这是一个老问题，但我最近遇到了这个问题&在尝试执行上述方法之后，我只是创建了另一个从联合视图和联合视图中选择的视图。我能够通过更新我的实体模型来映射新视图。
I know this is an old question but i faced this issue recently & after trying to do the above mentioned methods i simply created another view that selects from the Union view & i was able to map the new view by updating my entity model.