How to import a function with scalar return value in EF 5

Question

I'm using EF 5 with .NET 4.5, I have created a model for my database and imported my functions in to the model, I can successfully import TVF an SP but I'm not able to import functions with scalar return value. Is it possible with designer or I should manually edit the edmx file?

Answer 1

Scroll down to the section on Scalar Valued Functions on this page:

Database First Development with Entity Framework 5 - Importing Scalar Valued Functions

You can either follow this ugly workaround, or you can follow the advice that I will give at the very bottom of this answer.

Here's an excerpt from that article (on the workaround):

"This method requires some minor changes of the .edmx file’s xml, directly. To do so, right-click on the .edmx file and select ‘Open With…’, ‘XML (Text) Editor’. This is how the functions looks in the .edmx file before changes:

<Function Name="CountActivities" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountHydrations" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountMeals" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <Parameter Name="personId" Type="int" Mode="In" />
</Function>

"Remove the ‘ReturnType’ attribute from the element. Then, add a element to each of the elements. See the modified .edmx file below for the contents of the elements.

<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>
<Function Name="CountHydrations" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <CommandText>
        SELECT [dbo].[CountHydrations] (@personId)
    </CommandText>
    <Parameter Name="personId" Type="int" Mode="In" />
</Function>
<Function Name="CountMeals" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <CommandText>
        SELECT [dbo].[CountMeals] (@personId)
    </CommandText>
    <Parameter Name="personId" Type="int" Mode="In" />
</Function>

"Next, in the Model Browser, right-click on the ‘Function Imports’ folder and select ‘Add Function Import…’ This brings up the ‘Add Function Import’ dialog window. We will import the ‘CountActivities’ scalar-valued function to show this method. Enter the following information in the dialog window and select Save."

My Advice: Considering the effort required, it is way easier to just create a table-valued function that only returns one row to achieve the same effect and purpose.

Here's an example of a User-Defined Table-Valued function in SQL Server:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfGetTotalMinutesInvoiced] ( @billingType INT )
RETURNS TABLE
AS
RETURN
    (
      SELECT
            SUM([ProgressNote].[TotalMinutes]) AS 'TotalMinutesInvoiced'
        FROM
            [dbo].[InvoiceEntry]
        JOIN [dbo].[ProgressNote]
            ON [ProgressNote].[ProgressNoteID] = [InvoiceEntry].[ProgressNoteID]
        WHERE
            [InvoiceEntry].[BillingTypeID] = @billingType
            AND progressNote.[IsRecordedInInvoiceEntry] = 1
    )
GO

Notice also that you can actually call scalar-valued user-defined functions from within your table-valued function.

To call the table valued function, you would probably want to use the FirstOrDefault method like this:

        private void UpdateStatisticsPanel()
        {
            var billingTypeId = int.Parse(txtBillingTypeId.Text);

            var totalMinutesInvoiced = context.udfGetTotalMinutesInvoiced(billingType: billingTypeId);
            var minutesInvoiced = totalMinutesInvoiced.FirstOrDefault();
            var invoiced = new Tuple<string, int?>("totalMinutesInvoiced:", minutesInvoiced);
            lstFinancialSummary.Items.Add(invoiced);

            var totalMinutesNotInvoiced = context.udfGetTotalMinutesNotInvoiced(billingType: billingTypeId);
            var minutesNotInvoiced = totalMinutesNotInvoiced.FirstOrDefault();
            var notInvoiced = new Tuple<string, int?>("totalMinutesNotInvoiced:", minutesNotInvoiced);

            lstFinancialSummary.Items.Add(notInvoiced);
            // remember to push the values up to the ListView.
        }

Answer 2

Struggling with the same problem for almost a day and with all due respect @devinbost, it didn't solve the problem but it put me closer to the solution. In conclusion, the one and only solution are 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