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.