# EF Core 3.1 breaks left join with no navigation property

Just recently my team took on the challenge of upgrading our codebase from .NET Core 2.2 to .NET Core 3.1. Along the way we encountered a quirky issue which caused us much befuddlement. Should you be befuddled too, then maybe this can help you.

Whilst running our app, we started encountering an error with an Entity Framework Query that looked like this:

``var stuffWeCareAbout = await context.Things    .Include(thing => thing.ThisIsFine)    .Include(thing => thing.Problematic)    .Where(thing => thing.CreatedOn > startFromThisTime && thing.CreatedOn < endAtThisTime)    .OrderByDescending(thing => thing.CreatedOn)    .ToArrayAsync();``

## Join me!​

As EF Core tried to join from the `Things` table to the `Problematic` table (some obfuscation in table names here), that which worked in .NET Core 2.2 was not working in .NET Core 3.1. Digging into the issue, we discovered EF Core was generating an invalid `LEFT JOIN`:

``fail: Microsoft.EntityFrameworkCore.Database.Command      Failed executing DbCommand (18ms) [Parameters=[@__startFromThisTime_0='?' (DbType = DateTime2), @__endAtThisTime_1='?' (DbType = DateTime2)], CommandType='Text', CommandTimeout='30']      SELECT [o].[ThingId], [o].[AnonymousId], [o].[CreatedOn],  [o].[Status], [o].[UpdatedOn], [o0].[Id], [o0].[ThingId], [o0].[Name], [o1].[ThingId], [o1].[Status], [o1].[CreatedOn], [o1].[ThingThingId], [o1].[SentOn]      FROM [Things] AS [o]      LEFT JOIN [ThisIsFines] AS [o0] ON [o].[ThingId] = [o0].[ThingId]      LEFT JOIN [Problematic] AS [o1] ON [o].[ThingId] = [o1].[ThingThingId]      WHERE ([o].[CreatedOn] @__startFromThisTime_0) AND ([o].[CreatedOn] < @__endAtThisTime_1)      ORDER BY [o].[CreatedOn] DESC, [o].[ThingId], [o1].[ThingId], [o1].[Status]Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (18ms) [Parameters=[@__startFromThisTime_0='?' (DbType = DateTime2), @__endAtThisTime_1='?' (DbType = DateTime2)], CommandType='Text', CommandTimeout='30']SELECT [o].[ThingId], [o].[AnonymousId], [o].[CreatedOn], [o].[Status], [o].[UpdatedOn], [o0].[Id], [o0].[ThingId], [o0].[Name], [o1].[ThingId], [o1].[Status], [o1].[CreatedOn], [o1].[ThingThingId], [o1].[SentOn]FROM [Things] AS [o]LEFT JOIN [ThisIsFines] AS [o0] ON [o].[ThingId] = [o0].[ThingId]LEFT JOIN [Problematic] AS [o1] ON [o].[ThingId] = [o1].[ThingThingId]WHERE ([o].[CreatedOn] @__startFromThisTime_0) AND ([o].[CreatedOn] < @__endAtThisTime_1)ORDER BY [o].[CreatedOn] DESC, [o].[ThingId], [o1].[ThingId], [o1].[Status]``

Do you see it? Probably not; it took us a while too... The issue lay here:

``LEFT JOIN [Problematic] AS [o1] ON [o].[ThingId] = [o1].[ThingThingId]``

This should actually have been:

``LEFT JOIN [Problematic] AS [o1] ON [o].[ThingId] = [o1].[ThingId]``

For some reason EF Core was looking for `ThingThingId` where it should have looked for `ThingId`. But why?

This was the `Problematic` class:

``using System;using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;namespace Treasury.Data.Entities{    public class Problematic    {        [ForeignKey("Thing")]        [Required]        public Guid ThingId { get; set; }        [Required]        public DateTime CreatedOn { get; set; }        public DateTime SentOn { get; set; }    }}``

If you look closely you'll see it has a `ForeignKey` but no accompanying Navigation property. So let's add one:

``using System;using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;namespace Our.App{    public class Problematic    {        [ForeignKey("Thing")]        [Required]        public Guid ThingId { get; set; }        [Required]        public DateTime CreatedOn { get; set; }        public DateTime SentOn { get; set; }        /* THIS NAVIGATION PROPERTY IS WHAT WE NEEDED!!! */        public virtual Thing Thing { get; set; }    }}``

With this in place our app starts generating the SQL we need.
{"ft_lang_label":"__label__en","ft_lang_prob":0.60558087,"math_prob":0.43593073,"size":3780,"snap":"2022-40-2023-06","text_gpt3_token_len":1062,"char_repetition_ratio":0.17161018,"word_repetition_ratio":0.41356674,"special_character_ratio":0.2936508,"punctuation_ratio":0.25824964,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.951441,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-02-06T22:43:12Z\",\"WARC-Record-ID\":\"<urn:uuid:742498d8-845f-41bc-a7ff-d39ee739b25a>\",\"Content-Length\":\"92022\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:36286d75-7577-44c7-a22c-393d5d23c349>\",\"WARC-Concurrent-To\":\"<urn:uuid:56d772b2-abde-4f23-b1da-9ffbdec5d659>\",\"WARC-IP-Address\":\"172.67.129.104\",\"WARC-Target-URI\":\"https://johnnyreilly.com/ef-core-31-breaks-left-join-with-no-navigation-property\",\"WARC-Payload-Digest\":\"sha1:7I2DHARGFN2AVB2YSACVQ646E5E3MLX6\",\"WARC-Block-Digest\":\"sha1:PVNQ2S7DHBZ2TO7TMBEWKRG3L234ROUM\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-06/CC-MAIN-2023-06_segments_1674764500365.52_warc_CC-MAIN-20230206212647-20230207002647-00221.warc.gz\"}"}