Entity Data Model - Adding a keyless table

Question

Visual studio is giving me a trail of errors that I have hit a dead end on. I am trying to add an existing table in a database to my data model. I understand that the table should have a key, but it doesn't and I can't fix that; it isn't my database to re-design.

When I first try to add the table, I get this error:

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

Ok, fine, I'll define the keys for it and uncomment. Here is the block I uncomment, after manually defining the keys (I also had to add the nullable=false part):

      <EntityType Name="SAVED_QUERY_CATEGORY">
    <Key>
      <PropertyRef Name="SQC_CAT_ID"/>
      <PropertyRef Name="SQC_USER_ID"/>
    </Key>
    <Property Name="SQC_CAT_ID" Type="int" Nullable="false" />
    <Property Name="SQC_USER_ID" Type="int" Nullable="false" />
    <Property Name="SQC_CAT_DSCR" Type="varchar" MaxLength="50" />
    <Property Name="SQC_SEQ_NO" Type="int" />
  </EntityType>

Nope, now the designer won't open. Go back into the file, and Intellisense shows this error:

Error 11002: Entity Type 'SAVED_QUERY_CATEGORY' has no entity set.

Ok... Uncommenting created a new error. After adding an entity set:

<EntitySet Name="SAVED_QUERY_CATEGORY" EntityType="IssueModel.Store.SAVED_QUERY_CATEGORY" store:Type="Tables" Schema="dbo" />

Yay! The designer opens! Still not there yet, because while it shows up under the store's Tables/Views folder, it has not Entity Type in the model. Can't call it from my code. I don't have any errors to work off though, so I tried creating an entity set mapping, but that results in the error "does not exist in MetadataWorkspace".

So that's what I have tried. How do I get this poorly designed table into my data model?

Answer 1

By modifying XML you have only added information about database table. Now you have to open toolbox and add entity to your model in the designer. Configure the entity to have properties as you need. Then open mapping details and map the new entity to your table.

Btw. once you modify the XML describing database manually you cannot use update from database anymore - VS designer will always delete your changes and you will have to do them again.