Entity Framework: Adding DataTable with no Primary Key to Entity Model.

Exception: The table/view '[TableName]' 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.

Now We will see how to address above Issue.

Background: This issue will arise when u try to add a Database table with no  Primary Key to Entity Model  (.edmx) file.

Reason: Entity model expects every table to have a Primary key to bring uniqueness to every record in an entity thus making operations faster.

Immediately we will search Google, and we will find two options.
1. Change the  Table structure and add a Primary Column. Update the Model.
2. Modify the .EDMX file in XML Editor and try adding a New Column under <Key> tag for this specific table.

Even thought you are dealing with Exiting Database or Exiting table, if Possible i advice you to follow Option #1.
But, in case you cannot change the structure of table now, we have no other option but #2.

Trust me Option #2 will not work. Let me show you why?

Step 1:
I have a Database, which was already been modeled and was being under use. Now to test this scenario, i have added a new table which does not have any primary Column. Table Name is "NonPrimaryKeyTable".

Step 2:Right click on entity model file (.edmx) and update the model from Database. Select the newly created table in the wizard and click finish. The Entity for "NonPrimaryKeyTable"  will not be created but will show an Message in error section. saying "The table/view 'EntityMVCDatabase.dbo.NonPrimaryKeyTable' 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."

Step 3: Lets follow the option #2. In many sites the solution is half implemented or gave a hint about it.
But lets see how to implement it in full stretch.
Right click on .edmx file and Openwith... XML Editor. The model file will be opened in an XML File format.
Now, if you observe the new table "NonPrimaryKeyTable" will be in commented mode.

Step 4: Before going to edit .edmx file, lets learn a  few concepts.
There are 3 parts of a edmx file.

SSDL - Store Schema Definition Language - Defines Storage Model
CSDL - Conceptual Schema Definition Language - Defines Conceptual Model
MSL - Mapping Specification Language - Defines Mapping between above two models

Step 5: Now start editing each section.
Under SSDL
Define EntitySet for the new table. Un-comment the EntityType definition and modify to include a NonNullable column in defition.

<entityset entitytype="EntityMVCDatabaseModel.Store.NonPrimaryKeyTable" 

     name="NonPrimaryKeyTable" schema="dbo" store:type="Tables"></entityset>
     
     <EntityType Name="NonPrimaryKeyTable">
     <Key>
     <PropertyRef Name="NonNullableColumn"/>
     </Key>
     <Property Name="NonNullableColumn" Type="int" Nullable="false"/>
     <Property Name="Column1" Type="nchar" MaxLength="10" />
     <Property Name="Column2" Type="nchar" MaxLength="10" />
</EntityType>

Under CSDL
Define EntitySet and  EntityType for the new table.

<EntitySet Name="NonPrimaryKeyTables" EntityType="EntityMVCDatabaseModel.NonPrimaryKeyTable"/>

<EntityType Name="NonPrimaryKeyTable">
  <Key>
     <PropertyRef Name="NonNullableColumn"/>
  </Key>
  <Property Name="NonNullableColumn" Type="Int32" Nullable="false"/>
  <Property Name="Column1" Type="String" MaxLength="10" />
  <Property Name="Column2" Type="String" MaxLength="10" />
</EntityType>

Under MSL

<EntitySetMapping Name="NonPrimaryKeyTables">
  <EntityTypeMapping TypeName="EntityMVCDatabaseModel.NonPrimaryKeyTable">
   <MappingFragment StoreEntitySet="NonPrimaryKeyTable">
   <ScalarProperty Name="NonNullableColumn" ColumnName="NonNullableColumn"/>
   <ScalarProperty Name="Column1" ColumnName="Column1" />
   <ScalarProperty Name="Column2" ColumnName="Column2" />
   </MappingFragment>
  </EntityTypeMapping>
</EntitySetMapping>

Save the xml and close it and Open .edmx file again.
Here we have the "NonPrimarykeyTable" appeared in entity model file.

Step 6: I wrote a code to save a record to to that table.

public ActionResult SaveDatatoNonPrimaryKeyTable()
        {

            EntityMVCDatabaseEntities dbcontext = new EntityMVCDatabaseEntities();
            EntityConnector.NonPrimaryKeyTable NoPKTableInstance = new NonPrimaryKeyTable();
            NoPKTableInstance.Column1 = "Data1";
            NoPKTableInstance.Column2 = "Data2";
            dbcontext.NonPrimaryKeyTables.AddObject(NoPKTableInstance);
            dbcontext.SaveChanges();
            return Json(true);
        }

When we execute, here we got the exception saying  "Invalid column name NonNullableColumn"

Reason: Though we managed to create a Unique Column in Entity Model, when u try to update the data, the mapping column will not be found in Database table.
So, Adding a Primary column to definition in entity model is of No use.

No Worries, we have an Option #3.

Step 7: Now Lets do the following modifications instead of step 5.

Instead of creating a new Primary Column to Exiting table, i will make a composite key by involving all the existing columns.
Below are the changes to do.

Under SSDL

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

        <EntityType Name="NonPrimaryKeyTable">
          <Key>
            <PropertyRef Name="Column1" />
            <PropertyRef Name="Column2" />
          </Key>
          <Property Name="Column1" Type="nchar" MaxLength="10" Nullable="false"/>
          <Property Name="Column2" Type="nchar" MaxLength="10" Nullable="false" />
        </EntityType>

Under CSDL

<EntitySet Name="NonPrimaryKeyTables" EntityType="EntityMVCDatabaseModel.NonPrimaryKeyTable"/>

        <EntityType Name="NonPrimaryKeyTable">
          <Key>
            <PropertyRef Name="Column1" />
            <PropertyRef Name="Column2" />
          </Key>
          <Property Name="Column1" Type="String" MaxLength="10" Nullable="false"/>
          <Property Name="Column2" Type="String" MaxLength="10" Nullable="false"/>
        </EntityType>

Under MSL

<EntitySetMapping Name="NonPrimaryKeyTables">
            <EntityTypeMapping TypeName="EntityMVCDatabaseModel.NonPrimaryKeyTable">
              <MappingFragment StoreEntitySet="NonPrimaryKeyTable">
                <ScalarProperty Name="Column1" ColumnName="Column1"/>
                <ScalarProperty Name="Column2" ColumnName="Column2"/>
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>

Step 8: Now executing the same Save functionality, and here is the result.
Code got executed successfully, and the break point navigated to return statement without any exception.

Database Snapshot:

Our objective achieved. A Data table without a primary key was transformed into a Entity and used in Entity Framework model without disturbing the Existing table structure.

Is it helpful for you? Kindly let me know your comments / Questions.