none
LINQ to SQL bug RRS feed

  • Question

  • Hello, I've found a bug in LINQ to SQL. I'm not sure where to report so I'll put it here, if this is wrong please point me in the right direction.

    We have a small application to help manage high school student enrollments in an online course content management system and one of our reports wasn't working. An application-thrown exception was occurring because the query below was not returning any results ("Enumeration yielded no results.").

    Here's the LINQ query in my C# code:

                    var courseList = from c in db.Courses
                                     where c.SchoolID == schoolID
                                     && c.DistrictID == districtID
                                     && c.SchoolYear == schoolYear
                                     orderby c.CourseID, c.InstructorID
                                     select new
                                     {
                                         c.CourseID,
                                         c.CourseName,
                                         c.Instructor.LastName,
                                         c.Instructor.FirstName,
                                         c.InstructorID,
                                         c.CallNumber
                                     };

    Where schoolID, districtID, and schoolYear are all Strings declared and filled with user input earlier, I checked while debugging and they hold perfectly valid values.

    Here's the raw T-SQL for that query, found using the VS2008 debugger (I've formatted it a bit for clarity, the underlined code is the error):


    SELECT
        [t0].[CourseID],
        [t0].[CourseName],
        [t1].[LastName],
        [t1].[FirstName],
        [t0].[InstructorID],
        [t0].[CallNumber]

    FROM
        [dbo].[Course] AS [t0]
            INNER JOIN [dbo].[Instructor] AS [t1] ON
                ([t1].[DistrictID] = [t0].[SchoolID] )
                AND ([t1].[SchoolID] = [t0].[DistrictID] )
                AND ([t1].[InstructorID] = [t0].[InstructorID])

    WHERE
        ([t0].[SchoolID] = @p0)
        AND ([t0].[DistrictID] = @p1)
        AND ([t0].[SchoolYear] = @p2)

    ORDER BY [t0].[CourseID], [t0].[InstructorID]



    Here's a link to a Visio diagram of our database model. Our LINQ data context is a direct translation of this.
    http://img524.imageshack.us/img524/1043/database.png

    Here's the bug that I've found. In the LINQ code, I'm comparing c.SchoolID to schoolID and c.DistrictID to districtID. However, in the T-SQL (Look in the Inner Join clause) it's comparing a DistrictID field to a SchoolID field, and the line below that vice versa. Naturally, since those fields are not supposed to be compared, the query returns no results. Apparently LINQ is not generating the proper query and it's messing up one of our reports.

    I'm positive I can rewrite the LINQ and break it into multiple queries to get it to work, I'm sure this is just an obscure case of me doing too much in one query. However this is the first Microsoft bug (I'm assuming) that I've run into so I feel the need to report it.

    Anyway, all of our client machines run Windows XP SP3, .NET 3.5 SP1 (This app is a WPF application) and our server runs Windows Server 2003 with SQL Server 2005. I'm using VS2008 SP1 and C#. The problem does occur on multiple client machines.
    • Changed type David M Morton Friday, September 4, 2009 7:09 PM
    • Moved by eryang Tuesday, September 8, 2009 5:34 AM (From:.NET Base Class Library)
    Friday, September 4, 2009 6:56 PM

Answers

  • That's a join between Course and Instructor. It's attempting to join the two tables on the foreign keys. I'd bet either your relationships in your database are incorrect (and thus when the LINQ designer generated the code it did it "wrong") or the relationships in your DBML file are wrong (as in someone defined them incorrectly).
    • Marked as answer by mott555 Friday, September 4, 2009 7:16 PM
    Friday, September 4, 2009 7:08 PM
  • As usual, posting on MSDN after being frustrated helps me find the problem and realize it had nothing to do with what I thought.

    Originally, SQL Server would not allow us to define a foreign key relationship between Instructor and Course, it would claim that the fields don't match an existing primary key on Instructor while in actuality they would. (SQL Server seems to hate databases with composite primary keys and chains of foreign keys using them, just in my experience anyway.) So to get by we created a relationship in our DBML to enforce a foreign key relationship in code...and yeah we messed up and Course.DistrictID pointed to Instructor.SchoolID and Course.SchoolID pointed to Instructor.DistrictID.

    Not a LINQ bug...possibly an SQL Server bug at the very beginning though.
    • Marked as answer by mott555 Friday, September 4, 2009 7:16 PM
    Friday, September 4, 2009 7:16 PM

All replies

  • That's a join between Course and Instructor. It's attempting to join the two tables on the foreign keys. I'd bet either your relationships in your database are incorrect (and thus when the LINQ designer generated the code it did it "wrong") or the relationships in your DBML file are wrong (as in someone defined them incorrectly).
    • Marked as answer by mott555 Friday, September 4, 2009 7:16 PM
    Friday, September 4, 2009 7:08 PM
  • As usual, posting on MSDN after being frustrated helps me find the problem and realize it had nothing to do with what I thought.

    Originally, SQL Server would not allow us to define a foreign key relationship between Instructor and Course, it would claim that the fields don't match an existing primary key on Instructor while in actuality they would. (SQL Server seems to hate databases with composite primary keys and chains of foreign keys using them, just in my experience anyway.) So to get by we created a relationship in our DBML to enforce a foreign key relationship in code...and yeah we messed up and Course.DistrictID pointed to Instructor.SchoolID and Course.SchoolID pointed to Instructor.DistrictID.

    Not a LINQ bug...possibly an SQL Server bug at the very beginning though.
    • Marked as answer by mott555 Friday, September 4, 2009 7:16 PM
    Friday, September 4, 2009 7:16 PM