实体框架:不包含主键的视图排除 [英] Entity Framework: View exclusion without primary key

问题描述

我正在使用SQL Server，在其中设计了一个视图，用于对两个表的结果求和，并且我希望输出为包含结果的单个表.我的查询简化了:

I am using SQL Server where I have designed a view to sum the results of two tables and I want the output to be a single table with the results. My query simplified is something like:

SELECT SUM(col1), col2, col3
FROM Table1
GROUP BY col2, col3

这给了我想要的数据，但是在更新我的EDM时，该视图被排除在外，因为无法推断出主键".

This gives me the data I want, but when updating my EDM the view is excluded because "a primary key cannot be inferred".

经过一些研究，我修改了查询以欺骗id列，如下所示:

With a little research I modified the query to spoof an id column to as follows:

SELECT ROW_NUMBER() OVER (ORDER BY col2) AS 'ID', SUM(col1), col2, col3
FROM Table1
GROUP BY col2, col3

这种查询为我提供了一组越来越多的ID.但是，当我尝试更新模型时，它仍会排除我的视图，因为它无法推断出主键.我们如何使用汇总记录的视图并将它们与Linq-to-Entities连接起来?

This kind of query gives me a nice increasing set of ids. However, when I attempt to update my model it still excludes my view because it cannot infer a primary key. How can we use views that aggregate records and connect them with Linq-to-Entities?

推荐答案

如注释中所述，您可以尝试将MAX(id) as id添加到视图中.根据您的反馈，它将变为:

As already discussed in the comments you can try adding MAX(id) as id to the view. Based on your feedback this would become:

SELECT ISNULL(MAX(id), 0) as ID, 
       SUM(col1), 
       col2, 
       col3 
FROM   Table1 
GROUP BY col2, col3

另一个选择是尝试在视图上创建索引:

Another option is to try creating an index on the view:

CREATE UNIQUE CLUSTERED INDEX idx_view1 ON dbo.View1(id)

这篇关于实体框架:不包含主键的视图排除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！