5

I have a very basic Linq query which is not returning the same result if I execute it locally in Visual Studio or on an IIS server - but always targeting the same database server. I have used SQL Server Profiler to trace the real SQL query executed and found out that it was not the same when executing locally or remotely !

Locally it uses a Left join while remotely it uses an Inner join - and so locally it returns a record but not remotely. I think the good behavior would be the second as I defined a non nullable foreign key between TableA and TableB. Below is the Linq request:

from a in TableA.Include("TableB.TableC")
where a.Id == someId
select a;

In fact the first join is always translated in an Inner join, but the second is a left join when executed locally.

But my priority is to know why it generates a different query locally and remotely. The framework versions are the same, Entity framework versions are the same (copied locally)... Something must be different but I cannot find what ! Do you have any clue ?

Thank you in advance.

user1756338
  • 211
  • 2
  • 13
  • Are you running same application? The only different thing is a connection string value? – Sergey Berezovskiy Jul 15 '13 at 14:43
  • Yes the same application, and same connection string – user1756338 Jul 15 '13 at 14:49
  • So, you have different instances of application. Make sure you have deployed same assemblies to remote server – Sergey Berezovskiy Jul 15 '13 at 14:51
  • Which assembly is generating the SQL ? EntityFramework.dll ? – user1756338 Jul 15 '13 at 14:52
  • I think problem is in assembly which defines query – Sergey Berezovskiy Jul 15 '13 at 14:56
  • No I am sure it is the same, I have tried to change a detail in the query and still the problem. – user1756338 Jul 15 '13 at 14:58
  • I have just seen that the build version of the framework is different, 4.0.30319.1 locally and 4.0.30319.17929 remotely. It could be the problem if the SQL is not generated by Entity Framework itself... ? – user1756338 Jul 15 '13 at 15:06
  • Can you produce this with a small amount of code and post that code? I highly doubt that different builds of Entity Framework create different queries for something so simple as this would be a massively breaking change. It seems much more likely to me that it is in your code. Thus reproducing this with a small program would be valuable to either 1) prove it is in EF, or 2) help you figure out where your code is doing something different. – tster Jul 15 '13 at 15:16
  • In fact it seems that this build is .NET version 4.5 http://en.wikipedia.org/wiki/List_of_.NET_Framework_versions so it is more than just a different build. I am going to uninstall it and install the same version and I will let you know it it solves this problems (I did not find any release note to see if it was a bug fix) – user1756338 Jul 15 '13 at 15:26
  • FYI, you can use http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework to get the query without having to profile it. – Brad Christie Jul 15 '13 at 15:43
  • You should answer your own question and mark as answered if this is solved :) – bflemi3 Jul 17 '13 at 19:21

1 Answers1

1

So finally, the problem was the Framework version. I thought it was the same version of .NET but it was not: 4.0.30319.1 locally and 4.0.30319.17929 remotely. And it seems that 4.0.30319.17929 is .NET Framework 4.5, so it is more than just a different build. I have uninstalled the version 4.5, and reinstalled the 4.0 on the server It is strange because it reinstalled it in folder C:\Windows\Microsoft.NET\Framework\v4.0.30319 but the file versions are now correct, 4.0.30319.1 (file versions were 4.0.30319.17929 before) Then I have changed the .NET version in the IIS application pool. It has been reset to version 2.0 after uninstalling, so I reset to version 4 and restart the pool (but it is still displaying version v4.0.30319 in the application pool...). And now it works like locally, it does one inner join and then a left join.

I think this is a bug fix added in version 4.5 as it should use an inner join because of the non nullable foreign keys (note that the primary key is composed of several columns) But this kind of change would be difficult to detect when upgrading to 4.5...

user1756338
  • 211
  • 2
  • 13