[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](@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)

[ErikEJ]

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

[LeonarddeR]

@ErikEJ may I ask why your example contains a call to .AsEnumerable(). Is a call to First on the IQueryable problematic. How about if you prefer async?

[ErikEJ]

Yes, calling first on the result of FromSql is problematic.

Maybe you can use AsAsyncEnumetable?

[LeonarddeR]

I will tryu that, thanks!