实体框架和SQL Server视图。

For several reasons that I don't have the liberty to talk about, we are defining a view on our Sql Server 2005 database like so:

由于几个原因，我没有讨论的*，我们在Sql Server 2005数据库中定义了一个视图:

CREATE VIEW [dbo].[MeterProvingStatisticsPoint]
AS
SELECT
    CAST(0 AS BIGINT) AS 'RowNumber',
    CAST(0 AS BIGINT) AS 'ProverTicketId',
    CAST(0 AS INT) AS 'ReportNumber',
    GETDATE() AS 'CompletedDateTime',
    CAST(1.1 AS float) AS 'MeterFactor',
    CAST(1.1 AS float) AS 'Density',
    CAST(1.1 AS float) AS 'FlowRate',
    CAST(1.1 AS float) AS 'Average',
    CAST(1.1 AS float) AS 'StandardDeviation',
    CAST(1.1 AS float) AS 'MeanPlus2XStandardDeviation',
    CAST(1.1 AS float) AS 'MeanMinus2XStandardDeviation'
WHERE 0 = 1

The idea is that the Entity Framework will create an entity based on this query, which it does, but it generates it with an error that states the following:

其思想是，实体框架将基于此查询创建一个实体，它确实这样做，但是它会产生一个错误，并声明如下:

Warning 6002: The table/view 'Keystone_Local.dbo.MeterProvingStatisticsPoint' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

警告6002:表/视图的Keystone_Local.dbo。“MeterProvingStatisticsPoint”没有定义主键。关键字已经被推断出来，定义被创建为只读表/视图。

And it decides that the CompletedDateTime field will be this entity primary key.

它决定了CompletedDateTime字段将是这个实体主键。

We are using EdmGen to generate the model. Is there a way not to have the entity framework include any field of this view as a primary key?

我们使用EdmGen生成模型。是否有一种方法不让实体框架包含这个视图的任何字段作为主键?

9 个解决方案

#1

224

We had the same problem and this is the solution:

我们有同样的问题，这就是解决方案:

To force entity framework to use a column as a primary key, use ISNULL.

为了强制实体框架使用列作为主键，使用ISNULL。

To force entity framework not to use a column as a primary key, use NULLIF.

要强制实体框架不使用列作为主键，请使用NULLIF。

An easy way to apply this is to wrap the select statement of your view in another select.

应用此方法的一种简单方法是在另一个select中包装您的视图的select语句。

Example:

例子:

SELECT
  ISNULL(MyPrimaryID,-999) MyPrimaryID,
  NULLIF(AnotherProperty,'') AnotherProperty
  FROM ( ... ) AS temp

#2

I was able to resolve this using the designer.

我可以使用设计器解决这个问题。

Open the Model Browser.
打开模型浏览器。
Find the view in the diagram.
在图中找到视图。
Right click on the primary key, and make sure "Entity Key" is checked.
右键单击主键，确保选中“实体键”。
Multi-select all the non-primary keys. Use Ctrl or Shift keys.
多选择所有非主键。使用Ctrl或Shift键。
In the Properties window (press F4 if needed to see it), change the "Entity Key" drop-down to False.
在属性窗口中(如果需要查看，按F4)，将“实体键”下拉框改为False。
Save changes.
保存更改。
Close Visual Studio and re-open it. I am using Visual Studio 2013 with EF 6 and I had to do this to get the warnings to go away.
关闭Visual Studio并重新打开它。我使用的是Visual Studio 2013和EF 6，我必须这样做才能让警告消失。

I did not have to change my view to use the ISNULL, NULLIF, or COALESCE workarounds. If you update your model from the database, the warnings will re-appear, but will go away if you close and re-open VS. The changes you made in the designer will be preserved and not affected by the refresh.

我不必更改我的视图来使用ISNULL、NULLIF或合并工作区。如果您从数据库中更新您的模型，警告将会重新出现，但是如果您关闭和重新打开，而您在设计器中所做的更改将被保留，并且不会受到刷新的影响，那么将会消失。

#3

Agree with @Tillito, however in most cases it will foul SQL optimizer and it will not use right indexes.

同意@Tillito，但是在大多数情况下，它将会使SQL优化器变得糟糕，并且它不会使用正确的索引。

It may be obvious for somebody, but I burned hours solving performance issues using Tillito solution. Lets say you have the table:

这对某些人来说可能是显而易见的，但是我用Tillito解决了性能问题。假设你有桌子:

 Create table OrderDetail
    (  
       Id int primary key,
       CustomerId int references Customer(Id),
       Amount decimal default(0)
    );
 Create index ix_customer on OrderDetail(CustomerId);

and your view is something like this

