-->

29/04/2012

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.

21 comments:

  1. What if the two records have the same data. The new composite key will not be unique.

    Any idea how this type of scenario is handled in Entity Framework?

    ReplyDelete
    Replies
    1. If both the columns have the same data, then the context of the join through which the data for the two columns is generated is incorrect or that these two columns are not suitable to be selected as a composite key.

      Just my two cents!

      Delete
    2. I keep on getting this scenario, which people raise for sake of raising the point.
      Tell me in which general scenario, a table will be dumped with duplicate records with out any point of identity.

      Delete
  2. Thx for the detailed solution :)

    ReplyDelete
  3. Thanks for the solution, I was looking the same issue for the past two days...

    ReplyDelete
  4. Did anyone has answer to below question posted earlier?

    What if the two records have the same data. The new composite key will not be unique.

    Any idea how this type of scenario is handled in Entity Framework?

    ReplyDelete
    Replies
    1. I have been trying to understand this question! How we can design to add two identical records in a table. I dint encounter a design where someone can allow identical records saved in any table. Just in case, if there is such situation, we have to modify the table structure to add the primary key. I see no workarround for duplicate records.

      Delete
  5. I really appreciate your help!! Thank You Very Much Pratap!

    ReplyDelete
  6. Adding this to the view also works just fine.
    ISNULL(ROW_NUMBER() OVER( ORDER BY someColumn),0) AS id

    ReplyDelete
    Replies
    1. Thank you for such an elegant solution. Easiest and by far the most correct!

      Delete
    2. How can I achieve this using Oracle? Not working for me. I'm using NVL Oracle's function.

      Delete
  7. Hello,

    The solution you have provided works will for Insert case, but as you are making all the columns as Key columns then because of that this solution does not work in UPDATE case.

    OR you have some trick for update too?

    Thanks,
    Rohit

    ReplyDelete
    Replies
    1. In SQL Server Management Studio right click on the existing view and select Script View As to => Drop and Create To => Open In new query window.
      Add you ID to the query and execute.
      ISNULL(ROW_NUMBER() OVER( ORDER BY someColumn),0) AS id

      Delete
  8. Coming up two years later and this is still the best (and only) reference I can find for creating composite keys in EF!

    I am struggling however to find the context for the changes to the EDMX xml - I'm working with a legacy (oracle) database that has no primary keys in any of it's tables so I'm having to add all the XML structure by hand and I can't seem to figure out what names need to match and what names are being used where, even with reference to the MSDN schema documentation for the EDMX.

    The first gotcha for me was the SSDL EntitySet has to be inside an EntityContainer node

    The second gotcha was that the CSDL Property Type attribute has to come from the EDM types, not the CLR

    The third gotcha is that the EntitySetMapping for the MSL has to be inside an EntityContainerMapping node

    and fourthly (that I'm now stuck on) is that the MSL EntitySetMapping Name attribute has to match up with something, but I don't know what!

    Thanks so much for the assist, I wouldn't have gotten this far without you (and if anyone thinks that you can simply amend the schema of an existing product's DB without any difficulties at all, they need to come to the backwaters where the dark databases lurk and fight them for me).

    ReplyDelete
  9. Hi,
    Thanks a lot for the wonderful article.
    In my case I have three columns in the table without primary key:










    Above is the section in my SSDL for the table
    But I am getting error as bellow :
    "The Type numeric is not qualified with a namespace or alias"

    Can you please let me know what could be the possible issue ?

    ReplyDelete
  10. I don know why the SSDL din come up. I just have three columns two of them are numeric and the last one being int.
    I am trying to use the two numeric key as composite key.

    ReplyDelete
  11. BTW, I forgot to mention I am using EF 5

    ReplyDelete
  12. Worked perfectly for what I was trying to do, you are a life saver!

    ReplyDelete
  13. Very Nice Explanation ... Thank you so much :)

    ReplyDelete
  14. "This issue will arise when u try to ..."

    u?!

    ReplyDelete
  15. I can tell you that it works great with entity framework fluent api, just need to make a composite key there

    HasKey(
    m => new
    { m.Field1, m.Field2, ...})

    ReplyDelete