EF 6.1 Scalar-Valued Functions Database First
Solution 1
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.
Solution 2
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
Related videos on Youtube
Comments
-
hncl over 1 year
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.