Take the 2-minute tour ×

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


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 [t0].[Id], [t0].[ItemDate]
        FROM [dbo].[MySearchView] AS [t0]
        ) AS [t1]
        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.

share|improve this question
Could you please post your actual LINQ query? You said it was very complex. Given that everything else seems to be in order, the remaining option is that your LINQ query contains runtime-only processing that the translator could not turn into SQL. –  jrista Nov 18 '09 at 3:32
I responded to your comment on my answer below. –  Benjamin Autin Nov 18 '09 at 13:13
Is there no ORDER BY? How many rows are we talking about, and how are you validating that the results are different? Is it possible that the order is different and making it look like the results are different? –  Aaron Bertrand Nov 18 '09 at 14:18
There is an ORDER BY clause, which I removed for brevity. I am validating the results by count of rows - thirteen for the query in SQL Management Studio and eight for the ToList return from Linq - and by the ID column, some of which are the same and some of which are different. –  Bob Mc Nov 18 '09 at 17:14
Have you verified that the query the debug output is showing you is actually what Linq is sending to the server? I would run a quick trace to see what is actually being passed, and then compare that to what you are running in Management Studio. Other than that, I don't have any other suggestions, sorry. –  Aaron Bertrand Nov 18 '09 at 17:36
add comment

7 Answers

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.

share|improve this answer
While that could possibly explain missing rows, I think it is highly unlikely that the same row would appear differently in the two contexts, unless the query was intentionally crafted with CASE expressions based on user name. I assume this is just a SELECT query and not a stored procedure call (where such logic could be invisible to the Bob Mc). –  Aaron Bertrand Nov 17 '09 at 21:40
Yes, it's just a SELECT query, not a stored proc call. –  Bob Mc Nov 18 '09 at 0:55
I logged into SQL Server Management Studio using the program's credentials and tried the query. I got the correct results so permissions don't seem to be it. Thanks, though. –  Bob Mc Nov 18 '09 at 1:06
add comment

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.

share|improve this answer
That's a good thought, maybe try with a very simple query against a single row. Update the row in the database and see if Linq catches up. –  Aaron Bertrand Nov 17 '09 at 21:39
When I read this answer I thought for sure this was the problem, but I updated the relevant tables and views in the DBML file with no joy. –  Bob Mc Nov 18 '09 at 1:27
Not the DBML, but the actual DataContext. You should be doing Dim db as context = new MyDbContext() every time you do a new operation. If MyGetDataContextHelper() creates resuses a context between calls, it won't be grabbing the freshest table from the database. –  Benjamin Autin Nov 18 '09 at 13:12
Ah, now I understand what you meant. Yes, I'm getting a new DataContext each time this query is run. The helper method instantiates a new data context each time. –  Bob Mc Nov 18 '09 at 13:40
add comment

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.

share|improve this answer
As per your request, I posted a condensed version of the generated query from Linq. I'm not sure how I would be using REPEATABL READ or READ UNCOMMITTED, can you elaborate? Also, the data is very static - it's not transactional it's more for reporting. Thanks. –  Bob Mc Nov 18 '09 at 1:29
I don't use Linq but my understanding is that you can explicitly set the isolation level using something like: IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted ... however since you say that the data is relatively static, I am still guessing that either (a) the query you're comparing is not the one actually sent, or (b) your DataContext is stale. –  Aaron Bertrand Nov 18 '09 at 17:38
add comment

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

share|improve this answer
Thanks, I have already done that, with the same result. It's what I meant in the original post when I noted that I used the "DataContext log property" to write to a file. Unless you meant something else. If so, please elucidate. –  Bob Mc Nov 18 '09 at 1:16
add comment

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.
share|improve this answer
My code should have read Return qry.ToList(), since the code snippet is part of a function returning the List. I have edited the code to reflect that. –  Bob Mc Nov 18 '09 at 3:26
add comment

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? :)

share|improve this answer
add comment
up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer


