In Pomelo 5.0.0 and later, the result int item of DefaultIfEmpty returns null instead of 0

[karuakun]

I have an application that uses an older Pomelo version.
If you update your Pomelo version from 3.2.7 to 5.0.0 or higher, DefaultIfEmpty will return a different result.
Is there any way to revert to 3.2.7 results?

using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApp2;
public class Program
{
    public static void Main()
    {
        var optionBuilder = new DbContextOptionsBuilder<MyDbContext>();
        optionBuilder.UseMySql("server=localhost;user=root;password=root;database=db", new MySqlServerVersion("8.0"));
        //optionBuilder.UseMySql("server=localhost;user=root;password=root;database=db");
        using var dbContext = new MyDbContext(optionBuilder.Options);

        var mailAddresses = (
            from user in dbContext.Users
            join mailAddress in dbContext.UserMailAddresses
                on user.Id equals mailAddress.UserId
                into tJoin
            from mailAddress in tJoin.DefaultIfEmpty()
            select new
            {
                UserId = user.Id,
                //No = (int?)mailAddress.No ?? 0,
                mailAddress.No,
                mailAddress.MailAddress,
            }
        );
        foreach (var mailAddress in mailAddresses)
        {
            Console.WriteLine($"UserId:{mailAddress.UserId}, No:{mailAddress.No}, MailAddress:{mailAddress.MailAddress}");
        }
    }
}

public class User
{
    public int Id { get; set; }
    [MaxLength(100)]
    public string Name { get; set; } = null!;
}

public class UserMailAddress
{
    public int Id { get; set; }
    public int No { get; set; }
    public int UserId { get; set; }
    [MaxLength(100)]
    public string MailAddress { get; set; } = null!;
}
public class MyDbContext(DbContextOptions<MyDbContext> options) : DbContext(options)
{
    public DbSet<User> Users => Set<User>();
    public DbSet<UserMailAddress> UserMailAddresses => Set<UserMailAddress>();
}

Pomelo 3.2.7

UserId:1, No:1, MailAddress:hoge
UserId:2, No:0, MailAddress:

Pomelo 5.0.0

UserId:1, No:1, MailAddress:hoge
Unhandled exception. System.InvalidOperationException: Nullable object must have a value.
   at lambda_method1(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at ConsoleApp2.Program.Main() in C:\Users\***\source\repos\ConsoleApp2\ConsoleApp2\Program.cs:line 30

[lauxjpn]

The EF Core behavior has changed between 3.1 and 5.0. I would argue that the new behavior is the expected one. Calling .DefaultIfEmpty() should return null for the inner object, if there is no inner entity to LEFT JOIN with. And that is the EF Core 5+ behavior.

I used the following code to test the behavior in EF Core 3.1/5.0:

Program.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.Diagnostics;
using System.Linq;
using System.Net.Mail;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate;

public class User
{
    public int Id { get; set; }

    [MaxLength(100)]
    public string Name { get; set; } = null!;
}

public class UserMailAddress
{
    public int Id { get; set; }
    public int No { get; set; }
    public int UserId { get; set; }

    [MaxLength(100)]
    public string MailAddress { get; set; } = null!;
}

public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<UserMailAddress> UserMailAddresses { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;Database=Issue1994";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
#if EFCORE3_1
                .UseMySql(connectionString, b => b.ServerVersion(serverVersion).CharSetBehavior(CharSetBehavior.NeverAppend))
                .UseLoggerFactory(
                    LoggerFactory.Create(builder => builder
                        .AddConsole()
                        .AddFilter(level => level >= LogLevel.Information)))
#else
                .UseMySql(connectionString, serverVersion)
                .LogTo(Console.WriteLine, LogLevel.Information)
#endif
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasData(
            new User { Id = 1, Name = "John" },
            new User { Id = 2, Name = "Jane" },
            new User { Id = 3, Name = "Jim" });

        modelBuilder.Entity<UserMailAddress>().HasData(
            new UserMailAddress { Id = 1, No = 1, UserId = 1, MailAddress = "john@example.com" },
            new UserMailAddress { Id = 2, No = 2, UserId = 2, MailAddress = "jane@example.com" });
    }
}

internal static class Program
{
    private static void Main()
    {
        using var context = new Context();

        // Recreate the database on every run.
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var mailAddressesQuery = from user in context.Users
            join mailAddress in context.UserMailAddresses
                on user.Id equals mailAddress.UserId
                into tJoin
            from mailAddress in tJoin.DefaultIfEmpty()
            orderby user.Id
            select new
            {
                UserId = user.Id,
#if EFCORE3_1
                mailAddress.No,
                mailAddress.MailAddress,
#else
                No = mailAddress != null ? mailAddress.No : 0,
                MailAddress = mailAddress != null ? mailAddress.MailAddress : null,
#endif
            };
        
        var mailAddresses = mailAddressesQuery.ToList();
        
        Trace.Assert(mailAddresses.Count == 3);

        Trace.Assert(mailAddresses[0].UserId == 1);
        Trace.Assert(mailAddresses[0].MailAddress == "john@example.com");
        
        Trace.Assert(mailAddresses[1].UserId == 2);
        Trace.Assert(mailAddresses[1].MailAddress == "jane@example.com");
        
        Trace.Assert(mailAddresses[2].UserId == 3);
        Trace.Assert(mailAddresses[2].MailAddress == null);
    }
}

So, as part of your upgrade from 3.1 to 5.0, you should update your queries to ensure that inner (nullable) objects in a LEFT JOIN are not null, before accessing their properties.