adding a second table to the web api in asp.net

Question

I have created a simple web api project using Entity Framework on AP.Net 2013. It performs basic CRUD. The project is connected to a SQL Server database with 2 tables - Students and Subjects. These two tables are connected with the Roll number being a PK in Students and FK in Subjects.

The project works fine when I am accessing only the Students. It gives me a NotSupportedException when I add the code to handle the subjects also.

I am running this app in the Fiddler.

My code is as below:

private StudentDBEntities db = new StudentDBEntities();

// GET: api/Student
public IList<SubjectDetailsDto> GetStudents()
{
    return db.Students.Select(s => new SubjectDetailsDto
    {
        Roll_Number = s.Roll_Number,
        FirstName = s.FirstName,
        LastName = s.LastName,
        Class = s.Class,
        Gender = s.Gender,
        subjects = s.subjects.ToList()
    }).ToList();
}

Can anyone tell me why the exception is occurring?

EDIT: SubjectDetailsDto.cs

public class SubjectDetailsDto
{
    public int Roll_Number { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Class { get; set; }
    public string Gender { get; set; }
    public List<Subject> subjects { get; set; }
}

I have added a snapshot of the exception:

EDIT

Student.cs

    public partial class Student
    {
      public int Roll_Number { get; set; }
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public int Class { get; set; }
      public string Gender { get; set; }

      public ICollection<Subject> subjects { get; set; }
   }

Subject.cs

public partial class Subject
{
    public int Roll_Number { get; set; }
    public int English { get; set; }
    public int Mathematics { get; set; }
    public int Science { get; set; }
    public Nullable<int> Total { get; set; }

    //navigation property
    public Student student { get; set; }
}

is this line generate error ? subjects = s.subjects.ToList() — , Nov 27 '15 at 07:08
I did not seed the database. It is not a localDB. I connected to an external SQL server with the data. — Pallavi Prasad, Nov 27 '15 at 07:21
Also it would be helpful to have the full exception detail rather than just the type — ceej, Nov 27 '15 at 07:40
possible duplicate http://stackoverflow.com/questions/11584660/the-specified-type-member-is-not-supported-in-linq-to-entities-only-initializer — Amit Kumar Ghosh, Nov 27 '15 at 08:40
I am not using Javascript or any flavor of it. I am also not using HTML to display. I am getting the output in Fiddler in JSON format — Pallavi Prasad, Nov 27 '15 at 08:57
Can you also post your `Student` and `Subject` classes, along with any other relevant code? — ceej, Nov 27 '15 at 10:38

score 0 · Answer 1 · answered Nov 27 '15 at 08:52

The root of the problem is the fact that Entity Framework doesn't know how to translate Enumerable.ToList() into SQL code that will load the data. The culprit line is this one:

subjects = s.subjects.ToList()

In order to avoid this you could join the tables or load the data in two separate requests:

var students = db.Students.Select(s => {}).ToList();
var studentIds = students.Select(s=>s.Id).ToArray();
var subjects = db.Subjects.Where(s=>studentIds.Contains(s.StudentId))
    .ToArray()
    .GroupBy(s=>s.StudentId)
    .ToDictionary(g=>g.Key, g=>g.ToList());
//Populate subjects for each student
students.ForEach(s=>
{
    s.subjects = subjects.ContainsKey(s.Id)?subjects[s.Id]:new List<Subject>();
});

I used your code. The error comes in the first line. `var students = db.Students.Select(s => new SubjectDetailsDto {}).Tolist();` — Pallavi Prasad, Nov 27 '15 at 09:15

adding a second table to the web api in asp.net

1 Answers1