With all due respect to @devinbost, I've been working on the same issue for about a day, and while it didn't provide a solution, it did move me closer to finding one. In conclusion, there is only one possible solution: change the function's scalar type to a table value type with a single value. For a code sample, click here.
No changes need to be made to the EDMX XML; instead, replace 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
Screenshot of Edmx