Tuesday, May 6, 2014

Adding View or Table when no key or primary key is Inferred to edmx model EF5

Sometimes when you work on entity model (edmx) and want to add a view or table that doesn't have a primary key or non-nullable field (this case specially arise when you add a view to the model), then you end up with warning message says:

"Error 6013: The table/view  does not have a primary key defined and no valid primary key could be inferred"

I have googled it, and the answer was to modify the edmx xml file () to add this view manually to the edmx.
The steps are straightforward, but need to copy paste the solution when ever you want to update the model which will override your modified code. So rule #1 in this post is:

"When you do some manual modifications to the edmx file, you must take a copy of the text before updating the model".

ok, let say that you have a view which shows the number of students in each faculty in the university named INV (Institute of Nano Velocity).
the View name is: V_INV_TOTAL_STU, which has columns:

FAC_CODE: number.
FAC_NAME: Varchar2 (max: 4000 bytes).
TOTAL: number.

Now the following will show how to work manually with EF5 and edmx model file.

Step1:
Try to add the view from the update model option in the edmx file automatically, or at least try to.
If the above error (6013) appeared then right click the edmx file and choose edit with XML Editor

When the edmx opened in xml do the following:

1. Uncomment the entitytype generated automatically in the



Note: nullable="false" must be added to the property so that FAC_CODE will be the key of the view.

2. Add the following code in the


3. in the at the tag add the following:


4. Add the following Code in the  :
 Note: this will map the column name to the corresponding property in the model.
         

Save the edmx file.

Step2:
Now its time to map or bind this view to its corresponding model, so create a new class in the models folder named V_INV_TOTAL.cs.
then add the following properties to it:
        public int FAC_CODE { get; set; }
        public string FAC_NAME { get; set; }
        public Int64 TOTAL_REQUESTS { get; set; }

Step3:
The last step is to modify the model.context.cs file which will connect to the DB.
add this code to its properties:

public DbSet V_INV_TOTAL_REQUESTS { get; set; }.

Now you are ready to work on with this new model in your controllers and views.
Again I want to remind you to take a copy of the modified edmx and context files generated automatically when you create the edmx model for the first time, because once you want to update the model, then these files will be overwritten.... so be careful.



references:


Also i have found this tool from VisualStudio 2013 team, hope it will much easier than the steps provided in this post: