Calling scalar function from c# using Entity Frame

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 guess you miss the Edit Function Import dialog, where you can generate Complex Types. try to explore.

If you have successfully created the scalars, you can now navigate like this

using (var con = new DatabaseEntities())
{
   long? invoiceNo = con.sp_GetInvoiceMaxNumber(code.Length + 2).First();
   ....
}

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.

Answer 3

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 4

The one and the only solution is to convert the function scalar type to table value type with a single value in the table, please see the code sample.

You don't have to change anything in the EDMX XML, please modify the SQL function

Scalar function as it was, which doesn't work

CREATE FUNCTION [dbo].[GetSha256]
(
    -- Add the parameters for the function here
    @str nvarchar(max)
)
RETURNS VARBINARY(32)
AS
BEGIN
    RETURN ( SELECT * FROM HASHBYTES('SHA2_256', @str) AS HASH256 );
END -- this doesn't work.

Scalar function -> Converted to Table Valued function , it works

CREATE FUNCTION [dbo].[GetSha2561]
(
    -- Add the parameters for the function here
    @str nvarchar(max)
)
RETURNS  @returnList TABLE (CODE varbinary(32))
AS
BEGIN

    INSERT INTO @returnList
    SELECT HASHBYTES('SHA2_256', @str);

    RETURN; -- This one works like a charm.

END

Edmx screenshot