你的观点是这样的。

 Create view CustomerView
    As
      Select 
          IsNull(CustomerId, -1) as CustomerId, -- forcing EF to use it as key
          Sum(Amount) as Amount
      From OrderDetail
      Group by CustomerId

Sql optimizer will not use index ix_customer and it will perform table scan on primary index, but if instead of:

Sql优化器不会使用index ix_customer，它将对主索引执行表扫描，但如果不是:

Group by CustomerId

you use

你使用

Group by IsNull(CustomerId, -1)

it will make MS SQL (at least 2008) include right index into plan.

它将使MS SQL(至少在2008年)包含正确的索引到计划中。

如果

#4

This method works well for me. I use ISNULL() for the primary key field, and COALESCE() if the field should not be the primary key, but should also have a non-nullable value. This example yields ID field with a non-nullable primary key. The other fields are not keys, and have (None) as their Nullable attribute.

这种方法对我来说很有效。对于主键字段，我使用ISNULL()，如果字段不应该是主键，则使用ISNULL()，但也应该具有非空值。这个示例使用一个非空的主键来生成ID字段。其他字段不是键，并且有(None)作为它们的空属性。

SELECT      
ISNULL(P.ID, - 1) AS ID,  
COALESCE (P.PurchaseAgent, U.[User Nickname]) AS PurchaseAgent,  
COALESCE (P.PurchaseAuthority, 0) AS PurchaseAuthority,  
COALESCE (P.AgencyCode, '') AS AgencyCode,  
COALESCE (P.UserID, U.ID) AS UserID,  
COALESCE (P.AssignPOs, 'false') AS AssignPOs,  
COALESCE (P.AuthString, '') AS AuthString,  
COALESCE (P.AssignVendors, 'false') AS AssignVendors 
FROM Users AS U  
INNER JOIN Users AS AU ON U.Login = AU.UserName  
LEFT OUTER JOIN PurchaseAgents AS P ON U.ID = P.UserID

if you really don't have a primary key, you can spoof one by using ROW_NUMBER to generate a pseudo-key that is ignored by your code. For example:

如果您确实没有主键，那么可以使用ROW_NUMBER来进行欺骗，从而生成一个被代码忽略的伪键。例如:

SELECT
ROW_NUMBER() OVER(ORDER BY A,B) AS Id,
A, B
FROM SOMETABLE

#5

The current Entity Framework EDM generator will create a composite key from all non-nullable fields in your view. In order to gain control over this, you will need to modify the view and underlying table columns setting the columns to nullable when you do not want them to be part of the primary key. The opposite is also true, as I encountered, the EDM generated key was causing data-duplication issues, so I had to define a nullable column as non-nullable to force the composite key in the EDM to include that column.

当前的实体框架EDM生成器将在您的视图中创建一个来自所有非空字段的组合键。为了控制这一点，您需要修改视图和底层表列，在不希望它们成为主键的一部分时，将列设置为nullable。与我遇到的情况相反，EDM生成的密钥会导致数据复制问题，因此我必须定义一个可空列，以使EDM中的复合键能够包含该列。

#6

Looks like it is a known problem with EdmGen: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/12aaac4d-2be8-44f3-9448-d7c659585945/

看起来这是EdmGen的一个已知问题:http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/12aaac4d-2be8-44f3-9448-d7c659585945/。

#7

To get a view I had to only show one primary key column I created a second view that pointed to the first and used NULLIF to make the types nullable. This worked for me to make the EF think there was just a single primary key in the view.

为了获得一个视图，我必须只显示一个主键列，我创建了第二个视图，该视图指向第一个视图，并使用NULLIF来使类型为空。这对我来说很有效，让EF认为在视图中只有一个主键。

Not sure if this will help you though since I don't believe the EF will accept an entity with NO primary key.

不确定这是否会对您有所帮助，因为我不相信EF会接受没有主键的实体。

#8

I also recommend if you do not want to mess with what should be the primary key to incorporate ROW_NUMBER to your selection and set it as primary key and set all other columns/memebers as non-primary in the model.

我还建议，如果您不希望将ROW_NUMBER合并到您的选择中，并将其设置为主键，并将所有其他列/memebers设置为模型中的非主键，那么您就不需要这样做了。

#9

Due to the above mentioned problems, I prefer table value functions.

由于上述问题，我更喜欢表值函数。

If you have this:

如果你有这个:

CREATE VIEW [dbo].[MyView] AS SELECT A, B FROM dbo.Something

create this:

创建这个:

CREATE FUNCTION MyFunction() RETURNS TABLE AS RETURN (SELECT * FROM [dbo].[MyView])

Then you simply import the function rather than the view.

然后只需导入函数而不是视图。

#1

224