I have a website built using Asp.net and LinqToSql for Data Access. In a certain section of the site, LinqToSql produces a query that looks like this (from my dev machine):
select ...
from table1
left outer join table2 on table1 where ...
left outer join table3 on table2 where ...
Since the connection between table2 and table1 is not always there, the left outer join is appropriate in this situation. And since the link between table3 and table1 goes through table2, it also needs a left outer join. This sql returns the correct recordset.
I just put the code up to a server. Running the identical code in the same scenario, LinqToSql produces the following query:
select ...
from table1
left outer join table2 on table1 where ...
join table3 on table2 where ...
For some reason, it renders the join between table2 and table3 as an inner join, instead of an outer join. This results in zero records being returned from the query.
Both dev machine and server are using .Net 3.5 SP1. Dev machine is Vista64, Server is Windows Server 2003 SP2. A colleague of mine using Windows XP PRO also confirmed the same correct behavior on their dev machine.
Can anyone think of a reason why the server would create different sql? How can I fix this? It seems to be something tied into the way that Linq and .Net is running on the server. However, I can't think of any way to confirm and fix this.
Linq Code (I am only including the parts that are relevant to the section where the sql changed):
from Import_Table t in db.Import_Tables
select new {
CheckedOutUser = (!t.IsCheckedOut) ? "--" : t.Import_CheckoutHistory.System_User.FirstName + " " + t.Import_CheckoutHistory.System_User.LastName,
CheckedOutUserID = (!t.IsCheckedOut) ? 0 : t.Import_CheckoutHistory.System_UserID};
In the context of the description above, table1 = Import_Table, table2 = Import_CheckoutHistory, table3 = System_User. If I comment out the line here that begins with "CheckedOutUser = ..." then it works on the server - so this is definitely the culprit.
Actual sql returned:
SELECT
(CASE WHEN NOT ([t0].[IsCheckedOut] = 1) THEN CONVERT(NVarChar(401),'--') ELSE ([t2].[FirstName] + ' ') + [t2].[LastName] END) AS [CheckedOutUser],
(CASE WHEN NOT ([t0].[IsCheckedOut] = 1) THEN 0 ELSE [t1].[system_UserID] END) AS [CheckedOutUserID]
FROM [dbo].[import_Table] AS [t0]
LEFT OUTER JOIN [dbo].[import_CheckoutHistory] AS [t1] ON [t1].[import_CheckoutHistoryID] = [t0].[import_CheckoutHistoryID]
LEFT OUTER/INNER JOIN [dbo].[system_User] AS [t2] ON [t2].[system_UserID] = [t1].[system_UserID]
On the dev machines, the last line begins with "Left outer". On the server, the last line begins with "Inner"
Update: My solution is below