EF 6.1 Scalar-Valued Functions Database First

Question

My application is c# MVC5, using EF 6.1. Imported tables and functions using Database First. I can see the function in model (emdx) browser listed under DALModel.Store / Stored Procedures / Functions (grayed out).

I am trying to use the function using the following:

using (var ctx = new DALEntities())
{
    int? result = ctx.fn_TotalClient(MemberRepository.AllowedCId, fromDate, toDate);
    return (result != null ? result.Value : 0);
}

I get can't resolve fn_TotalClient

Would appreciate your suggestions.

Old issue that still exists in EntityFramework 6.1.2 https://social.msdn.microsoft.com/Forums/en-US/756865e5-ff25-4f5f-aad8-fed9d741c05d/add-scalar-function-to-function-import-folder-in-model-browser-of-entity-framework-40-edmx?forum=adodotnetentityframework — Waqar, Apr 06 '16 at 14:14
Glad you got a solution. Would just like to comment that a better way to use the nullable int is to check its `HasValue` property: `return (result.HasValue ? result.Value : 0);` — SvenAelterman, Jul 19 '16 at 00:00

score 9 · Accepted Answer · answered Oct 16 '14 at 18:51

Apparently I could not use Scalar-Valued Function directly into my model; I found a solution in this blog http://programmaticponderings.wordpress.com/2012/11/22/first-impressions-of-database-first-development-with-entity-framework-5-in-visual-studio-2012/.

However, I used a different approach by redeveloping the function as a Table-Valued Function, then used FirstOrDefault() to get the result value.

Hope this could help someone facing the same issue.

score 2 · Answer 2 · answered Aug 17 '17 at 05:36

Well, you need to modify SQL to convert the single/scalar value to table valued function then it will work.

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

EF 6.1 Scalar-Valued Functions Database First

2 Answers2