EF Core查询中StudentCourses转ICollection遇类型转换错误求解决

问题场景 #

编写ASP.NET Core Web API接口时，尝试构造包含学生选课列表（ICollection<StudentCourse>）的Student对象，遇到类型转换错误。

原接口代码

// GET: api/Students
[HttpGet]
public async Task<ActionResult<IEnumerable<Student>>> GetStudents()
{            
    var resultOfQuery = from s in _context.Students
                        join sc in _context.StudentCourses on s.StudentId equals sc.StudentId
                        join c in _context.Courses on sc.CourseId equals c.CourseId
                        select new Student
                        {
                            StudentId = s.StudentId,
                            Lname = s.Lname,
                            StudentCourses = new StudentCourse { Course = new Course { CourseTitle = c.CourseTitle, CreatedDt = c.CreatedDt } } // 此处报错
                        };
    return await resultOfQuery.ToListAsync();
}

错误信息

Cannot implicitly convert type 'StudentAPI.Models.StudentCourse' to 'System.Collections.Generic.ICollection<StudentAPI.Models.StudentCourse>'

模型定义

public class Student 
{
    public Student()
    {
        StudentCourses = new HashSet<StudentCourse>();
    }

    public int StudentId { get; set; }
    public string Fname { get; set; }
    public string Lname { get; set; } = null!;

    public virtual ICollection<StudentCourse> StudentCourses { get; set; }
}
    
// 中间表
public partial class StudentCourse
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public DateTime? CreatedDt { get; set; }

    public virtual Course Course { get; set; } = null!;
    public virtual Student Student { get; set; } = null!;
}
//

public class Course
{
    public Course()
    {
        StudentCourses = new HashSet<StudentCourse>();
    }

    public int CourseId { get; set; }
    public string CourseTitle { get; set; } = null!;

    public virtual ICollection<StudentCourse> StudentCourses { get; set; }
}

解决方案 #

错误核心是StudentCourses为ICollection<StudentCourse>类型，但代码中直接赋值了单个StudentCourse对象。需按学生分组，为每个学生构造完整的选课集合，以下两种方法均可解决：

方法1：手动分组构造集合

先通过StudentId分组查询结果，再为每个学生构建选课列表：

[HttpGet]
public async Task<ActionResult<IEnumerable<Student>>> GetStudents()
{            
    // 先查询关联数据，暂存为匿名类型
    var rawQuery = from s in _context.Students
                   join sc in _context.StudentCourses on s.StudentId equals sc.StudentId
                   join c in _context.Courses on sc.CourseId equals c.CourseId
                   select new 
                   {
                       s.StudentId,
                       s.Lname,
                       sc.CourseId,
                       sc.CreatedDt,
                       CourseTitle = c.CourseTitle
                   };

    // 按学生分组，构造Student对象及选课集合
    var result = await rawQuery.GroupBy(item => item.StudentId)
        .Select(group => new Student
        {
            StudentId = group.Key,
            Lname = group.First().Lname,
            StudentCourses = group.Select(item => new StudentCourse
            {
                CourseId = item.CourseId,
                CreatedDt = item.CreatedDt,
                Course = new Course
                {
                    CourseTitle = item.CourseTitle
                }
            }).ToList()
        }).ToListAsync();

    return result;
}

方法2：利用EF Core导航属性（推荐）

如果DbContext已正确配置多对多导航关系，直接用Include和ThenInclude加载关联数据，代码更简洁：

[HttpGet]
public async Task<ActionResult<IEnumerable<Student>>> GetStudents()
{            
    var students = await _context.Students
        // 加载学生的选课记录，以及对应的课程信息
        .Include(s => s.StudentCourses)
            .ThenInclude(sc => sc.Course)
        // 构造返回的Student对象，筛选所需字段
        .Select(s => new Student
        {
            StudentId = s.StudentId,
            Lname = s.Lname,
            StudentCourses = s.StudentCourses.Select(sc => new StudentCourse
            {
                CourseId = sc.CourseId,
                CreatedDt = sc.CreatedDt,
                Course = new Course
                {
                    CourseTitle = sc.Course.CourseTitle
                }
            }).ToList()
        }).ToListAsync();

    return students;
}

内容的提问来源于stack exchange，提问作者Cedric VH