Use Linq to return multiple values using DTO

Question

//This is student model
namespace StudentApp.Models
{
    public class Student
    {
        [Key]
        public int studentId { get; set; }
        public List<AssignedSupervisor> assigned_supervisorId { get; set; }
    }
    public class AssignedSupervisor
    {
        [Key]
        public int supervisorId { get; set; }
    }
}

Based on the Student Model, entity framework has created the below db

//Here is the sample db structure
//Table Students
studentId
---------
    1
    2
//Table Supervisors
supervisorId | studentId
------------   ---------
     101           1
     102           1
     105           2

studentId is a foreign key in Supervisor Table. Students can have one or more supervisors assigned. Required output should be similar to the below example:

{
  "studentId": "1",
  "supervisorId": [101,102]
},
{
  "studentId": "2",
  "supervisorId": [105]
}

Below is the DTO

//This is student DTO
namespace StudentApp.DTOs
{
    public class StudentDTO
    {
        public int studentId { get; set; }
        public List<AssignedSupervisor> assigned_supervisorId { get; set; }
    }
}

In my controller

namespace ContractingApp.Controllers
{
    //first way I'm doing which returns ugly JSON.
    public List<StudentDTO> GetStudents()
    {
        return db.Students.Select(s => new StudentDTO()
        {
            studentId = s.studentId,
            //Not sure how to use the select statement with it
            assigned_supervisorId = s.Supervisors.Where(
                u => u.studentId == s.studentId
                ).ToList()
        }).ToList();
    }

    //Another way I have tried after the suggestions by @darth_phoenixx I'm trying which doesn't return correct correct format either.
    //It returns 3 JSON objects - one with each supervisor ID.
    public List<dynamic> GetStudents()
    {
        var students = (from t1 in db.Students
                        join t2 in db.Supervisors on t1.studentId equals t2.studentId
                        select new
                        {
                           t1.studentId,
                           t2.supervisorId
                        });
        return students.ToList<dynamic>();
    }
}

Any article or direction to solve will be helpful.

Do you have a table containing the details of the supervisors? If so I think you probably want to look into a join - joining the students table to the supervisors table and getting the supervisor out of the db as well based on the `supervisor_internal_id`. Take a look at [http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql](http://stackoverflow.com/questions/37324/what-is-the-syntax-for-an-inner-join-in-linq-to-sql) — darth_phoenixx, Apr 09 '16 at 09:14
@darth_phoenixx - Thanks for the help and link. I tried using Join but it doesn't return the JSON in the expected format. I have updated my question with an example of the required format. Please have look. I'm happy to provide more info if required. The biggest trouble I'm facing is to find the correct words to explain it. :) — sandiejat, Apr 12 '16 at 06:25

score 0 · Accepted Answer · edited May 23 '17 at 10:28

I have found a hint on stackoverflow. Thanks to @Aducci for answering a question on Mapping Linq Query results to a DTO class

The issue was with my DTOs. The correct DTO should be:

//SupervisorId is in another DTO and has the correct data type as it is in the DB.
namespace StudentApp.DTOs
{
    public class StudentDTO
    {
        public int studentId { get; set; }
        public IEnumerable<SupervisorDTO> assigned_supervisorId { get; set; }
        //assigned_supervisorId in Student Model needs to be IEnumerable to work with this change in DTO.
    }
    //Separate DTO for supervisor
    public class SupervisorDTO
    {
        public int supervisorId { get; set; }
    }
}

Once the DTO was sorted, the stackoverflow answer helped in getting the 'chaining' of the queries done. For every query, the related DTO needs to be used. And here is the final controller in my WebAPI. (db is the object of the Entity Framework)

public IEnumerable<StudentDTO> GetStudents()
{
    return (from t1 in db.Students
            select new StudentDTO()
            {
                studentId = t1.StudentId,
                assigned_supervisorId = (from t2 in db.Supervisors
                                where t1.StudentId == t2.StudentId
                                select new SupervisorDTO() // Supervisor DTO needs to be used here
                                {
                                    supervisorId = t2.supervisorId
                                })
            });
}

Use Linq to return multiple values using DTO

1 Answers1