Linq to Sql: Help with InvalidCastException in my query

Question

I have written a SQL query that works just fine, but am having a little trouble with the conversion to LINQ. Here is the SQL:

SELECT        
  CourseID, 
  CourseName, 
  CreditHours, 
  CPTRequired, 
  COTRequired, 
  CPTElective, 
  COTElective
FROM            
  Courses
WHERE        
  (CPTRequired = 'true') 
  AND 
  (CourseID NOT IN
    (SELECT        
      Courses_1.CourseID
    FROM
      Courses AS Courses_1 INNER JOIN
      Sections ON Sections.CourseID = Courses_1.CourseID INNER JOIN
      Enrollment ON Enrollment.SectionID = Sections.SectionID INNER JOIN
      Students ON Students.StudentID = Enrollment.StudentID
    WHERE        
      (Students.StudentID = '11110004')))

And here is what I have written so far with LINQ:

Dim maj = (From c In connect.Courses _
           Where c.CPTRequired = "True" _
           Select c.CourseID, c.CourseName, c.CreditHours).Except _
          (From en In connect.Enrollments _
           Join s In connect.Sections On en.SectionID Equals s.SectionID _
           Join cs In connect.Courses On s.CourseID Equals cs.CourseID _
           Join st In connect.Students On en.StudentID Equals st.StudentID _
           Order By cs.CourseName _
           Where st.StudentID = StudentID _
           Select cs.CourseID)

When executed the LINQ statement throws the following errors.

System.InvalidCastException was unhandled Message="Unable to cast object of type 'System.Data.Linq.DataQuery'1[System.String]' to type 'System.Collections.Generic.IEnumerable'1 [VB$AnonymousType_6'3[System.String,System.String,System.Nullable`1[System.Int32]]]'." Source="Final Project"

What am I missing? I am brand new to LINQ, so please be gentle!!

Answer 1

I can't say for sure because the VB syntax has thrown me off, but I believe it is because in the statement prior to the except you are selecting a new anonymous object (with multiple properties) and in the query being passed to Except you are only selecting a single column. So it is unable to compare the types properly.

Effectively it appears that you are trying to compare:

c.CourseID, c.CourseName, c.CreditHours

with

c.CourseID

and LINQ doesn't know how to do that properly.. It may work if you expand the secondary select it include c.CourseName and c.CreditHours

Making it:

Dim maj = (From c In connect.Courses _
           Where c.CPTRequired = "True" _
           Select c.CourseID, c.CourseName, c.CreditHours).Except _
          (From en In connect.Enrollments _
           Join s In connect.Sections On en.SectionID Equals s.SectionID _
           Join cs In connect.Courses On s.CourseID Equals cs.CourseID _
           Join st In connect.Students On en.StudentID Equals st.StudentID _
           Order By cs.CourseName _
           Where st.StudentID = StudentID _
           Select cs.CourseID, cs.CourseName, cs.CreditHours)

But again i'm not all that familiar with the VB syntax so best I can say is give it a shot.

Edit: Moving Context logging code from comment to answer..

Dim sb As New StringBuilder
Dim sw As New StringWriter(sb)
connect.Log = sw
...Do Query & Enumerate Results...
sb.ToString() 'will contain the generated SQL

Answer 2

I think you cannot have the types in your selects be different for Except to work:

Select c.CourseID, c.CourseName, c.CreditHours vs

Select cs.CourseID

I think LINQ will compare the whole selected result and compare it. comparing these unlike results should fail at compile time. ???

Answer 3

I think the query that you're trying to call Except on hasn't been run yet. Try adding a .toList(), or some other conversion that gives you an IEnumerable, on the end of:

Dim maj = (From c In connect.Courses _ Where c.CPTRequired = "True" _ Select c.CourseID, c.CourseName, c.CreditHours)

Then call Except on the result.

Answer 4

How about

Dim maj = (From c In connect.Courses _
       Where c.CPTRequired = "True" &&_
       !(From en In connect.Enrollments _
       Join s In connect.Sections On en.SectionID Equals s.SectionID _
       Join cs In connect.Courses On s.CourseID Equals cs.CourseID _
       Join st In connect.Students On en.StudentID Equals st.StudentID _
       Order By cs.CourseName _
       Where st.StudentID = StudentID _
       Select cs.CourseID).Contains(c.CourseID) _
       Select c.CourseID, c.CourseName, c.CreditHours)

In other words...

from x in y where !(from a in b select a.foo).Contains(x.foo) select x