How to create a primary key on a column in SQL Server or how to import a view without PK in EDM designer?

Question

I have a linked server on my SQL server that links to the Active Directory. I created a view that selects relevant information about the users from AD. This view does not have a primary key. Now I want to build an OData service that will query the view from the browser and my application. I use .NET WCF Data services for that (.NET 4.0, VS2010). However, when I create an edmx file and I try to import the view, the view is not imported, because there is no PRIMARY KEY on it. I cannot create a primary key on a view (not possible on MS SQL Server), but I need it to be able to import the view in my Entity Data Model Designer. What can I do? How can I make it work?

Let me add that I cannot create an indexed view, because my view is not schema bound (the data comes from Active Directory).

Answer 1

It's really funny how you try to solve things for an hour or two, you decide to post on SO and solve your problem within minutes...

I found this: https://stackoverflow.com/a/2715299/989256

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

It worked just fine, but I am a little concerned about the performance. The view seems to load much slower...