Left outer join not being honoured

Question

We have the following EF code:

    var qry =
            from c in db.Contacts

            join comp in db.Companies on c.CompanyId equals comp.CompanyId
                into compLeft
            from cj in compLeft.DefaultIfEmpty()

            select new CompleteUserDlModel
            {
                CompanyName = cj.Company1,
                CompanyId = c.CompanyId
            };

which generates this SQL

SELECT 
    [Extent1].[CompanyId] AS [CompanyId], 
    [Extent2].[Company] AS [Company]
    FROM  [dbo].[Contacts] AS [Extent1]
    INNER JOIN [dbo].[Company] AS [Extent2] ON [Extent1].[CompanyId] = [Extent2].[CompanyId]

but we actually want

SELECT 
    [Extent1].[CompanyId] AS [CompanyId], 
    [Extent2].[Company] AS [Company]
    FROM  [dbo].[Contacts] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Company] AS [Extent2] ON [Extent1].[CompanyId] = [Extent2].[CompanyId]

Could someone point out what we've done wrong, please?

All the refs on left outer joins in C# EF (i.e. LEFT OUTER JOIN in LINQ) point to the syntax we're using. Clearly, we're missing something.

What version of EF are you using? Are those two tables are related in your schema and relationship is represented in your model? — ocuenca, Jul 03 '19 at 17:36
I think you need to explicitly provide the null value case in the select statement e.g. if CompanyName is a string: CompanyName = cj.Company1??" ". — Andrew, Jul 03 '19 at 17:47
Thanks for the responses. We're using EF 6.2.0. Company.CompanyId is the PK. Contacts.CompanyId is an FK on Company.CompanyId. I had a look in the edmx file and the relationship seems to be there. — Adam Benson, Jul 04 '19 at 09:54

score 2 · Accepted Answer · answered Jul 03 '19 at 18:15

Could someone point out what we've done wrong, please?

Probably you have Contact.CompanyId a typed as int instead of int?, making it a required property, and so EF assumes you have referential integrity when generating a query.

But, as always, left join in LINQ has bad code smell, and can almost always be replaced by just querying your target entity and traversing its Navigation Properties. EG:

 from c in db.Contacts
 select new
 {
     CompanyName = c.CompanyId.HasValue?c.Company.CompanyName : null,
     CompanyId = c.CompanyId
 };

Quite right - CompanyId was not nullable. Not sure the code smells though - smells OK on my machine ;-) Thanks for the response; I learnt something from that. — Adam Benson, Jul 04 '19 at 10:25

score 0 · Answer 2 · answered Jul 03 '19 at 18:10

0

var qry =
            from c in db.Contacts

            join comp in db.Companies on c.CompanyId equals comp.CompanyId
                into compLeft
            from cj in compLeft.DefaultIfEmpty()

            select new CompleteUserDlModel
            {
                CompanyName = cj.Company1,
                CompanyId = c?.CompanyId ?? String.Empty
            };

answered Jul 03 '19 at 18:10

Edney Holder

1,140
8
22

`?.` is not allowed in expression trees. – madreflection Jul 03 '19 at 18:39

Left outer join not being honoured

2 Answers2