How can I retrieve JSON stored procedure's result with Entity Framework Core 2.0?

Question

Ciao, I'm working on a Web API Core 2 project and I'm using Entity Framework Core 2 with a SQL Azure Database.

Actually

Inside SQL Azure Database I've a stored procedure that returns a JSON, as you can see below:

CREATE PROCEDURE [dbo].[sp_Procedura]
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @data NVARCHAR(MAX) = 
    (SELECT
        *
    FROM
        dbo.Entity
    FOR JSON AUTO)

    select 0 as 'Id', @data as 'data'
END

Actually, to read the result, I had to create a entity that maps the result and reading the data with following code:

string json = myContext
    .JsonResult.FromSql("exec dop.sp_Procedura")
    .ToList().FirstOrDefault().data;

below the entity class:

public class Entity
{
    [Key]
    public int Id { get; set; }

    public string data { get; set; }
}

Desired

I think that this solution is not completely clean. My stored procedure should be like the following:

CREATE PROCEDURE [dbo].[sp_Procedura]
AS
BEGIN

    SET NOCOUNT ON

    SELECT
        *
    FROM
        dbo.Entity
    FOR JSON AUTO
END

Having a stored procedure as previous, using EF Core 2.0 Is there a way for read the JSON result like the following pseudo-code?

string json = myContext.FromSql("exec dbo.sp_Procedura").Result;

Thanks you

Answer 1

With newtonsoft.JSon nuget package, you can convert the Entity to JSON and back. Do not need to implement the stored procedure.

In this way is faster, because the entity framework can use the binary transfer to transfer data from SqlServer and not the whole string content.

here are some samples to json serialize: https://www.newtonsoft.com/json/help/html/SerializeObject.htm

By the way, I believe the SqlServer is also uses the newtonsoft.json inside.