Linq Query Returns Incorrect Result Set

Question

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.

Answer 1

The one thing that immediately comes to mind is a permission issue. Is it possible that the program and the manually executed query are running under different credentials and hence have different access levels to the database? That can influence the results of the query.

Answer 2

I would begin by looking at your DataContext. If your DataContext isn't being updated from the SQL Server, then you may be returning an older version of the table.

DataContext maintains a state of the database when it was created. You want to be using a fresh context for each set of operations.

Answer 3

Another possibility is isolation level and the nature of the data. Are you using REPEATABLE READ or READ UNCOMMITTED or SNAPSHOT under Linq? What about when using SSMS? Obviously if the data is moving around then a lax isolation level will let you skip rows, read some rows twice, see the old version of a row, etc.

Also, can you give us a slightly better idea of what "the very complex query" looks like? You don't have to use your real table names.

Answer 4

You could use a DebuggerWriter to check the actual SQL sent to the server.

Answer 5

qry.ToList()

This statement creates and returns the list you want. You need to assign the result to something (such as a local variable) if you want to use the list later.

Edit: thanks for the update.

I suspect that there must be something you're not telling us that could also be a problem, and it might live here:

Dim db as MyDataContext = MyGetDataContextHelper()

Does this method connect to the same database as the one you connected to when you used sql studio?

• Check the Connection property of the datacontext.
• Make sure the query is issued to the database by watching for it with the sql profiler.
• Issue a very simple query and confirm that it returns correct results.

Answer 6

It may sound silly but always a good one to check, are you connecting to the same database environment in SSMS that you are from your application? :)

Answer 7

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.