[Ef Core 5.0] Call a scalar function and retrieve value

[julienGrd]

This issue follow this one #21039, but focus on the call on the scalar functions

On the same way, how I can call a scalar function directly? I only find example when its use inside entity request but not from outside ? (with the DbFunction attribute)

If i take back my example

CREATE FUNCTION [dbo].[FnGetNbMessages](@DestId int, @EtsId tinyint, @Statut tinyint)
RETURNS int
AS
BEGIN
	return ISNULL(
	(
		SELECT	Count(Id) 
		FROM	[Messages] Msg
					LEFT JOIN GrpDests_Users Dg on DG.GrpNum = Msg.DestGrpNum AND DG.UserID = @DestId
		WHERE	
				--DestUserID = @DestId AND (EtsID IS NULL OR EtsID = @EtsId) AND (@Statut IS NULL OR Statut = @Statut)
				(@EtsId IS NULL OR Msg.EtsID is NULL OR Msg.EtsID = @EtsId)
					AND (DestUserID = @DestId OR DG.UserID IS NOT NULL)
					AND (@Statut IS NULL OR Statut = @Statut)
	), 0)

END;

I try

var result = this.Set<IntReturn>().FromSqlRaw("select dbo.FnGetNbMessages(@p0, @p1, @p2)", destinataireId, etsId, statut)
                        .AsEnumerable().FirstOrDefault()?.Nb ?? 0;

but without success (and it seem i can't put "as Nb" in the scalar function)

thanks !

[ErikEJ]

What does "without success" mean? An error or no result?

[julienGrd]

I have this exception
System.InvalidOperationException 
The required column 'Nb' was not present in the results of a 'FromSql' operation.'

[smitpatel]

#9810

[ErikEJ]

Have you tried:

select dbo.FnGetNbMessages(@p0, @p1, @p2) AS Nb

[julienGrd]

Oh yes you have right i don't think about that, it works perfectly, thanks !