I have a web application that was using SubSonic version 2.1.0. While adding some new features I came across a bug in that version that was fixed in 2.2.0.
On my dev box, I switch the version of the DLL referenced and everything worked fine.
After updating the Windows 2008 R2 server running IIS 7.5, the bug has persisted.
I searched the server and replaced every instance of SubSonic.dll with the latest version.
Restarted the site, the app pool and then the entire server.
I ran SysInternal's Process Explorer and checked the w3wp.exe process for the site and according to it, w3wp.exe is referencing version 2.2.0 of SubSonic.dll.
I've copied the database and site files from that Windows 2008 server to another 2008 (one that never had v2.1.0 loaded) and confirmed the bug doesn't occur there.
Based on the symptoms, it seems that the server is holding on to the 2.1.0 version of the DLL but I cannot figure out where it has it and how to get rid of it.
I checked the GAC, no SubSonic dlls.
Installed 2.2.0 into the GAC using gacutil from the Windows SDK 6.1
Setup a new Site and AppPool on the server and uploaded a fresh copy of the site files from my dev box.
Site is C# .Net 2.0 using MVC 1 with nHaml 2.0 for the view engine.
Used cygwin find to search the filesystem for any files with the same size as the 2.1 DLL.
Found a couple copies that the Windows search box didn't, they were just in a backup folder, shouldn't have been in use, deleted just in case.
The only remaining files with the same size as the 2.1.0 version are:
Which appear to have nothing to do with the SubSonic DLLs.
Reasoning regarding bug for suspecting the DLL version in use is the issue:
The error that is happening is rather specific and on my development box when I change between 2.1.0 and 2.2.0 of the DLL it predictably happens under 2.1.0 and not 2.2.0.
I've also put the site to a different server and it operates fine with 2.2.0, so it working is not unique to my development box.
Basically, in version 2.1.0 when doing a paged result query the WHERE clause elements are doubled up so the generated query would end up with "WHERE CreatedOn > '8-1-2010' AND CreatedOn > '8-1-2010'".
Although redundant, syntactically this is fine to execute.
When you add in a SubQuery is when it goes wrong because the SubQuery object has its SQL generated twice and the second time instead of starting with a WHERE it starts with an AND because a boolean flag on the object tracking if the WHERE has been started is true at the start from the first time generating the SQL.
So under 2.1.0 you will get "WHERE id IN (SELECT id FROM table WHERE CreatedOn > '8-1-2010') AND id IN (SELECT id FROM table AND CreatedOn > '8-1-2010')"
In 2.2.0, the WHERE clause on a paged query doesn't repeat its conditions and so the SubQuery doesn't generate improper SQL syntax.
The SQL generation occurs within the DLL and I can observer through SQL Profiler that 2.1.0 generates the bad syntax but when I run locally with 2.2.0 the syntax is proper.
Because this error is such a specific situation, the site works fine in general, it is on just a specific search query that this happens and it is very easily to repeat that with no code, data or data structure or other environment changes, it errors under 2.1.0 but not 2.2.0.
I hadn't given specifics on the bug previously as it doesn't seem relevant to solving the issue, the resolution should be regarding any 3rd party dll loaded from the bin directory of an asp.net site being cached and not updating version after app pool, service and machine reboots, new site container creation, deletion and reupload of all site files, etc.