By Sergio Romero


2009-06-18 15:23:02 8 Comments

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?

9 comments

@Santhos 2014-02-25 13:22:19

If you do not want to mess with what should be the primary key, I recommend:

  1. Incorporate ROW_NUMBER into your selection
  2. Set it as primary key
  3. Set all other columns/members as non-primary in the model

@Ray 2014-08-03 20:05:28

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.

@ggderas 2016-09-19 21:17:04

How would you create associations among entities going with this approach? Is it possible?

@Casey Plummer 2014-01-18 19:04:28

I was able to resolve this using the designer.

  1. Open the Model Browser.
  2. Find the view in the diagram.
  3. Right click on the primary key, and make sure "Entity Key" is checked.
  4. Multi-select all the non-primary keys. Use Ctrl or Shift keys.
  5. In the Properties window (press F4 if needed to see it), change the "Entity Key" drop-down to False.
  6. Save changes.
  7. 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.

@Michael Logutov 2014-01-22 08:10:24

Confirmed. Have to restart VS2013 to make the warning go away.

@Obl Tobl 2014-08-06 07:10:56

"Have you tried turning it off and on again?" ;-) Thanks, works like a charm!

@Prakash Joshi 2015-09-04 12:35:49

New generation coder use this as solution :) :)

@ggderas 2016-09-27 21:02:11

When I'm creating views, they don't even get to be in the model diagram. They're commented in the xml file

@LuqJensen 2016-11-15 20:14:54

Simple and easy solution and doesnt seem as much of a nono hacky fix as manipulating the view! Thank you.

@Marc Levesque 2018-06-12 15:39:26

Confirmed VS2017 needs to be restarted also for the warning to go away.

@Ishwor Khanal 2018-07-09 09:45:13

It worked for me but I combined with ISNULL(MyPrimaryID,-1) as MyPrimaryID.

@SpazDude 2013-01-29 23:07:46

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

@ruffin 2016-03-22 21:55:11

Yeah, I ended up cheating with NEWID() as id, but it's the same idea. And there are legitimate use-cases -- if you've got a read-only view, for instance. Ugly, EF, ugly.

@Val Bakhtin 2012-05-04 19:24:57

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

@zimdanen 2012-05-04 19:32:04

This should be a comment on Tillito's answer, not an answer itself, as it does not provide a solution for the OP's question.

@jrcs3 2012-05-05 02:16:45

The guy has a rep of 1, he can't add a comment, yet.

@Contango 2014-03-25 15:35:02

@zimdanen There is no way you could fit all this information into a comment, it makes more sense to have it in a separate answer.

@zimdanen 2014-03-25 16:17:15

@Contango: This answer was edited six days after it was posted and I posted my comment. See the revision history.

@Tillito 2010-04-26 17:00:42

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

@MvcCmsJon 2010-11-13 19:41:51

I think this is the best to be hoped for. Bottom line it works.

@sabanito 2011-11-09 18:37:49

I've tried this and it doesn't work. Does EF designer parses the view definition or just infers the columns from the data results?

@Rabbi 2011-12-28 20:27:57

Thank You! It worked perfectly. @sabanito I think it parses the definition. that is why you need to specifically wrap the key properties in IsNull(). I have a view that does not return any nulls (and can not return any nulls) but because of the way the logic was written, EF could not determine that that was the case until I wrapped the keys in IsNull().

@sabanito 2012-01-02 16:01:15

It worked for me also. It uses the SchemaDefinition views.

@Bart 2012-04-11 18:00:15

The only issue i see here is that view might legitimately need to return an empty string ''. What I did, was simply cast the column back to its own data type. for example if AnotherProperty had a datatype of varchar(50) I would cast it as such 'CONVERT(VARCHAR(50), AnotherProperty) AS [AnotherProperty]'. this masked the nullability from EF and also allowed empty strings.

@dc2009 2012-07-10 15:27:35

yes this works for instance to make EF use the column as a primary key isnull(CONVERT(VARCHAR(50), newid()),'') AS [PK]

@dyslexicanaboko 2013-02-21 17:01:31

Aside from there just being an annoying message in the solution, is there any harm in not fixing this? I agree with your solution, but frankly I don't feel that I should have to do this - I think we can all agree this is a bug right?

@Yann39 2014-01-21 16:06:40

Is this still relevant ? I still get the warning after updating the Entity model, even using ISNULL in my views. Visual Studio 2012, .NET Framework 4.5, MSSQL Server 2012.

@user1546077 2014-01-21 16:30:23

It doesn't work for me either, i.e I still get the error, with EF 6, VS 2013, .NET 4.5, SQL Server 2012

@MCattle 2014-01-22 20:09:47

I suspect that the above solution will still produce the warning, but it may be a valid workaround for EF being too greedy and including a number of non-key columns as the Primary Key, which is the behaviour we're seeing (and having to correct manually).

@Gordon Thompson 2014-02-20 10:57:42

We've got this working now; you need to restart VS2013 for the error to go away though.. very annoying

@Schiavini 2014-03-10 09:14:20

Indeed, I've been searching for a solution for hours, and in the end I just had to restart VS2012. I'll edit the answer to include this.

@natenho 2014-06-03 21:05:19

@Tillito Why not to set all the view entity fields properties to "Entity Key = True" in the edmx designer instead of adding ISNULL/NULLIF to SQL code? It looks like a better performance workaround approach. I couldn't find drawbacks.

@Vincent Dagpin 2014-12-17 06:15:40

Hi, is this problem resolved in latest EF?

@ggderas 2016-09-19 21:21:43

Somebody here working with Oracle? Can't get the entities to work

@James Joyce 2017-09-11 03:19:54

The other option is to define a unique primary key using FluentAPI - using fields in the object; ` builder.Entity<DailyTick>() .ToTable("vwDailyTick") .HasKey("CurrencyPairId", "OpenTickId");`

@Nick Gotch 2010-02-22 23:02:49

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.

@Annagram 2009-10-07 18:54:56

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.

@David Oliván Ubieto 2015-02-22 08:45:40

We have the same problem with the inferred PK, the entity returns duplicated records and is completely annoying. If you execute Context.Entity.ToList() duplicates records, but if you execute the SQL Query generated by EF directly (obtained with LINQPad), no record duplication happens. Seems to be a problem mapping the database records to the entity objects (POCO) returned, as the PK is inferred using the explained logic (non nullable columns).

@RBarryYoung 2009-06-18 15:55:19

@Sergio Romero 2009-06-18 18:31:04

That makes sense. So, is there a way to define a column as not null or null in a view the way we are defining it?

@RBarryYoung 2009-06-18 20:07:51

Sorry, I am already beyond my level of expertise in Entity Framework. :-)

@live-love 2011-08-11 15:45:04

Any one knows when this issue is gonna be fixed? Annoying to have to workaround this when you have non null columns that are not primary keys.

Related Questions

Sponsored Content

38 Answered Questions

26 Answered Questions

[SOLVED] Check if table exists in SQL Server

2 Answered Questions

[SOLVED] Use Foreign Key on SQL View in Entity Framework

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

18 Answered Questions

[SOLVED] How do I view the SQL generated by the Entity Framework?

16 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

16 Answered Questions

[SOLVED] Entity Framework: table without primary key

5 Answered Questions

[SOLVED] It has a DefiningQuery but no InsertFunction element... err

1 Answered Questions

[SOLVED] Adding a logical key to a View in SQL Server Manager

Sponsored Content