Reading data from one-to-many relationship SQL table into my model

Question

I have below two classes which represent my SQL tables (generated by ADO.net entity data model):

public partial class Parent
{
    public Parent()
    {
        this.Students = new HashSet<Student>();
    }

    public int Parent_Id { get; set; }
    public string Parent1_Last_Name { get; set; }
    public string Parent1_First_Name { get; set; }
    public virtual ICollection<Student> Students { get; set; }
}

public partial class Student
{
        public int Student_Id { get; set; }
        public string Student_Last_Name { get; set; }
        public string Student_First_Name { get; set; }
        public int Parent_Id { get; set; }
        public virtual Parent Parent { get; set; }
}

Now I am trying to read the data from my tables in a method as below:

var parent = new Parent();
var students = new List<Student>();

using (var dc = new SchoolDataEntities())
{
    students = dc.Students.Where(s => s.Parent_Id.Equals(parentId)).ToList();
    parent = dc.Parents.Where(p => p.Parent_Id.Equals(parentId));
    parent.Students = students;     
}

I am getting a run-time "Invalid Cast" exception error when I reading from Parent table (Students table is working fine).

Error: System.InvalidCastException: 'Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[SchoolApplication.Parent]' to type 'SchoolApplication.Parent'.'

Someone please educate me what I was doing wrong?

You need to materialize the query with `dc.Parents.Where(p => p.Parent_Id.Equals(parentId)).FirstOrDefault()` or `dc.Parents.Where(p => p.Parent_Id.Equals(parentId)).SingleOrDefault()`. — Tetsuya Yamamoto, Jun 09 '17 at 01:36

score 0 · Accepted Answer · answered Jun 09 '17 at 01:38

0

This part is your problem:

parent = (Parent)dc.Parents.Where(p => p.Parent_Id.Equals(parentId));

The output of "Where" is a collection of Parent rather than just a single parent.

You can use "Single" or "First" rather than "Where" in this case.

answered Jun 09 '17 at 01:38

Daniel Keogh

233
1
7

score 0 · Answer 2 · answered Jun 09 '17 at 01:42

This part of code returns IQueryable collection instead of single Parent model:

parent = dc.Parents.Where(p => p.Parent_Id.Equals(parentId));

For this reason, you need to materialize the query to Parent model with either FirstOrDefault() or SingleOrDefault():

parent = dc.Parents.Where(p => p.Parent_Id.Equals(parentId)).FirstOrDefault();

parent = dc.Parents.Where(p => p.Parent_Id.Equals(parentId)).SingleOrDefault();

Related: Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[]' using linq lambda expression

Reading data from one-to-many relationship SQL table into my model

2 Answers2