One to Many relationship issue in .net core web api

Viewed 54

I have two tables in my database Student and Course. Using these two tables, I have created a relationship like one student can have multiple courses.

Student table :

Id |    FirstName  | LastName  |    AddressNo | City      | CourseId
----------------------------------------------------------------
1  |  Sandanuwan     Dharmarathna      52      Kurunegala      1

Course table :

Id |   CourseName
--------------------
1  |  English Course

in my ASP.NET Core Web API, I have created two model classes called Student and Course:

public class Student
{
    public int Id { get; set; }
    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;
    public string AddressNo { get; set; } = null!;
    public string City { get; set; } = null!;       
    public List<Course> Courses { get; set; }
}

public class Course
{
    public int Id { get; set; }
    public string CourseName { get; set; } = null!;       
}

Now I want to get response like this

{
  "data": [
    {
      "id": 0,
      "firstName": "string",
      "lastName": "string",
      "addressNo": "string",
      "city": "string",
      "country": "string",
      "street": "string",
      "grade": "string",
      "className": "string",
      "courses": [
        {
          "id": 0,
          "courseName": "string"
        }
      ]
    }
  ],
  "success": true,
  "message": "string"
}

But after call get function I am getting this response. It's always sending empty array in payload.

{
  "data": [
    {
      "id": 1,
      "firstName": "Sandanuwan",
      "lastName": "Dharmarathna",
      "addressNo": "52",
      "city": "Kurunegala",
      "country": "Sri Lanka",
      "street": "Piduruwella",
      "grade": "10",
      "className": "B",
      "courses": []
    }
  ],
  "success": true,
  "message": "Successfully received all Students details"
}

This is my GET method:

public async Task<ServiceResponse<List<GetStudentDto>>> GetAllStudents()
{
    var response = new ServiceResponse<List<GetStudentDto>>();
    var dbStudents = await _dataContext.Student.ToListAsync();
    response.Data = dbStudents.Select(c => _mapper.Map<GetStudentDto>(c)).ToList();
    response.Message = "Successfully received all Students details";
    return response;
}

GetStudentDto class

public class GetStudentDto
{
        public int Id { get; set; }
        public string FirstName { get; set; } = null!;
        public string LastName { get; set; } = null!;
        public string AddressNo { get; set; } = null!;
        public string City { get; set; } = null!;
        public string Country { get; set; } = null!;
        public string Street { get; set; } = null!;
        public string Grade { get; set; } = null!;
        public string ClassName { get; set; } = null!;
        public List<GetCourseDto> Courses  { get; set; }
}

public class GetCourseDto
{
    public int Id { get; set; }
    public string CourseName { get; set; } = null!;
}

Errors


Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'CourseStudent'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---

Db Context Class

public partial class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> options): base(options)
        {
        }

        public virtual DbSet<Course> Course { get; set; } = null!;
        public virtual DbSet<Student> Student { get; set; } = null!;        
    }

enter image description here

1 Answers

include courses into student instance

 var dbStudents = await _dataContext.Student
                                     .Include(i=>i.Courses)
                                     .ToListAsync();

and fix Course class

public class Course
{
    public int Id { get; set; }
    public string CourseName { get; set; } = null!;  
    public virtual List<Student> Students { get; set; }     
}

you have to repeat ef migration to database. New table will be created

public class CourseStudent
{
    public int CourseId { get; set; }
    public virtual Course Course { get; set; } 
    public int StudentId { get; set; }
    public virtual Student Student { get; set; } 
}
Related