EF 6.1 Scalar-Valued Functions Database First

asp.net-mvc c# entity-framework sql-function

Question

I'm using EF 6.1 in my c# MVC5 application. Database First imports tables and functions. I can see the function mentioned under DALModel.Store / Stored Procedures / Functions in the model (emdx) browser (grayed out).

The following is how I'm attempting to use the function:

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

I get fn TotalClient can't be resolved.

I would value your recommendations.

1
7
10/15/2014 6:52:00 AM

Accepted Answer

Evidently, I was unable to incorporate Scalar-Valued Functions directly into my model; however, I discovered a workaround on the blog http://programmaticponderings.wordpress.com/2012/11/22/first-impressions-of-database-first-development-with-entity-framework-5-in-visual-studio-2012/.

Instead, I developed the function again as a Table-Valued Function and then used FirstOrDefault() to obtain the result value.

I hope this can assist someone who is having the similar problem.

9
10/16/2014 6:51:10 PM

Popular Answer

The single/scalar value must be changed in SQL to a table valued function in order for it to 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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow