Entity Framework and SQL Server View

 

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.

Example: 

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

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.
  • In the Properties window (press F4 if needed to see it), change the "Entity Key" drop-down to 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.

    • 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.

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

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

     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: 

    Group by CustomerId

    you use 

    Group by IsNull(CustomerId, -1)

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

    If

    链接地址: http://www.djcxy.com/p/29150.html

    上一篇: 实体框架和MVC模型

    下一篇: 实体框架和SQL Server视图