none
Incorrect Query Result Set Returned RRS feed

  • 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....
    
    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.

    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?

    Thanks in advance,

    Bob Mc.
    Tuesday, November 17, 2009 7:05 PM

Answers

  • Hello,

     

    I’d like to update this thread for some new information.   Thanks to Bob for sending me the demo project!  After our research, the root cause of the problem is the precision of the parameters passed into the LINQ to SQL query. 

     

    The original parameter is in type of VB.NET’s Decimal (0.0174532925199433), then LINQ to SQL passes it as decimal(29, 4), so it becomes 0.0174.  After a complicated operation, the return value can be different if we pass 0.0174532925199433.  The workaround is to change the type to VB.NET’s Single, and then LINQ to SQL will pass it as float (0. 0174532925199433).   So we get the right output. 

     

    During the research process, we also found a potential issue on SQL Server Profiler when tracking the T-SQL generated by LINQ to SQL provider.  I will consult the product team for the issues and update the thread again.  

     

    Merry Christmas & Happy New Year!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 24, 2009 3:57 AM
    Moderator

All replies

  • It's possible LINQ to SQL is doing some optimization when it comes to actually running the query.

    Set the datacontext's Log property to something you can observe http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-writers and see what actual TSQL is being generated.

    [)amien

    Tuesday, November 17, 2009 7:12 PM
    Moderator
  • Thanks for the reply Damien.

    I set up a log (nice tip, thanks) that wrote the query out to a file. It looked very similar to the query shown in the SQL Debug Visualizer. I copied the query, painstakingly replaced all the parameters with the correct values (shown in comments at the end of the log) and ran the query in SQL Management Studio. And I got the correct thirteen rows that I expected. However, what gets returned by the "ToList" method is still only eight somewhat different rows.

    So I'm still confused about what's happening here. The generated SQL returns the correct result in SQL Server, but Linq returns a different result.

    Bob Mc.
    Tuesday, November 17, 2009 8:19 PM
  • Hello Bob,

     

    Welcome to LINQ to SQL forum!

     

    Are you querying the views via LINQ to SQL?   Or do you loading the relational data via DataLoadOptions?  If DataLoadOptions is used,  LINQ to SQL will JOIN the relational tables and returns all the necessary columns.  But when mapping the result to the CLR types, some children entities will be loaded into parent entities’ navigation property.  

     

    Could you please provide us with more detailed information to investigate?   The LINQ to SQL query, data structure of the tables and the T-SQL generated by LINQ to SQL provider would be really helpful.   If it is convenient for you to create a demo project, it will be great!  J   My mail address is v-micsun @ microsoft.com. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 18, 2009 5:50 AM
    Moderator
  • Hello Bob,

     

    How is the problem?  If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 23, 2009 12:40 AM
    Moderator
  • Lingzhi,

    The problem has not been resolved. I finally got around to making a sample project that I will send to your email address, as noted in an earlier post.

    Thanks,

    Bob Mc.

    Tuesday, December 15, 2009 9:39 PM
  • Hello,

     

    I’d like to update this thread for some new information.   Thanks to Bob for sending me the demo project!  After our research, the root cause of the problem is the precision of the parameters passed into the LINQ to SQL query. 

     

    The original parameter is in type of VB.NET’s Decimal (0.0174532925199433), then LINQ to SQL passes it as decimal(29, 4), so it becomes 0.0174.  After a complicated operation, the return value can be different if we pass 0.0174532925199433.  The workaround is to change the type to VB.NET’s Single, and then LINQ to SQL will pass it as float (0. 0174532925199433).   So we get the right output. 

     

    During the research process, we also found a potential issue on SQL Server Profiler when tracking the T-SQL generated by LINQ to SQL provider.  I will consult the product team for the issues and update the thread again.  

     

    Merry Christmas & Happy New Year!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 24, 2009 3:57 AM
    Moderator