View of Entity Framework and SQL Server

.net-3.5 entity-framework primary-key sql-server-2005 sql-view

Question

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?

1
132
12/6/2012 9:31:54 PM

Accepted Answer

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.

Example:

SELECT
  ISNULL(MyPrimaryID,-999) MyPrimaryID,
  NULLIF(AnotherProperty,'') AnotherProperty
  FROM ( ... ) AS temp
242
4/26/2010 5:00:42 PM

Popular Answer

With the help of the designer, I was able to fix this.

  1. Activate the Model Browser.
  2. In the diagram, locate the view.
  3. Make sure "Entity Key" is selected when you right-click the main key.
  4. Select all of the non-primary keys at once. Use the Shift or Ctrl keys.
  5. Make the "Entity Key" drop-down in the Properties box (press F4 if necessary to view it) False.
  6. Encrypt changes.
  7. Restart Visual Studio after closing it. This is what I had to do in order to get the warnings to go when using Visual Studio 2013 with EF 6.

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow