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:
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.
And it decides that the CompletedDateTime field will be this entity primary key.
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:
To force entity framework to use a column as a primary key, use ISNULL.
To force entity framework not to use a column as a primary key, use NULLIF.
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
I was able to resolve this using the designer.
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.