Can I scaffold a controller and view based on a SQL query or stored procedure? (in ASP.NET Core MVC with EF Core)

Can I make use of the scaffolding feature in Visual Studio where the model is based on a SQL query or stored procedure rather than a database table?

https://learn.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/adding-controller?view=aspnetcore-7.0&tabs=visual-studio

This is in part fuelled by laziness/efficiency but also to understand the limitations of EF Core & VS scaffolding... Therefore I would also like to find out about recommended alternative approaches.

I will be generating a bunch of reports selecting aggregate columns from various tables with time period filtering, all to be displayed within the ASP.NET Core MVC web app.

I would prefer writing the SQL as actual SQL rather than fumble around with Linq-to-SQL but either is an option. I'm also not bothered if the SQL is a string executed in the web app or a stored procedure or even a SQL view.

I realise that I could manually generate the model, controller and view code and execute the SQL and project it as an anonymous type. But that feels like a lot of typing!

Would it be possible to have in the DbContext a DBSet<TimeReport> TimeReports and have that entity based on SQL so as to facilitate scaffolding?

My current working idea is to create a SQL view per report and have in the DbContext a DBSet<TimeReportView> TimeReports but haven't yet tried this.

I realise now that this was a pretty dumb question for me to ask...

Why? - VS scaffolding doesn't really know if you have a DB table as defined in a DbContext until it tries to make use of it in runtime.

Therefore you can create a Model class containing whatever properties you want...

1. Put a DbSet<TMyFakeModel> in your DbContext
2. Leverage Scaffolding to save on typing
3. Remove DbSet<TMyFakeModel> in your DbContext
4. Update Scaffolded Controller to retrieve your Models from an alternative source such as Linq to Sql, Stored Procedure etc...

E.g. Change scaffolded code in Controller...

// GET: StaffTimeReport
        public async Task<IActionResult> Index()
        {
              return _context.StaffTimeReport != null ? 
                          View(await _context.StaffTimeReport.ToListAsync()) :
                          Problem("Entity set 'AdminContext.StaffTimeReport'  is null.");
        }

To something like this instead...

// GET: StaffTimeReport
        public async Task<IActionResult> Index()
        {

           
            var response = 
                (
                    from staff in _context.Users
                    join sessions in _context.Sessions on staff.UserId equals sessions.UserId
                    group new { staff, sessions } by new { staff.UserId, staff.FirstName, staff.Surname } into g
        //project to model that was scaffolded
                    select new StaffTimeReport
                    {
                         UserId = g.Key.UserId,
                          FirstName = g.Key.FirstName,
                           Surname = g.Key.Surname ,
                            Points = g.Sum(g=>g.sessions.Points), 
                            Sessions = g.Count()  ,
                            TimeSpent = g.Sum(g => g.sessions.SessionLength)
                    }
                )               
                .ToList();

            return  View(response);
        }