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:

Create an Entity Key When No Key Is Inferred

Add a Defining Query (Entity Framework)

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

Edmx Views Processor (Free)

Use Scalar Function with Entity Framework and MVC 5 project

I have faced the challenge of using scalar function within select statement in SQL or Oracle in mvc project for example: 

select hr.get_emp_name(:P_EmpID) from dual 

So suppose you have an edmx model with the views selected and you want to add the statement above and use it, the problem with this scalar function is the way its being used, its look like a normal select statement, not like stored procedures where it expects input parameter and output parameters.

Well after searching the Internet for the answer and after reading many blogs and articles with some help with my friends I have reached to the right and easy way to do so.

Step1:

Open the model1.edmx diagram and write click on it and choose (Update Model from Database).

When the dialog open go to the (add) tab and expand the stored procedures and functions extension.

Select your schema and then select the scalar function you want to represent in your model, let say the function name is hr.get_emp_name(P_EmpID as string) from the dbo schema.

Note: Make sure to remove the check box (Import Selected Stored procedures and functions into entity model). because you will get a warning that this function can't be imported and it is not added to the model.

Step 2:

After that if you go to the Model Browser Window and expand the model1.edmx, then expand Model.Store, then expand Stored Procedures or Functions, you will see function you have added in step one with the name hr_emp_name and under it you will see it has a parameter named P_EmpID.

until now everything is cool, now lets move to the most important step; step3.

Step3:

In this step you will modify the model.edmx file xml file, yes you will do some dirty nifty things in the XML.

Ok then, wake up, you are a programmer !!!

Ok, now right click the model1.edmx file from the Solution Explorer and click open with,

use XML Editor and open the file; it may ask you to close the diagram file if its opened, click ok.

Press ctrl+f to search for the word (function), and you will find the function you added in step one as xml tags starts with

ok now, you have to modify these parameters in the function tag:

IsComposable="false"

Remove the ReturnType attribute.

Remove StoreFunctionName attribute, becuase its a function not a stored procedure.

Add attribute: store:Name="hr.EMP_NAME", means i want to execute this function.

Then after the function opening tag add the CommandText tag:

            select dbo.HR.EMP_NAME(:P_EmpID) from dual


Make sure you have a parameter named P_EmpID.

as a result your function tag must look like the one below:

HR_EMP_NAME
" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="DBO" store:Name="hr.EMP_NAME">         
            select dbo.HR.EMP_NAME(:P_EmpID) from dual
         
          P_EmpID" Type="varchar2" Mode="In" />

As a result you will save the xml file, build the project and close the xml file.

Now we will move to step4

Step4:

In this step you will import the function you have add to the entity model, this step didn't complete if you check the import stored procedures and functions to entity model in step1.

So, go again to the model1.edmx diagram and browse the Model Browser, expand the Model.Store, then expand Stored Procedures or Functions, and double click the function you have added with the name hr_emp_name.

The Import function will open now,

write down the function name you want.

Uncheck the Function Import is Composable.

Then chose the function from the dropdown list which is HR_EMP_NAME.

Return the collection depends on your function, in our case the function will return scalars of type string, sometimes you may return none or an entity.

Ok for now, click generate column info, and you may get an exception i didn't solve till this post. then click ok and save.

Step5:

Now its time to use the function inside a controller.

If you open the model1.context.cs file after save and build everything you will notice the definition of the function with something like this below:

public virtual ObjectResult<string> HR_EMP_NAME(string P_EmpID)
        {
            var P_EmpIDParameter = P_EmpID!= null ?
                new ObjectParameter("P_EmpID", P_EmpID) :
                new ObjectParameter("P_EmpID", typeof(string));
    
            return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("HR_EMP_NAME", P_EmpIDParameter);

        }

To use it in the a controller

string empName = "";
            empName = _db.HRINFO_EMP_EMAIL("55214").First();
            if (!string.IsNullOrEmpty(empName))

                ViewBag.empName = empName ;

Cool ha, i know its somehow complex but I hope this was useful for you.