Efcore: [Ef Core 5.0] Call a stored procedure and retrieve value

[julienGrd]

Hello guys, i already read a lot of docs and try a lot of stuff but im definitively not able not call a particular stored proc, surprisely i don't find any doc in this situation, so maybe you have an idea

I Have a stored procedure like this
````
CREATE PROCEDURE [dbo].[GetNbMessages]
@DestinataireId int,
@EtsId tinyint = null,
@Statut tinyint = null
AS
BEGIN
SET NOCOUNT ON;

SELECT  Count(Id)
FROM    [Messages] Msg
            LEFT JOIN GrpDests_Users Dg on DG.GrpNum = Msg.DestGrpNum AND DG.UserID = @DestinataireID
WHERE   
        --DestinataireID = @DestinataireId 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 = @DestinataireId OR DG.UserID IS NOT NULL)
            AND (@Statut IS NULL OR Statut = @Statut)

END

GO
````

you call it in sql like this
````
DECLARE @RC int
DECLARE @DestinataireId int
DECLARE @EtsId tinyint
DECLARE @Statut tinyint

-- À faire : définir des valeurs de paramètres ici.

EXECUTE @RC = [dbo].[GetNbMessages]
@DestinataireId
,@EtsId
,@Statut
GO
````

I'm for now not able to make this kind of code works, i don't know if it the good way to do that, i don't find any docs (or its doc for ef core 1 or 2, it seem things change since). i only find docs with output parameter which is not my case
````
var returnValParam = new SqlParameter("@returnVal", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue};//
this.Database.ExecuteSqlRaw("exec @returnVal=dbo.GetNbMessages @DestinataireId, @EtsId, @Statut", returnValParam
, new SqlParameter("@DestinataireId", destinataireId),
new SqlParameter("@EtsId", etsId),
new SqlParameter("@Statut", statut));
var result = int.Parse(returnValParam.Value.ToString());

````
this code finish with exception "@returnVal not declared". How i can make it works ?

I also try this kind of syntax, im not sure its relevant in my case, anyway i was not able to make it works too
[DbFunction("GetNbMessages", "dbo")] public int? GetNbMessages(Nullable<int> destinataireId, Nullable<byte> etsId, Nullable<byte> statut)
thanks for your help !

[ErikEJ]

Blog post coming up!

[ErikEJ]

Blog post up: https://erikej.github.io/efcore/2020/05/26/ef-core-fromsql-scalar.html

[julienGrd]

Blog post up: https://erikej.github.io/efcore/2020/05/26/ef-core-fromsql-scalar.html

Perfect it work !

thanks for your help !

[julienGrd]

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
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().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)

[ErikEJ]

Please raise a new issue and I might have a look.