How can I use a local collection with Linq-to-SQL?

Question

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?

That's because Linq's lazy evaluation – shenhengbin Jan 05 '12 at 01:18 — shenhengbin, Jan 05 '12 at 01:18

score 1 · Accepted Answer · answered Jan 05 '12 at 01:55

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;
    }

This is exactly what Anthony suggested. I tried it and it didn't work. I later found out that it was because I had 2 similar queries running and the second one also needed the fix. Sorry Anthony! — Mark, Jan 05 '12 at 02:08

score 0 · Answer 2 · answered Jan 05 '12 at 01:33

0

I think you can do two linq joins and do the work with just one query as you would do on SQL. So the first two queries can go away.

answered Jan 05 '12 at 01:33

H27studio

467
1
4
12

Something like this: http://weblogs.thinktecture.com/cnagel/2008/02/linq-with-joins.html I dont know if for that data structure would work since LinQ queries can be tricky. But i think you can do: var courses = from c in ld_SqlContext.YT_TRAINING_COMPONENTs join ld_SqlContext.YT_STUDENT_COURSE_ENROLLMENT_STATUS on ce.STUDENT_EMPLOYEE_ID_NR.ToLower() equals sStudentId.ToLower() ... etc... As i said, it can be tricky to do joins on LinQ but it might work. – H27studio Jan 05 '12 at 09:29

How can I use a local collection with Linq-to-SQL?

2 Answers2