How do you add a primary key to a sql view? - Or alternate way to link views to linq-2-entities

Question

I'm adding a very simple view (or trying to) to my entities object model. The database is in sql server 2008. I'm on .net 3.5 (sp1) using C#.

The view has two fields: color and colorcount, a Varchar(50) and a count(*) respectively.

When I do Update Model from Database, and select the view to add, it runs (it updated tables, adding fields no problem) but does not add the view. No error, warnings, or messages are displayed.

When I open the .edmx file I see that it shows Warning 6013: No primary key defined.

The view is complex and I would rather not translate it to a LINQ query. How can I add a primary key so that Entities will support the view?

Is there a non-hack-around way to add a view like this to an EDMX?

Answer 1

I dont know if this will help you, but you can create a primary key using a multi-statement table-valued function.

I cant find any ref to a primary key for a view, but i know it can be done with a table function.

CREATE FUNCTION (Transact-SQL)

Answer 2

After you create the view using schemabinding you can add a primary key to it:

CREATE VIEW Colors WITH SCHEMABINDING
AS SELECT Color='yellow', ColorCount=100
GO
CREATE UNIQUE CLUSTERED INDEX PK_Colors ON Colors (Color)