How to create API endpoint to fetch Departments including Student in it?

Question

I have implemented a .net core web api with Student and Department entities. One to One relationship is implemented between these entities where departmentid is foreignkey for student table. How can I implement api end point in DepartmentController to fetch all the departments with student included in it?

DepartmentController.cs

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 Students.Models;

namespace Students.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DepartmentController : ControllerBase
    {
        private readonly StudentContext _context;

        public DepartmentController(StudentContext context)
        {
            _context = context;
        }

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

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

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

            return department;
        }

        // PUT: api/Department/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for
        // more details see https://aka.ms/RazorPagesCRUD.
        [HttpPut("{id}")]
        public async Task<IActionResult> PutDepartment(int id, Department department)
        {
            if (id != department.Id)
            {
                return BadRequest();
            }

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

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

            return Ok();
        }

        // POST: api/Department
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for
        // more details see https://aka.ms/RazorPagesCRUD.
        [HttpPost]
        public async Task<ActionResult<Department>> PostDepartment(Department department)
        {
            _context.Departments.Add(department);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetDepartment", new { id = department.Id }, department);
        }

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

            _context.Departments.Remove(department);
            await _context.SaveChangesAsync();

            return department;
        }

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

Department.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Students.Models
{
    public class Department
    {
        [Key]
        public int Id { get; set; }
        [Required]
        [Column(TypeName = "varchar(20)")]
        public string Dep { get; set; }
    }
}

Student.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Students.Models
{
    public class Student
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int SId { get; set; }
        [Required]
        [Column(TypeName ="varchar(50)")]
        public string Name { get; set; }
        public int DepartmentId { get; set; }
        public Department Department { get; set; }
    }
}

StudentContext.cs

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace Students.Models
{
    public class StudentContext:DbContext
    {
        public StudentContext(DbContextOptions<StudentContext> options) : base(options)
        {

        }
        public DbSet<Student> Students { get; set; }
        public DbSet<Department> Departments { get; set; }
    }
}

StudentController.cs Please refer GetStudents()

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

namespace Students.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class StudentsController : ControllerBase
    {
        private readonly StudentContext _context;

        public StudentsController(StudentContext context)
        {
            _context = context;
        }

        // GET: api/Students
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Student>>> GetStudents()
        {
            return await _context.Students.Include(d => d.Department).ToListAsync();
        }

        // GET: api/Students/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Student>> GetStudent(int id)
        {
            var student = await _context.Students.Include(d => d.Department).FirstOrDefaultAsync(i => i.SId == id);

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

            return student;
        }

        // PUT: api/Students/5
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for
        // more details see https://aka.ms/RazorPagesCRUD.
        [HttpPut("{id}")]
        public async Task<IActionResult> PutStudent(int id, Student student)
        {
            if (id != student.SId)
            {
                return BadRequest();
            }
            _context.Departments.Update(student.Department);
            await _context.SaveChangesAsync();
            _context.Entry(student).State = EntityState.Modified;

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

            return Ok();
        }

        // POST: api/Students
        // To protect from overposting attacks, please enable the specific properties you want to bind to, for
        // more details see https://aka.ms/RazorPagesCRUD.
        [HttpPost]
        public async Task<ActionResult<Student>> PostStudent(List<Student> student)
        {
            try
            {
                for (var i = 0; i < student.ToArray().Length; i++)
                {
                    _context.Students.Add(student[i]);
                }
                _context.SaveChanges();
                return CreatedAtAction("GetStudents", student);
            }
            catch
            {
                return BadRequest();
            }

            /*_context.Students.Add(student);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetStudent", new { id = student.SId }, student);*/
        }


        /*[HttpPost]
        [Route("StudentList")]
        public async Task<ActionResult<Student>> PostStudentList([FromBody] List<Student> student)
        {
            try
            {
                for (var i = 0; i < student.ToArray().Length; i++)
                {
                    _context.Students.Add(student[i]);
                }
                _context.SaveChanges();
                return CreatedAtAction("GetStudents", student);
            }
            catch
            {
                return BadRequest();
            }
        }*/

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

            _context.Students.Remove(student);
            await _context.SaveChangesAsync();

            return student;
        }

        private bool StudentExists(int id)
        {
            return _context.Students.Any(e => e.SId == id);
        }
    }
}

I have already implemented this: `context.Students.Include(student => student.Department);` in StudentController but how to implement endpoint to fetch **Departments including Students in it**?? — Jay Bhiyani, Dec 03 '19 at 10:20
Can you update your example, so we can see, where you put it? — Fildor, Dec 03 '19 at 10:22

score 0 · Answer 1 · answered Dec 03 '19 at 10:15

What you're looking for is probably this: https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.entityframeworkqueryableextensions.include?view=efcore-3.0 use the include method on the student context to include the department like so:

context.Students.Include(student => student.Department);

It could be that you have to update your mapping so EF understands it. In that case, in your mapping you need to include a HasOne(student => student.Department) if necessary. or in your Department mapping, use a HasMany(department => department.Students) (but your department does not have a collection of students, so the latter would not be an option unless you change the model)

rjs123431 · Answer 2 · 2019-12-03T10:40:10.930

0

Modify your Department class to include the Student navigation property

public virtual Student Student { get; set; }

then in your GetDepartments() method, include the Student property

var query = _context.Departments.Include(d => d.Student);

that returns an IQueryable, so you can add another filtering

var filteredQuery = query.Where(d => d.Student.Name.Contains("jane"));

before you return it

return query.ToList();

or

return filteredQuery.ToList();

edited Dec 03 '19 at 10:40

answered Dec 03 '19 at 10:19

rjs123431

688
4
14

I have tried this. But it creates circular dependency between Student and Department – Jay Bhiyani Dec 03 '19 at 10:22
update your `Student` class to make it `virtual` for `Deparment` property `public virtual Department Department { get; set; }` – rjs123431 Dec 03 '19 at 10:25
Is there any other way possible using Include() and Where() so that I can iterate all the departments and can fetch student in it while GetDepartments()?? – Jay Bhiyani Dec 03 '19 at 10:30
updated the answer, modify it to fit your needs. Mark it as answer if it resolved your issues. Thank you – rjs123431 Dec 03 '19 at 10:41

score 0 · Answer 3 · answered Dec 04 '19 at 05:53

I have tried this. But it creates circular dependency between Student and Department

As above has said that you could add Student navigation property in Department model and use Include to retrieve data.

To prevent Reference Looping (or remove circular dependency between Student and Department here),you could use below code in startup ConfigureServices:

For asp.net core 2.2:

services.AddMvc()
    .AddJsonOptions(
        options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
);

For asp.net core 3.0:

services.AddControllers()
    .AddNewtonsoftJson(options =>
        options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
);

(You need to install the package Microsoft.AspNetCore.Mvc.NewtonsoftJson firstly for 3.0)

Refer to .net core 3 not having ReferenceLoopHandling in AddJsonOptions

How to create API endpoint to fetch Departments including Student in it?

3 Answers3