We are creating a view on our SQL Server 2005 database as follows for a number of reasons that I am not at liberty to discuss:
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 Entity Framework is supposed to build an entity based on this query, which it does, but it does so with the following error:
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.
It also determines that the CompletedDateTime field will serve as the primary key for this object.
To create the model, EdmGen is used. Is there a method to prevent any field in this view from being used as a primary key by the entity framework?
This is the answer since we had the same issue:
Utilize ISNULL to have entity framework use a column as a primary key.
Use NULLIF to prevent entity framework from using a column as a primary key.
Applying this is made simple by enclosing your view's choose statement with another select.
SELECT ISNULL(MyPrimaryID,-999) MyPrimaryID, NULLIF(AnotherProperty,'') AnotherProperty FROM ( ... ) AS temp
With the help of the designer, I was able to fix this.
To utilize the workarounds for ISNULL, NULLIF, or COALESCE, I did not need to alter my view. The warnings will return if you update your model from the database, but they will disappear if you close and reopen Visual Studio. Your designer modifications will remain in place and won't be impacted by the refresh.