Should I neccessarily have a PK on the source table to create an ADO.NET Entity Data Model?

Question

I'm trying to "Entitify" some external table (which I don't administer) in order to use it within a MVC application and in principle I'm not being terribly successful with the attempt (VS2008 output):

Error List [0 Errors] [0 Warnings] [1 Message]

Description

The table/view 'DATABASE.dbo.table' 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.

File

C:\Documents and Settings\%USERNAME%\My Documents\Visual Studio 2008\Projects\MVC_Entity_Test\MVC_Entity_Test\Models\EmployeesDataModel.edmx

Line

0

Column

1

Project

MVC_Entity_Test

Output

Show output from: Entity Data Model

Added the connection string to Web.Config.
Successfully registered the assembly 'System.Data.Entity, Version=3.5.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' in Web.Config.
The model was generated with warnings or errors.
Please see the Error List for more details. These issues must be fixed before
running your application.
Loading metadata from database took 00:00:06.2809306.
Generating model took 00:00:03.0359078.
Writing out the EDMX file took 00:00:00.0230083.
Added the connection string to Web.Config.
Successfully registered the assembly 'System.Data.Entity, Version=3.5.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' in Web.Config.
The model was generated with warnings or errors.
Please see the Error List for more details. These issues must be fixed before running
your application.
Loading metadata from database took 00:00:12.3208290.
Generating model took 00:00:03.6914563.
Writing out the EDMX file took 00:00:02.1670689.

So my question would be, is it absolutely mandatory to declare a PK on the source table to correctly map it as an ASP.NET Entity?

Any advice will be greatly appreciated.

Note: I wouldn't like this thread to become a dissertation about great, standard-driven, academically-backed database modeling, thing is, I have to deal with this external table that comes from a view somewhere, that comes from a table somewhere, so on so so forth, so I don't really have any control over it. The point being that I just want to get my side of the thing done.

Answer 1

whatever your design, please make a primary key, you'll thanks me in the long run!!!

Answer 2

Well, I would generally recommend it as a best practice to put a primary key on any table - except in very rare cases like a table used for bulkloading data etc.

After all, only with the primary key can you uniquely identify and keep apart rows in the table (and thus entities or object instances in your domain model).

One SQL guru even said: If it doesn't have a primary key, it's not a table! :-)

Marc

Answer 3

The Entity Framework is a mapping framework, so it needs a way to uniquely map every row to an object. This requires some form of a unique identifier, which is used to generate SQL DML statements so the row can be modified.

In your situation if there is a combination of columns do not contain NULLs that will uniquely identify a row, you can manually add the necessary information to the edmx file. This MSDN page will describe the basics of manually adding an object to an edmx, http://msdn.microsoft.com/en-us/library/bb399785.aspx. Remember it is just an XML file that you can edit.

Answer 4

This is a fun one that I don't understand. Why doesn't this map correctly and I end up getting the same error as above?

I'm trying to create two new entities in EF from data in a linked server. I created two views that look similar to this, both tied to a linked server table:

SELECT ORGUNIT_ID FROM 
DWSQL.DWH.dbo.HRXV_ORGUNIT_TSD
WHERE (ORGUNIT_STATUS_CODE = 'A') 
AND (ORGUNIT_TYPE_CODE = 'DEPT')

If I set these two views up, EF doesn't map them and files errors in the EDMX about not being able to identify a primary key. If I change the T-SQL to

SELECT '' AS ORGUNIT_ID

EF will map that - until I change the query back to the one before. Then it says the entity's not mapped.

How do I map these linked server tables in EF? I'm an EF newbie and haven't seen how to do this.