Extend my web API (EF asp.core) to call a stored proc

See more:

C#

SQL

entity-framework

, +

Hi I'm very new to Web And API development and I'm trying to build a very simple API to connect to a local SQL DB. The ultimate aim will be to push this into Azure and create a Android app for the UI.

I've built my API (With the help of EF Scaffolding) and it works in debug returning the data from the respective DB Tables through my Models and controllers
However I need to return data from 2 tables (Inner joined), but my scaffolded API only returns data linked to the specific Table EF built..


My Models & controllers..

Practice Model

using System;
using System.Collections.Generic;

namespace NSC_API.Models
{
    public partial class Practice
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string ContactName { get; set; }
        public string ContactAddress { get; set; }
        public string ContactEmail { get; set; }
        public int? Milage { get; set; }
    }
}

PracticesController

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using NSC_API.Models;

namespace NSC_API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class PracticesController : ControllerBase
    {
        private readonly NSClinical_DBContext _context;

        public PracticesController(NSClinical_DBContext context)
        {
            _context = context;
        }

        // GET: api/Practices
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Practice>>> GetPractice()
        {
            return await _context.Practice.ToListAsync();
        }

        // GET: api/Practices/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Practice>> GetPractice(int id)
        {
            var practice = await _context.Practice.FindAsync(id);

            if (practice == null)
            {
                return NotFound();
            }

            return practice;
        }

        // PUT: api/Practices/5
        // To protect from overposting attacks, enable the specific properties you want to bind to, for
        // more details, see https://go.microsoft.com/fwlink/?linkid=2123754.
        [HttpPut("{id}")]
        public async Task<IActionResult> PutPractice(int id, Practice practice)
        {
            if (id != practice.Id)
            {
                return BadRequest();
            }

            _context.Entry(practice).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!PracticeExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        // POST: api/Practices
        // To protect from overposting attacks, enable the specific properties you want to bind to, for
        // more details, see https://go.microsoft.com/fwlink/?linkid=2123754.
        [HttpPost]
        public async Task<ActionResult<Practice>> PostPractice(Practice practice)
        {
            _context.Practice.Add(practice);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetPractice", new { id = practice.Id }, practice);
        }

        // DELETE: api/Practices/5
        [HttpDelete("{id}")]
        public async Task<ActionResult<Practice>> DeletePractice(int id)
        {
            var practice = await _context.Practice.FindAsync(id);
            if (practice == null)
            {
                return NotFound();
            }

            _context.Practice.Remove(practice);
            await _context.SaveChangesAsync();

            return practice;
        }

        private bool PracticeExists(int id)
        {
            return _context.Practice.Any(e => e.Id == id);
        }
    }
}

WorkItems Model

using System;
using System.Collections.Generic;

namespace NSC_API.Models
{
    public partial class WorkItems
    {
        public int Id { get; set; }
        public DateTime? WiDate { get; set; }
        public DateTime? WiStartTime { get; set; }
        public DateTime? WiEndTime { get; set; }
        public int? Practice { get; set; }
        public string Rate { get; set; }
        public string Paid { get; set; }
        public string Revenue { get; set; }
    }
}

WorkItemscontroller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using NSC_API.Models;

namespace NSC_API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class WorkItemsController : ControllerBase
    {
        private readonly NSClinical_DBContext _context;

        public WorkItemsController(NSClinical_DBContext context)
        {
            _context = context;
        }

        // GET: api/WorkItems
        [HttpGet]
        public async Task<ActionResult<IEnumerable<WorkItems>>> GetWorkItems()
        {
            return await _context.WorkItems.ToListAsync();
        }

        // GET: api/WorkItems/5
        [HttpGet("{id}")]
        public async Task<ActionResult<WorkItems>> GetWorkItems(int id)
        {
            var workItems = await _context.WorkItems.FindAsync(id);

            if (workItems == null)
            {
                return NotFound();
            }

            return workItems;
        }

        // PUT: api/WorkItems/5
        // To protect from overposting attacks, enable the specific properties you want to bind to, for
        // more details, see https://go.microsoft.com/fwlink/?linkid=2123754.
        [HttpPut("{id}")]
        public async Task<IActionResult> PutWorkItems(int id, WorkItems workItems)
        {
            if (id != workItems.Id)
            {
                return BadRequest();
            }

            _context.Entry(workItems).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!WorkItemsExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        // POST: api/WorkItems
        // To protect from overposting attacks, enable the specific properties you want to bind to, for
        // more details, see https://go.microsoft.com/fwlink/?linkid=2123754.
        [HttpPost]
        public async Task<ActionResult<WorkItems>> PostWorkItems(WorkItems workItems)
        {
            _context.WorkItems.Add(workItems);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetWorkItems", new { id = workItems.Id }, workItems);
        }

        // DELETE: api/WorkItems/5
        [HttpDelete("{id}")]
        public async Task<ActionResult<WorkItems>> DeleteWorkItems(int id)
        {
            var workItems = await _context.WorkItems.FindAsync(id);
            if (workItems == null)
            {
                return NotFound();
            }

            _context.WorkItems.Remove(workItems);
            await _context.SaveChangesAsync();

            return workItems;
        }

        private bool WorkItemsExists(int id)
        {
            return _context.WorkItems.Any(e => e.Id == id);
        }
    }
}

In the DB the tables\data are joined in Workitems.Practice inner joined with Practice.ID

Select wi.ID, wi.WI_Date, wi.WI_Start_Time, wi.WI_End_Time, PR.Name, Wi.Paid, wi.Revenue
From Work_Items WI inner join Practice PR on WI.Practice = PR.ID

Any help would be VERY much appreciated as im really struggling..

thanks for reading..

What I have tried:

Ive tried to read several articles but am really struggling and becoming more confused by the day.
Ive adopted for a DB first approach (just because my background is more in the DB space, ill need to run a series of ad hoc querries at a later point against the DB and im a little old school in that regard) and so im struggling to find a suitable resource to help..

My Stored proc simple calls:

Select 
	wi.ID, wi.WI_Date, wi.WI_Start_Time, wi.WI_End_Time, PR.Name, Wi.Paid, wi.Revenue

	From 
	Work_Items WI inner join Practice PR on WI.Practice = PR.ID