Click here to Skip to main content
15,942,154 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
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
C#
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
C#
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
C#
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
C#
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
SQL
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
Posted
Updated 2-Nov-20 5:22am
v3

1 solution

Sorry should have included that my stored proc returns a cimple Inner join statement on the tables:

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
 
Share this answer
 
Comments
Richard Deeming 2-Nov-20 11:10am    
If you want to update your question to include additional information, click the green "Improve question" link and edit your question.

Do not post your update as a "solution" to your question.
DumbCoderX 2-Nov-20 15:53pm    
Appologies

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900