Sql – Linq Query Returns Incorrect Result Set

linqlinq-to-sqlnetsql server

I have a very complex Linq to SQL query that returns a result set from a Microsoft SQL Server database. The query is created using syntax similar to:

Dim db as MyDataContext = MyGetDataContextHelper()
Dim qry = From rslt in db.MyView Select ColumnList

If userParam1 IsNot Nothing Then
    qry = qry.Where(lambda for the filter)
End If

etc....

Return qry.ToList()

There are several user-specified filters to the query, including one that does a geographic radius search.

Here's the problem. I have a break set on the "ToList" call right at the end. When the break is hit I use the Linq to SQL Debug Visualizer to see the generated SQL statement. I copy that complex SQL statement into a SQL Server Management Studio query window and execute it against my database to get exactly the result set I want. So the generated SQL appears to produce the desired result. However, when I execute the "ToList" method of the query object the list returned has fewer rows and some different rows. I have also tried this using the DataContext log property writing to a file, with the same result. The query generates the correct result set in SQL Management Studio, but incorrect results from the ToList method.

How can that be? If the generated SQL is simply passed over the connection to the SQL Server shouldn't it generate exactly the result set I see in SQL Server Management Studio? I assume that I am misunderstanding something about the Linq to SQL mechanism, i.e. that it's not just a passthrough to SQL Server. Is that correct?

EDIT:
As per a request below, here is a much condensed version of the SQL that is generated by Linq, with most of the result columns removed for brevity. It produces the correct result in SQL Management Studio, but the result returned to my application is different.

SELECT [t3].[Id]
FROM (
    SELECT DISTINCT [t1].[Id]
    FROM (
        SELECT [t0].[Id], [t0].[ItemDate]
        FROM [dbo].[MySearchView] AS [t0]
        ) AS [t1]
    WHERE (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [dbo].[ZipCoverage] AS [t2]
        WHERE ([t2].[Id] = [t1].[Id]) 
        AND ([t2].[Latitude] >= (41.09046 - (0.5))) 
        AND ([t2].[Latitude] <= (41.09046 + (0.5))) 
        AND ([t2].[Longitude] >= (-73.43106 - (0.5))) 
        AND ([t2].[Longitude] <= (-73.43106 + (0.5))) 
        AND (ABS(3956.08833132861 * 2 * ATN2(SQRT(POWER(SIN((((CONVERT(Float,CONVERT(Float,0.0174532925199433))) * [t2].[Latitude]) - 0.717163818159029) / (CONVERT(Float,2))), 2) + (COS(0.717163818159029) * COS((CONVERT(Float,CONVERT(Float,0.0174532925199433))) * [t2].[Latitude]) * POWER(SIN((((CONVERT(Float,CONVERT(Float,0.0174532925199433))) * [t2].[Longitude]) - -1.28161377022951) / (CONVERT(Float,2))), 2))), SQRT((1 - POWER(SIN((((CONVERT(Float,CONVERT(Float,0.0174532925199433))) * [t2].[Latitude]) - 0.717163818159029) / (CONVERT(Float,2))), 2)) + (COS(0.717163818159029) * COS((CONVERT(Float,CONVERT(Float,0.0174532925199433))) * [t2].[Latitude]) * POWER(SIN(((CONVERT(Float,CONVERT(Float,0.0174532925199433))) * [t2].[Longitude]) / (CONVERT(Float,2))), 2))))) <= 5))) 
        AND ([t1].[ItemDate] <= '11/17/2009 8:12:42 PM')
    ) AS [t3]

UPDATE 2009-11-17 Was able to contact MS regarding this issue. Created a sample application which I submitted to their support rep. They have duplicated the issue and are researching. Will post answer when I get a response.

UPDATE 2009-12-21 Finally arrived at the correct answer with help from Microsoft. Please see my accepted answer below for the explanation.

Best Answer

Well, after some back and forth with a very helpful support rep from Microsoft, we finally arrived at the source of the problem. And unfortunately I did not furnish enough information in my original post for anyone here on SO to make the determination, so my apologies in that regard.

Here's the issue - as part of the code that constructs the LINQ query in question, I declared a .Net variable like so:

Dim RadCvtFactor As Decimal = Math.PI / 180

It turns out that when this is passed to SQL the parameter declaration, as evidenced in the LINQ log file, is DECIMAL( 29, 4 ). Due to the scale value on the declaration an invalid value is passed through to the RDBMS, resulting in the strange difference in query results.

Declaring the .Net variable as a Single value, like so:

Dim RadCvtFactor As Single = Math.PI / 180

completely corrects the problem.

The Microsoft rep acknowledged that this parameter conversion may be a "potential issue" and would consult the product team.

Thanks to everyone that submitted answers.

Related Topic