C# interpret my query as inner join instead of left outer join on what cases this occurs?

Question

I have two different queries in my MVC application. Both queries produce the expected result in LINQPAD. But one of my queries doesn't produce left outer joined result; instead it produces inner join result, where the null record in left table is eliminated and produces other records.

my query looks like

var query = from inq in Inquiries
join sp in SalesPersons on inq.AssignedToID equals (int?)sp.SalesPersonID
into inquirySalesJoin
from sp in inquirySalesJoin.DefaultIfEmpty()
where (!inq.Deleted)
select new 
{
InquiryID = inq.InquiryID,
NameFirst = inq.NameFirst,
Salutation = inq.SalutationID,
NameLast = inq.NameLast,   
AssignedToID = inq.AssignedToID,
AssignedToDescription = (inq.AssignedToID == null ? "Not Assigned" : ((sp.NameFirst == null ? "" : sp.NameFirst) + " " + (sp.NameLast == null ? "" : sp.NameLast))),
InquiryStatus = inq.InquiryStatus
}

when I convert convert my query to string the SQL statement produced doesn't contains any outer join method, where it's actually performing inner join

"SELECT 
[Project1].[InquiryID] AS [InquiryID], 
[Project1].[NameFirst] AS [NameFirst], 
[Project1].[SalutationID] AS [SalutationID], 
[Project1].[NameLast] AS [NameLast], 
[Project1].[CompanyName] AS [CompanyName], 
[Project1].[JobTitle] AS [JobTitle], 
[Project1].[TelePhone] AS [TelePhone], 
[Project1].[Email] AS [Email], 
[Project1].[InterestedProductID] AS [InterestedProductID], 
[Project1].[Others] AS [Others], 
[Project1].[ReferedBy] AS [ReferedBy], 
[Project1].[Comments] AS [Comments], 
[Project1].[AssignedToID] AS [AssignedToID], 
[Project1].[C1] AS [C1], 
[Project1].[InquiryStatus] AS [InquiryStatus]
FROM ( SELECT 
    [Extent1].[InquiryID] AS [InquiryID], 
    [Extent1].[NameLast] AS [NameLast], 
    [Extent1].[NameFirst] AS [NameFirst], 
    [Extent1].[CompanyName] AS [CompanyName], 
    [Extent1].[JobTitle] AS [JobTitle], 
    [Extent1].[TelePhone
] AS [TelePhone], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[InterestedProductID] AS [InterestedProductID], 
    [Extent1].[Others] AS [Others], 
    [Extent1].[ReferedBy] AS [ReferedBy], 
    [Extent1].[Comments] AS [Comments], 
    [Extent1].[AssignedToID] AS [AssignedToID], 
    [Extent1].[InquiryStatus] AS [InquiryStatus], 
    [Extent1].[SalutationID] AS [SalutationID], 
    CASE WHEN ([Extent2].[NameFirst] IS NULL) THEN N'' ELSE [Extent2].[NameFirst] END + N' ' + CASE WHEN ([Extent2].[NameLast] IS NULL) THEN N'' ELSE [Extent2].[NameLast] END AS [C1]
    FROM  [DBO].[Inquiry] AS [Extent1]
    INNER JOIN [DBO].[SalesPerson] AS [Extent2] ON [Extent1].[AssignedToID] = [Extent2].[SalesPersonID]
    WHERE [Extent1].[Deleted] <> cast(1 as bit)
)  AS [Project1]
ORDER BY [Project1].[InquiryID] ASC"

I am wondering on what cases this occurs?

My another query I am not assigning nullable int? to the FK id.

Please format the SQL query more sensibly - it's *incredibly* hard to read right now. (Just replacing each "\r\n" with a real line break would be a good start.) — Jon Skeet, Oct 31 '14 at 09:47
It would also help if you'd give a minimal example, e.g. between tables with just a couple of properties each. — Jon Skeet, Oct 31 '14 at 09:48
http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join Try the second answer from Amir, it should work and the syntax is more pleasant — Aymeric, Oct 31 '14 at 10:03
@Aymeric i looked over Amirs Answer but its not helpful,all i wanted to know is i wrote a linq query to perform a left outer join but c# interprets the query as inner,it produce o/p based on right table my null records in left table is not shown ,but the same query is producing expected result in linqpad not in VS2013 — bassam, Nov 03 '14 at 10:47

score 0 · Accepted Answer · answered Nov 13 '14 at 13:54

Just change your code as

var query = from inq in Inquiries
        join sp in SalesPersons on inq.AssignedToID equals (int?)sp.SalesPersonID
        into inquirySalesJoin
        from subsp in inquirySalesJoin.DefaultIfEmpty()
        where (!inq.Deleted)
        select new 
        {
        InquiryID = subsp.InquiryID,
        NameFirst = subsp.NameFirst,
        Salutation = subsp.SalutationID,
        NameLast = subsp.NameLast,   
        AssignedToID = subsp.AssignedToID,
        AssignedToDescription = (subsp.AssignedToID == null ? "Not Assigned" : ((subsp.NameFirst    == null ? "" : subsp.NameFirst) + " " + (subsp.NameLast == null ? "" : subsp.NameLast))),
        InquiryStatus = subsp.InquiryStatus
        }

Not from sp you should take it from subsp not the same with sp in SalesPersons — cCcik, Nov 14 '14 at 11:38

C# interpret my query as inner join instead of left outer join on what cases this occurs?

1 Answers1