ASP.NET Core 3.1 : using stored procedures with view model

The below code is used to run a stored procedure and return the values (rows) to var ReturnedRowsFromSP:

var recordValue = new SqlParameter("@thisRecordValue", RecordValueFromInput);

var ReturnedRowsFromSP = _context.ReturnedRowsTableExistsInDB.FromSqlRaw("exec sp_MySpReturnsManyRows @thisRecordValue", recordValue)
                                 .AsNoTracking().ToList();

My problem here is, I need to create a class, do migration and add the table (ReturnedRowsTableExistsInDB) to the database in order to use the stored procedure and the class needs to be exactly the same as the returned columns from the stored procedure.

Is there any way to use stored procedures with tables (not created in the database) or with view model or even to pass the result (of the stored procedure) to a ViewBag or ViewData?

Many thanks in advance

You could refer the following steps to query stored procedure using EF core (without creating table for the stored procedure result).

1. Create the Stored Procedure in SQL server database:

    CREATE PROCEDURE sp_GetBookAuthors @bookname nvarchar(250)
    AS
   
    select b.Id, b.BookName, a.AuthorName, b.ISBN from Authors as a 
             join BookAuthors as ba 
             on a.Id = ba.AuthorId 
             join Books as b 
             on ba.BookId = b.Id
             where b.BookName = @bookname
   

2. Based on the query result create the following ViewModel:

    public class BookAuthorViewModel
    {
        [Key]
        public int Id { get; set; }
        public string BookName { get; set; }
        public string AuthorName { get; set; }
        public string ISBN { get; set; }
    }
   

3. Register the stored procedure in the DB context OnModelCreating method:

    public class WebApplication2Context : IdentityDbContext<WebApplication2User>
    { 
        public DbSet<Book> Books { get; set; }
        public DbSet<Author> Authors { get; set; }
        public DbSet<BookAuthor> BookAuthors { get; set; } 
   
        public WebApplication2Context(DbContextOptions<WebApplication2Context> options)
            : base(options)
        {
        }
   
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.Entity<BookAuthor>().HasKey(sc => new { sc.AuthorId, sc.BookId });
   
            #pragma warning disable CS0618 // Type or member is obsolete
            builder.Ignore<BookAuthorViewModel>(); //ignore create the table for the stored procedure
            builder.Query<BookAuthorViewModel>();  //register stored procedure.
            #pragma warning restore CS0618 // Type or member is obsolete
        }
   
        //create a method for the stored procedure.
        public List<BookAuthorViewModel> GetBookAuthors(string sqlQuery)
        {
            // Initialization.  
            List<BookAuthorViewModel> lst = new List<BookAuthorViewModel>();
   
            try
            { 
                #pragma warning disable CS0618 // Type or member is obsolete
                lst = this.Query<BookAuthorViewModel>().FromSqlRaw<BookAuthorViewModel>(sqlQuery).ToList();
                #pragma warning restore CS0618 // Type or member is obsolete
            }
            catch (Exception ex)
            {
                throw ex;
            }
            // Info.  
            return lst;
        }
    }
   

4. Call the store procedure method from controller action method:

    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;
        private readonly WebApplication2Context _dbcontext;
   
        public HomeController(ILogger<HomeController> logger, WebApplication2Context context)
        {
            _logger = logger;
            _dbcontext = context;
        }
   
        public IActionResult BookAuthorIndex()
        { 
            // Processing.  
            string sqlQuery = "execute sp_GetBookAuthors @bookname = 'Book A'";
            var result = _dbcontext.GetBookAuthors(sqlQuery);
            return View(result);
        }
   

The result as below: