Entity Framework and SQL Server View

Question

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

User · Accepted Answer

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

User · Answer

To get a view I had to only show one primary key column I created a second view that pointed to the first and used NULLIF to make the types nullable   This worked for me to make the EF think there was just a single primary key in the view   Not sure if this will help you though since I don t believe the EF will accept an entity with NO primary key

User · Answer

Looks like it is a known problem with EdmGen   http   social msdn microsoft com forums en-US adodotnetentityframework thread 12aaac4d-2be8-44f3-9448-d7c659585945

User · Answer

The current Entity Framework EDM generator will create a composite key from all non-nullable fields in your view   In order to gain control over this  you will need to modify the view and underlying table columns setting the columns to nullable when you do not want them to be part of the primary key   The opposite is also true  as I encountered  the EDM generated key was causing data-duplication issues  so I had to define a nullable column as non-nullable to force the composite key in the EDM to include that column

User · Answer

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

User · Answer

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

User · Answer

This method works well for me  I use ISNULL   for the primary key field  and COALESCE   if the field should not be the primary key  but should also have a non-nullable value  This example yields ID field with a non-nullable primary key  The other fields are not keys  and have  None  as their Nullable attribute   SELECT       ISNULL P ID  - 1  AS ID    COALESCE  P PurchaseAgent  U  User Nickname   AS PurchaseAgent    COALESCE  P PurchaseAuthority  0  AS PurchaseAuthority    COALESCE  P AgencyCode      AS AgencyCode    COALESCE  P UserID  U ID  AS UserID    COALESCE  P AssignPOs   false   AS AssignPOs    COALESCE  P AuthString      AS AuthString    COALESCE  P AssignVendors   false   AS AssignVendors  FROM Users AS U   INNER JOIN Users AS AU ON U Login   AU UserName   LEFT OUTER JOIN PurchaseAgents AS P ON U ID   P UserID   if you really don t have a primary key  you can spoof one by using ROW NUMBER to generate a pseudo-key that is ignored by your code  For example   SELECT ROW NUMBER   OVER ORDER BY A B  AS Id  A  B FROM SOMETABLE

User · Answer

If you do not want to mess with what should be the primary key  I recommend    Incorporate ROW NUMBER into your selection  Set it as primary key  Set all other columns members as non-primary in the model

User · Answer

Due to the above mentioned problems  I prefer table value functions    If you have this   CREATE VIEW  dbo   MyView  AS SELECT A  B FROM dbo Something   create this   CREATE FUNCTION MyFunction   RETURNS TABLE AS RETURN  SELECT   FROM  dbo   MyView     Then you simply import the function rather than the view

[entity-framework] Entity Framework and SQL Server View

Examples related to entity-framework

Examples related to sql-server-2005

Examples related to .net-3.5

Examples related to primary-key

Examples related to sql-view