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

Answer 2

Here is my solution to this issue, which is almost exactly what your manager was asking for.. albeit 18 months late.

As an vanilla method:

    /// <summary>     /// Calls a given Sql function and returns a singular value     /// </summary>     /// <param name="db">Current DbContext instance</param>     /// <typeparam name="T">CLR Type</typeparam>     /// <param name="sql">Sql function</param>     /// <param name="parameters">Sql function parameters</param>     /// <param name="schema">Owning schema</param>     /// <returns>Value of T</returns>     public T SqlScalarResult<T>(DbContext db,                                  string sql,                                  SqlParameter[] parameters,                                 string schema = "dbo") {          if (string.IsNullOrEmpty(sql)) {             throw new ArgumentException("function");         }          if (parameters == null || parameters.Length == 0) {             throw new ArgumentException("parameters");         }          if (string.IsNullOrEmpty(schema)) {             throw new ArgumentException("schema");         }          string cmdText =             $@"SELECT {schema}.{sql}({string.Join(",",                 parameters.Select(p => "@" + p.ParameterName).ToList())});";          // ReSharper disable once CoVariantArrayConversion         return db.Database.SqlQuery<T>(cmdText, parameters).FirstOrDefault();      }  } 

And as an extension method to EF:

namespace System.Data.Entity {      public static class DatabaseExtensions {          /// <summary>         /// Calls a given Sql function and returns a singular value         /// </summary>         /// <param name="db">Current DbContext instance</param>         /// <typeparam name="T">CLR Type</typeparam>         /// <param name="sql">Sql function</param>         /// <param name="parameters">Sql function parameters</param>         /// <param name="schema">Owning schema</param>         /// <returns>Value of T</returns>         public static T SqlScalarResult<T>(this Database db,                                             string sql,                                             SqlParameter[] parameters,                                            string schema = "dbo") {              if (string.IsNullOrEmpty(sql)) {                 throw new ArgumentException("sql");             }              if (parameters == null || parameters.Length == 0) {                 throw new ArgumentException("parameters");             }              if (string.IsNullOrEmpty(schema)) {                 throw new ArgumentException("schema");             }              string cmdText =                 $@"SELECT {schema}.{sql}({string.Join(",",                      parameters.Select(p => "@" + p.ParameterName).ToList())});";              // ReSharper disable once CoVariantArrayConversion             return db.SqlQuery<T>(cmdText, parameters).FirstOrDefault();          }      }  } 

Though it doesn't smoke here, I would suggest unit testing before any serious use.