EF 6.1 Scalar-Valued Feature Database

Question

My application is C # MVC5 using EF 6.1. Imported tables and functions using the First database. I see a function in the model browser (emdx) specified in DALModel.Store/ Stored Procedures / Functions (grayed out).

I am trying to use this 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 not resolve fn_TotalClient

Thank you for your suggestions.

Answer 1

Apparently, I could not use the Scalar-Valued Function directly in my model; I found a solution to 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 took a different approach, reworking the function as a function with tables, and then using FirstOrDefault () to get the result value.

Hope this helps someone who is facing the same problem.

Answer 2

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

Scalar function as is, which does not 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 Meaningful 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