Repository pattern issue with API controller querying of intermediate table

Question

Here is Repository, in which I created a methods in which I needed the data on the basis of intermediate table.

public Task<IEnumerable<DepartmentSchool>> GetAllDepartmentBySchoolIdAsync(int schoolId)
    {
        var school = _GpsContext.School.Where(e => e.ID == schoolId).FirstOrDefaultAsync();
        if (school == null)
            return NotFound();
        var departments = _GpsContext.DepartmentSchool
            .Where(e => e.SchoolsId == schoolId).Select(e => e.DepartmentID);
        return Ok(departments);

    }

And the method is called from my controller class to get value on the basis of the methods.

[Route("api/[controller]")]
[ApiController]
public class DepartmentSchoolController : ControllerBase
{
    private readonly IDepartmentSchoolRepository _departmentSchoolRepository;
    public DepartmentSchoolController(DepartmentSchoolRepository departmentSchoolRepository)
    {
        _departmentSchoolRepository = departmentSchoolRepository;
    }

    /// <summary>
    /// Calling depatment on the basis of school id. 
    /// </summary>
    /// <param name="schoolId"></param>
    /// <returns></returns>
    [HttpGet("school/{schoolId}/departments")]
    public async Task<IEnumerable<DepartmentSchoolRepository>> GetDepartmentsFromSchool(int schoolId)
    {
        return await _departmentSchoolRepository.GetAllDepartmentBySchoolIdAsync();
    }

Can, please tell me where is the problem. Above code is not working and also showing problem in NotFound() and Ok() 'does not exist in the current context', what I have need to do?

Here is related entity:

public class DepartmentSchool
{
    public int Id { get; set; }
    public int DepartmentID { get; set; }
    [ForeignKey("DepartmentID")]
    public virtual Department Department { get; set; }
    public int SchoolsId { get; set; }
    [ForeignKey("SchoolsId")]
    public virtual Schools Schools { get; set; }
}

public partial class Schools
{
    public int ID { get; set; }
    public DateTime UpdatedAt { get; set; }

}

public partial class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int SchoolSystemsID { get; set; }
}

Above code is not working and also showing problem in NotFound() and Ok() 'does not exist in the current context', what I have need to do? — Vipin Jha, Feb 07 '19 at 10:26

StuartLC · Answer 1 · 2019-04-10T11:48:41.190

Your code has a bunch of issues, including that it doesn't compile.

In your controller:

You don't want to be returning repositories from your controller. Since you're using .net-core, why not use the IActionResult type as per the docs

[HttpGet("school/{schoolId}/departments")]
public async Task<IActionResult> GetDepartmentsFromSchool(int schoolId)
{
    var departments = await _departmentSchoolRepository
        .GetAllDepartmentBySchoolIdAsync(schoolId);
    if (departments.Any())
    {
        return Ok(departments)
    }
    return NotFound();
}

In your repository:

You're not awaiting either of your EF calls. As such, both these calls would return a Task instead of the EF Entity types that you are expecting.
From the names of the methods, it seems you want to project out all departments from a given school. To do this, use the navigation properties you've defined in your EF model.
You'll need to materialize the second query and change the return type (.ToListAsync())
You'll also need to mark the method as async
Don't use HTTP / Web Api stuff like Ok / NotFound in a repository layer - this should be reserved for your controller layer.
For your own sanity, in LINQ, use lambda variable names which represent the type that they represent. e.g. I've used ds below for DepartmentSchool, and s for School, below.

// ** Return type is the projected result
public async Task<IEnumerable<Department>> GetAllDepartmentBySchoolIdAsync(int schoolId) // ** async
{
    var school = await _GpsContext.School // ** await 
       .Where(s => s.ID == schoolId)
       .FirstOrDefaultAsync(); 
    if (school == null)
        return Enumerable.Empty<Department>(); // Avoid returning nulls
    var departments = await _GpsContext.DepartmentSchool // ** await
        .Where(ds => ds.SchoolsId == schoolId)
        .SelectMany(ds => ds.Department) // ** flatten and project the navigation property
        .ToListAsync(); // ** materialize
    return departments; // ** Don't use Http stuff like OK in Repo layer
}

Note also that your first query is actually redundant. If you have referential integrity enforced in your database, it is impossible to have a DepartmentSchool junction row without a linked School, so you should be able to look just for the DepartmentSchool rows.

In the above, I've assumed that you've got lazy loading enabled. If you don't you'll need to eager load (i.e. join) the DepartmentSchool -> Department navigation as per here.

Repository pattern issue with API controller querying of intermediate table

1 Answers1