I read this question which is the same issue I'm having. Unfortunately, the marked solution didn't help. I'm probably misunderstanding something really obvious about LINQ.
I'm trying to do a reverse lookup of sorts. I need to find all courses that a student is enrolled in.
Here's the code...
public static IQueryable GetCoursesByStudent(string sStudentId)
{
Ld_Sql_ServerDataContext ld_SqlContext = new Ld_Sql_ServerDataContext();
// course-lesson IDs
var activityEnrollmentIds = from ce in ld_SqlContext.YT_STUDENT_COURSE_ENROLLMENT_STATUS
where ce.STUDENT_EMPLOYEE_ID_NR.ToLower() == sStudentId.ToLower()
select ce.TRAINING_ACTIVITY_ID;
// lesson parent course IDs
var parentIds = from c in ld_SqlContext.YT_TRAINING_COMPONENT_RLTNPs
where activityEnrollmentIds.Contains(c.TRAINING_ACTIVITY_ID)
select c.PARENT_TRAINING_ACTIVITY_ID;
// filtered list of courses
var courses = from c in ld_SqlContext.YT_TRAINING_COMPONENTs
where c.TRAINING_ACTIVITY_TYPE_DC == "Course" &&
(activityEnrollmentIds.ToList().Contains(c.TRAINING_ACTIVITY_ID)
|| parentIds.ToList().Contains(c.TRAINING_ACTIVITY_ID))
select c;
return courses;
}
I'm databinding the results to a an ASP:ListBox and the following error gets thrown on DataBind()...
System.NotSupportedException: Queries with local collections are not supported.
Anybody know what's going on?
Can you try this ?
I think you should convert activityEnrollmentIds and parentIds into List before use it.
public static IQueryable GetCoursesByStudent(string sStudentId)
{
Ld_Sql_ServerDataContext ld_SqlContext = new Ld_Sql_ServerDataContext();
// course-lesson IDs
var activityEnrollmentIds = (from ce in ld_SqlContext.YT_STUDENT_COURSE_ENROLLMENT_STATUS
where ce.STUDENT_EMPLOYEE_ID_NR.ToLower() == sStudentId.ToLower()
select ce.TRAINING_ACTIVITY_ID).ToList();
// lesson parent course IDs
var parentIds = (from c in ld_SqlContext.YT_TRAINING_COMPONENT_RLTNPs
where activityEnrollmentIds.Contains(c.TRAINING_ACTIVITY_ID)
select c.PARENT_TRAINING_ACTIVITY_ID).ToList();
// filtered list of courses
var courses = from c in ld_SqlContext.YT_TRAINING_COMPONENTs
where c.TRAINING_ACTIVITY_TYPE_DC == "Course" &&
(activityEnrollmentIds.Contains(c.TRAINING_ACTIVITY_ID)
|| parentIds.Contains(c.TRAINING_ACTIVITY_ID))
select c;
return courses;
}