实体框架和 SQL Server 视图 [英] Entity Framework and SQL Server View

问题描述

出于几个我无法自由谈论的原因，我们在我们的 Sql Server 2005 数据库上定义一个视图，如下所示:

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:

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:

警告 6002:表/视图Keystone_Local.dbo.MeterProvingStatisticsPoint"没有定义主键.已推断出键并将定义创建为只读表/视图.

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.

它决定 CompletedDateTime 字段将是该实体的主键.

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

我们使用 EdmGen 来生成模型.有没有办法不让实体框架包含这个视图的任何字段作为主键?

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?

推荐答案

我们遇到了同样的问题，这是解决方案:

We had the same problem and this is the solution:

要强制实体框架使用列作为主键，请使用 ISNULL.

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

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

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

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

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

示例:

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

这篇关于实体框架和 SQL Server 视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！