How can I make this LinqToSQL query work? (SqlExecption)

Question

The following code gives me an SqlException: Invalid object name 'dbo.studentsCourses'

OO theCourse = subject.Course;
var students = dc.studentsCourses.Where(x => x.course == theCourse).Select(x => x.student);

I tried the following code instead but I also get an Exception. My original question was asked on Aardvark and can be read bellow:

var allStudents = from s in dc.students select s;
List thestudents = new List();
foreach (student s in allStudents)
{
     if (s.courses.Contains(theCourse))
     {
     thestudents.Add(s);
     }
}

I did a right click, "run custom tool" on my dbml and checked my names of my tables and entities. The project compiles but I get an Exception at runtime on this line: "if (s.courses.Contains(theCourse))" Any ideas?

Original question on Aardvark:

How do I do a LinqToSQL query that gives me this: I want to select all students that attended a certain lesson. The lesson is from a certain course. So select the course the lesson is from. Now select all the students that are following that course. There is a many-to-many relationship between the students and the courses table in my DB. I already extended my LINQ entities to be able to select student.Courses and course.Students using this method: http://www.codeproject.com/KB/linq/linq-to-sql-many-to-many.aspx

Answer 1

Your link to sql classes don't match your db schema or your db does not contain a table or view called studentcourses. You need to adjust either your classes or db so they match.

Answer 2

You could start debugging this problem by visualizing the query that is generated by the LinqToSQL. The Gu has written a blogpost on this a while ago: http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

Just copy/paste the query in your favourite database management application and run it against the database. It should become clear what the error is. If there are still some crazy things happening, just update your question?

Hope this helps!

Answer 3

First check your database to see if there is really a table or view name studentsCourses. If there is then try to regenerate to dbml file and then try again.

Answer 4

hi

I'm not sure... but you may try this one:

var xxx = dc.Include("studentsCourses") .studentsCourses .Where(x => x.course == theCourse) .Select(x => x.student) .ToList();