0

I am trying to add a view to an entity data model but I get the error below. The view is a group by with a count. I don’t understand this because a view does not have a primary key by it’s nature.

I modified the original post because I figured out how to add a key to the view. But I still have the same problem.

warning 6013: The table/view 'fmcsa.dbo.vieFMCSADocumentCount' does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.

Here is the View

CREATE VIEW [dbo].[vieFMCSADocumentCount] with SCHEMABINDING
AS
SELECT     COUNT_BIG(*) AS CountOfDocs, ROLE_ID, OWNER_ID
FROM         dbo.FMCSA_DOCUMENT
GROUP BY ROLE_ID, OWNER_ID

then I can add a key

CREATE UNIQUE CLUSTERED INDEX [MainIndex] ON [dbo].[vieFMCSADocumentCount] 
(
    [OWNER_ID] ASC,
    [ROLE_ID] ASC
)

Still not working.

Stephen Turner
  • 7,125
  • 4
  • 51
  • 68
Bob Avallone
  • 979
  • 7
  • 20
  • 36
  • Check out [this question](http://stackoverflow.com/questions/745341/can-ms-sql-views-have-primary-and-foriegn-keys) and see if it helps – nickmoriarty Feb 18 '11 at 17:07
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Feb 18 '11 at 17:20
  • possible duplicate of [Adding a logical key to a View in SQL Server Manager](http://stackoverflow.com/questions/1368964/adding-a-logical-key-to-a-view-in-sql-server-manager) – Craig Stuntz Feb 22 '11 at 17:19
  • Sorry none of these hints helped me. I don't know how to add a logical key to a View. Can one of you be more specific? Also reply as an answer instead of a comment so I can accpet the answer if it helps me. – Bob Avallone Feb 23 '11 at 22:32

1 Answers1

1

You didn't specify, but I'm assuming you're using EF4. I've come across this before--you either want to define a key manually or edit recreate your view WITH SCHEMABINDING and reimport.

Schema binding effectively tells SQL to track dependencies for your view. It's both a blessing and a curse (try adding a column to FMCSA_DOCUMENT once this view has schema binding), so you might want to read up on the effects.

CREATE VIEW [dbo].[vieFMCSADocumentCount] WITH SCHEMABINDING
AS 
  SELECT COUNT(ID) AS CountOfDocs, ROLE_ID, OWNER_ID 
  FROM  dbo.FMCSA_DOCUMENT GROUP BY ROLE_ID, OWNER_ID

Alternately, in the EF Model Browser Go to the Entity Types folder, find your view (right click and choose Show in Designer). Then on the view, highlight the column(s) that comprise your primary key and right click and choose "Entity Key"

EBarr
  • 11,826
  • 7
  • 63
  • 85
  • I thought I had this solved but I didn't, so I modified my original post. – Bob Avallone Feb 24 '11 at 15:34
  • @BoB Avallone -- after altering your view to add ScheamBinding ...did you delete and re-import the view into your EF model? I've found the auto-synch EF tries in inaccurate/incomplete in many scenarios, particularly with key definitions. – EBarr Feb 24 '11 at 16:02