Entity Framework: LINQ query generates different SQL between local execution and server execution

Question

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.

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