Calling scalar function from c# using Entity Framework 4.0 / .edmx

Question

I would like to map my scalar function to my .edmx but it fails. I right click on my entity framework mapping, and choose update model from database. It appears in my stored procedures folder in my model browser.

However, when I want to add it to my Function Imports folder in the model browser, the message scalar function does not appear shows in the drop down list. Can someone help me?

I can call the scalar function using the old way, such as:

dbContext.ExecuteStoreQuery<DateTime?>(
"SELECT dbo.getMinActualLoadDate ({0}, {1}, {2}) AS MyResult", 
LoadPkid, LoadFkStartLoc, TripSheetPkid).First();

but it is not the best way. My manager would like me to find a way be able to put the scalar function in the "function import" folder so I can call the scalar function using the following code instead of the previous code:

dbContext.ExecuteFunction("getMinActualLoadDate ", paramList);

I tried to add an image to display what I mean but as my reputation is still low, I am unable to do so. However the image could be found here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/756865e5-ff25-4f5f-aad8-fed9d741c05d

Thanks.

Answer 1

I've encountered same problem. And here is solution I've found my self suitable enough (tested in EF5, but should also work in EF4):

There is no support of mapping scalar-value functions out of the box but you can execute them directly.

You can also edit edmx file to make edmx generate proper method for scalar-value function, but it ll be deleted if you ll synch you model with database.

Write scalar-valued function implementation yourself:

string sqlQuery = "SELECT [dbo].[CountMeals] ({0})";
Object[] parameters = { 1 };
int activityCount = db.Database.SqlQuery<int>(sqlQuery, parameters).FirstOrDefault();

Or edit edmx and add Xml for custom maping of scalar-valued function:

<Function Name="CountActivities" Aggregate="false" BuiltIn="false"    NiladicFunction="false" IsComposable="false"   ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <CommandText>
        SELECT [dbo].[CountActivities] (@personId)
    </CommandText>
    <Parameter Name="personId" Type="int" Mode="In" />
</Function>

This information was found in this blog post