I created a Stored Procedure by Selecting values from 2 different tables.

Now the problem I have is to execute and read the result data from my dotnetcore 3.1 code.

1. I am not sure if the code I have written is correct. I am using IQueryable<AppUser> but, The SP contains a mix of AppUser and CourseMarks. So can I represent the result ?

2. How to convert the result of IQueryable to a JSON format ?

Can someone please help me out sort this. I have been stuck in this issue for awhile now.

        var userId= new SqlParameter("@UserId",2);

        IQueryable<AppUser> userType =  _dbContext.AppUsers.FromSqlRaw("exec dbo.GetCourseAndCourseMark @UserId", userId).IgnoreQueryFilters();

SQL - SP

SELECT U.[Name],CM.CourseName , CM.CourseResult 
       U.ID,
       ISNULL(SUM(CM.Marks),0) AS TotalMarks
FROM dbo.[AppUser] U 
     LEFT JOIN dbo.CourseMarks CM ON U.ID = CM.UserID
WHERE U.ID = @UserId
GROUP BY U.[Name],
         U.ID, CM.CourseName , CM.CourseResult ;

CodePudding user response：

You will have to create a special class for your sp result, it should include ALL data properties that are selected in your sp

[NotMapped]
public class SpResult
{
     public int ID  {get; set;}
     public string Name {get; set;}
     public double TotalMarks {get; set;}

      .... and so on
}

All properties in this class should be the same name and type as it is in your stored procedure

Add SpPlayerResult to db context and run this code

public virtual DbSet<SpResult> SpResults { get; set; }

....      
var userId= new SqlParameter("@UserId",2);

var userType =  _dbContext.SpResults.FromSqlRaw("exec dbo.GetCourseAndCourseMark @UserId", userId)
.ToList().FirstOrDefault();

I don't know why you need convert it to json, since .net will do everything automatically, but you can use standard net serializer System.Text.Json

var json=  JsonSerializer.Serialize(userType);