Asp.NET | Adding New table Mapping to .edmx file manually

Asp.NET: Adding New table Mapping to Existing entity framework Designer .edmx manually from Xml View

As we know that .edmx designer entity framework mapping is generated either from ‘Database First Approach’ or from ‘Model First Approach’ (in case  ‘Code First Approach’ there is no .edmx designer file generated ). In these cases, EF Designer .edmx corresponds to ORM mapping to database tables.

You can choose to update the Model from database, but it may take some time to bring changes in effect and you may also lose some settings. This could be a disadvantage when there is a large amount of existing code and you want only minor changes. In this case safer method can be manually changing the code in XML view of .edmx file.

To add new table mapping, you need to add new entity to .edmx designer by right clicking on .edmx designer and choosing the option to add new entity from the menu as shown below :

Let’s say the new table be ‘UserPerformanceRating’ for which I want to create a new Mapping in the existing EF designer :

Creating a new Mapping in .edmx designer will change only Conceptual Mapping. You can also see the Mapping View Information from Model Browser window as shown in the Screenshot :

From this screenshot, it can be clearly seen that ‘Store model’ does not include Mapping for new table. Here we are going to add manually the required mappings in the XML View.

Right click the .edmx page and Select the ‘Xml (Text) Editor ’ From the ‘Open with’ Dialogue Box :

After click on ‘Ok’ button , it will ask you to Close the .edmx designer window. Click on ‘Yes’ button. It will open the ‘Xml’ view of the .edmx file.

Also you will be able to see the ‘Error List’  Window and it will show error of ‘UserPreferenceRating’ is not Mapped and some warnings to you as shown in the screenshot below :

It is because, when I created a new Mapping in the designer, it will change only in the ‘ConceptualModels’ schema. In simple words, it will assign the data types corresponding to C# data types in the Conceptual schema. Currently It does not have any information regarding the database types.         

 

Below is the Xml code generated for the Conceptual Model schema for the newly added entity :

<EntityType Name="UserPerformanceRating">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Int32" Nullable="false" p1:StoreGeneratedPattern="Identity" />
<Property Name="UserId" Type="String" Nullable="false" />
<Property Name="ActivityId" Type="String" Nullable="false" />
<Property Name="Rating" Type="String" Nullable="false" />
</EntityType>

Now perform changes in the storage model to reflect new table mapping.

~ Add the schema declaration for table ‘UserPerformanceRating’ in the EntityContainer:

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

~ And then write in the Entity Types for the table schema with data types corresponding to  

  sql database types.    

<EntityType = "ETFile_C3Model.Store.UserPerformanceRating" store:Type = "Tables" Schema="dbo"  />
<EntityType Name="UserPerformanceRating">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
<Property Name="UserId" Type="int" Nullable="false" />
<Property Name="ActivityId" Type="varchar" Nullable="false" MaxLength="255"/>
<Property Name="Rating" Type="varchar" Nullable="false" MaxLength="50" />
</EntityType>

Update the C-S Mapping( Written as <edmx:Mapping>) to map correspondence of Store Model and conceptual Model with column mappings.

It defines the mapping of conceptual Model and Storage Model columns as follows :

<EntitySetMapping Name="UserPerformanceRating">
<EntityTypeMapping TypeName="ETFile_Model.UserPerformanceRating">
<MappingFragment StoreEntitySet="Cabinet">
<ScalarProperty Name="Id" ColumnName="Id" />
<ScalarProperty Name="UserId" ColumnName="UserId" />
<ScalarProperty Name="ActivityId" ColumnName="ActivityId" />
<ScalarProperty Name="Rating" ColumnName="Rating" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>        

This is the quickest way to manually add the schema for the New table in the ‘StorageModels’ and writing C-S Mapping for this. If you now look in the ‘Model Browser’ window it will display the new Mapping in the ‘storage’ Model also.

In Short, Write the table schema and mappings in the XML Schema editor by yourself, wherever it is missing.