#nullable disable

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

var options = new DbContextOptionsBuilder<BugDbContext>()
   .UseSqlServer()
   .Options;

using var _db = new BugDbContext(options);

var stockItems =
    from g in _db.product_stock_item
    group g by g.product_id into x
    select new
    {
        product_id = x.Key,
        first_time_stocked = x.Min(y => y.date_arrived)
    };

var items =
    from p in _db.product
    from stock_item_error in stockItems.Where(si => si.product_id == p.product_id).DefaultIfEmpty()
    join x in stockItems on p.product_id equals x.product_id into xg
    from stock_item_okay in xg.DefaultIfEmpty()
    select new
    {
        p.product_id,
        error = stock_item_okay.first_time_stocked,
        okay = stock_item_error.first_time_stocked
    };

Console.WriteLine(items.ToQueryString());

public class BugDbContext : DbContext
{
    public BugDbContext(DbContextOptions<BugDbContext> options)
        : base(options)
    { }

    public DbSet<product> product { get; set; }
    public DbSet<product_stock_item> product_stock_item { get; set; }
}

public class product
{
    [Key]
    public int product_id { get; set; }
}

public class product_stock_item
{
    [Key]
    public int product_stock_item_id { get; set; }
    public int product_id { get; set; }
    public DateTime? date_arrived { get; set; }
}