Left Outer Join return less number of rows than it should using Linq

[mnhafiz23]

Hi, I'm trying to fetch data from joining two tables but the number of rows that I'm expecting/actual result are less than it should be (actual result should be 12 but I've got only 5 rows) and there is no error. Here is my models:

MetInfo:

public class MetInfo
    {
        [Key]
        public int StnID { get; set; }
        public int MMSI { get; set; }
        public DateTime RecvTime { get; set; }
        public Int16? DAC { get; set; }
        public Int16? FI { get; set; }
        public double Longitude { get; set; }
        public double Latitude { get; set; }    
    }

ShipList:

 public class ShipList
    {
        [Key]
        public int ShipListID { get; set; }
        public int? MMSI { get; set; }
        public string Name { get; set; }
        public int? IMO { get; set; }
        public string CallSign { get; set; }
        public double? Latitude { get; set; }
        public double? Longitude { get; set; }
        public DateTime RecvTime { get; set; }
    }

Here is my query:

(from met in vtsDbContext.MetInfos
 orderby met.RecvTime descending
 group met by met.MMSI into mg
 join sl in vtsDbContext.ShipLists on mg.FirstOrDefault().MMSI equals sl.MMSI into ps from sl in ps.DefaultIfEmpty()
select new { mg.FirstOrDefault().MMSI, mg.FirstOrDefault().RecvTime, sl.Name }).ToList();

I've tried to compare it with EF6 by creating 2 different Console Apps. One is using EF Core and the other is using EF6. EF6 returns the expected results but EF Core does not.

Here is EF Core:

This is EF6:

Further technical details
EF Core version: 2.2.0
Database Provider: Microsoft.EntityFrameworkCore.SQLServer

[ajcvickers]

@smitpatel Log and executable code below. Anything look wrong here?

dbug: Microsoft.EntityFrameworkCore.Query[10101]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Compiling query model:
      'from IGrouping<int, MetInfo> mg in
          (from MetInfo met in DbSet<MetInfo>
          order by [met].RecvTime desc
          select [met]).GroupBy([met].MMSI, [met])
      join ShipList sl in DbSet<ShipList>
      on (Nullable<int>)
          (from MetInfo <generated>_1 in [mg]
          select [<generated>_1]).FirstOrDefault().MMSI equals [sl].MMSI into ps
      from ShipList sl in
          (from ShipList <generated>_1 in [ps]
          select [<generated>_1]).DefaultIfEmpty()
      select new {

              (from MetInfo <generated>_1 in [mg]
              select [<generated>_1]).FirstOrDefault().MMSI,

              (from MetInfo <generated>_3 in [mg]
              select [<generated>_3]).FirstOrDefault().RecvTime,
          [sl].Name
       }'
warn: Microsoft.EntityFrameworkCore.Query[10103]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Query: '(from MetInfo <generated>_1 in [mg] select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
warn: Microsoft.EntityFrameworkCore.Query[10103]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Query: '(from MetInfo <generated>_1 in [mg] select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
warn: Microsoft.EntityFrameworkCore.Query[10103]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Query: '(from MetInfo <generated>_3 in [mg] select (Nullable<DateTime>)[<generated>_3].RecvTime).FirstOrDefa...' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
dbug: Microsoft.EntityFrameworkCore.Query[10104]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Optimized query model:
      'from IGrouping<int, MetInfo> mg in
          (from MetInfo met in DbSet<MetInfo>
          order by [met].RecvTime desc
          select [met]).GroupBy([met].MMSI, [met])
      join ShipList sl in DbSet<ShipList>
      on
          (from MetInfo <generated>_1 in [mg]
          select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault() equals [sl].MMSI into ps
      from ShipList sl in
          (from ShipList <generated>_1 in [ps]
          select [<generated>_1]).DefaultIfEmpty()
      select new {
          (int)
              (from MetInfo <generated>_1 in [mg]
              select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault(),
          (DateTime)
              (from MetInfo <generated>_3 in [mg]
              select (Nullable<DateTime>)[<generated>_3].RecvTime).FirstOrDefault(),
          [sl]?.Name
       }'
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'GroupBy([met].MMSI, [met])' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'join ShipList sl in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ShipList]) on {from MetInfo <generated>_1 in [mg] select Convert([<generated>_1].MMSI, Nullable`1) => FirstOrDefault()} equals [sl].MMSI' could n
ot be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'from ShipList sl in {[ps] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      (QueryContext queryContext) => IEnumerable<<>f__AnonymousType1<int, DateTime, string>> _InterceptExceptions(
      |__ source: IEnumerable<<>f__AnonymousType1<int, DateTime, string>> _Select(
      |   |__ source: IEnumerable<TransparentIdentifier<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>, ValueBuffer>> _SelectMany(
      |   |   |__ source: IEnumerable<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>> _GroupJoin(
      |   |   |   |__ outer: IEnumerable<IGrouping<int, MetInfo>> _GroupBy(
      |   |   |   |   |__ source: IEnumerable<MetInfo> _ShapedQuery(
      |   |   |   |   |   |__ queryContext: queryContext,
      |   |   |   |   |   |__ shaperCommandContext: SelectExpression:
      |   |   |   |   |   |       SELECT [met].[StnID], [met].[DAC], [met].[FI], [met].[Latitude], [met].[Longitude], [met].[MMSI], [met].[RecvTime]
      |   |   |   |   |   |       FROM [MetInfos] AS [met]
      |   |   |   |   |   |       ORDER BY [met].[MMSI], [met].[RecvTime] DESC,
      |   |   |   |   |   |__ shaper: BufferedEntityShaper<MetInfo>),
      |   |   |   |   |__ keySelector: (MetInfo met) => met.MMSI,
      |   |   |   |   |__ elementSelector: (MetInfo met) => met),
      |   |   |   |__ inner: IEnumerable<ValueBuffer> _ShapedQuery(
      |   |   |   |   |__ queryContext: queryContext,
      |   |   |   |   |__ shaperCommandContext: SelectExpression:
      |   |   |   |   |       SELECT [sl].[MMSI], [sl].[Name]
      |   |   |   |   |       FROM [ShipLists] AS [sl],
      |   |   |   |   |__ shaper: ValueBufferShaper),
      |   |   |   |__ outerKeySelector: (IGrouping<int, MetInfo> mg) => Nullable<int> FirstOrDefault(IEnumerable<Nullable<int>> _Select(
      |   |   |   |   |__ source: mg,
      |   |   |   |   |__ selector: (MetInfo <generated>_1) => (Nullable<int>)<generated>_1.MMSI)),
      |   |   |   |__ innerKeySelector: (ValueBuffer sl) => Nullable<int> TryReadValue(sl, 0, ShipList.MMSI),
      |   |   |   |__ resultSelector: (IGrouping<int, MetInfo> mg | IEnumerable<ValueBuffer> ps) => TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>> CreateTransparentIdentifier(
      |   |   |       |__ outer: mg,
      |   |   |       |__ inner: ps)),
      |   |   |__ collectionSelector: (TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>> t0) => IEnumerable<ValueBuffer> DefaultIfEmpty(t0.Inner),
      |   |   |__ resultSelector: (TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>> t0 | ValueBuffer sl) => TransparentIdentifier<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>, ValueBuffer> CreateTransparentIde
ntifier(
      |   |       |__ outer: t0,
      |   |       |__ inner: sl)),
      |   |__ selector: (TransparentIdentifier<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>, ValueBuffer> t1) => new {
      |           (int)Nullable<int> FirstOrDefault(IEnumerable<Nullable<int>> _Select(
      |           |__ source: t1.Outer.Outer,
      |           |__ selector: (MetInfo <generated>_1) => (Nullable<int>)<generated>_1.MMSI)),
      |           (DateTime)Nullable<DateTime> FirstOrDefault(IEnumerable<Nullable<DateTime>> _Select(
      |           |__ source: t1.Outer.Outer,
      |           |__ selector: (MetInfo <generated>_3) => (Nullable<DateTime>)<generated>_3.RecvTime)),
      |           !(t1.Inner.IsEmpty) ? string TryReadValue(t1.Inner, 1, ShipList.Name) : default(string)
      |        }),
      |__ contextType: BloggingContext,
      |__ logger: DiagnosticsLogger<Query>,
      |__ queryContext: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [met].[StnID], [met].[DAC], [met].[FI], [met].[Latitude], [met].[Longitude], [met].[MMSI], [met].[RecvTime]
      FROM [MetInfos] AS [met]
      ORDER BY [met].[MMSI], [met].[RecvTime] DESC
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [met].[StnID], [met].[DAC], [met].[FI], [met].[Latitude], [met].[Longitude], [met].[MMSI], [met].[RecvTime]
      FROM [MetInfos] AS [met]
      ORDER BY [met].[MMSI], [met].[RecvTime] DESC
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [sl].[MMSI], [sl].[Name]
      FROM [ShipLists] AS [sl]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [sl].[MMSI], [sl].[Name]
      FROM [ShipLists] AS [sl]
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug

My code:

public class MetInfo
{
    [Key]
    public int StnID { get; set; }
    public int MMSI { get; set; }
    public DateTime RecvTime { get; set; }
    public Int16? DAC { get; set; }
    public Int16? FI { get; set; }
    public double Longitude { get; set; }
    public double Latitude { get; set; }    
}

public class ShipList
{
    [Key]
    public int ShipListID { get; set; }
    public int? MMSI { get; set; }
    public string Name { get; set; }
    public int? IMO { get; set; }
    public string CallSign { get; set; }
    public double? Latitude { get; set; }
    public double? Longitude { get; set; }
    public DateTime RecvTime { get; set; }
}

public class BloggingContext : DbContext
{
    private static readonly LoggerFactory Logger
        = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(
                @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
    
    public DbSet<MetInfo> MetInfos { get; set; }
    public DbSet<ShipList> ShipLists { get; set; }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new MetInfo
                {
                    MMSI = 1
                },
                new MetInfo
                {
                    MMSI = 1
                },
                new MetInfo
                {
                    MMSI = 2
                },
                new MetInfo
                {
                    MMSI = 3
                },
                new ShipList
                {
                    MMSI = 1,
                },
                new ShipList
                {
                    MMSI = 1,
                },
                new ShipList
                {
                    MMSI = 2,
                },
                new ShipList
                {
                    MMSI = null,
                },
                new ShipList
                {
                    MMSI = 4,
                }
            );

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var results = (from met in context.MetInfos
                orderby met.RecvTime descending
                group met by met.MMSI into mg
                join sl in context.ShipLists on mg.FirstOrDefault().MMSI equals sl.MMSI into ps from sl in ps.DefaultIfEmpty()
                select new { mg.FirstOrDefault().MMSI, mg.FirstOrDefault().RecvTime, sl.Name }).ToList();            
        }
    }
}

My output:

[mnhafiz23]

@smitpatel Log and executable code below. Anything look wrong here?

dbug: Microsoft.EntityFrameworkCore.Query[10101]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Compiling query model:
      'from IGrouping<int, MetInfo> mg in
          (from MetInfo met in DbSet<MetInfo>
          order by [met].RecvTime desc
          select [met]).GroupBy([met].MMSI, [met])
      join ShipList sl in DbSet<ShipList>
      on (Nullable<int>)
          (from MetInfo <generated>_1 in [mg]
          select [<generated>_1]).FirstOrDefault().MMSI equals [sl].MMSI into ps
      from ShipList sl in
          (from ShipList <generated>_1 in [ps]
          select [<generated>_1]).DefaultIfEmpty()
      select new {

              (from MetInfo <generated>_1 in [mg]
              select [<generated>_1]).FirstOrDefault().MMSI,

              (from MetInfo <generated>_3 in [mg]
              select [<generated>_3]).FirstOrDefault().RecvTime,
          [sl].Name
       }'
warn: Microsoft.EntityFrameworkCore.Query[10103]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Query: '(from MetInfo <generated>_1 in [mg] select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
warn: Microsoft.EntityFrameworkCore.Query[10103]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Query: '(from MetInfo <generated>_1 in [mg] select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
warn: Microsoft.EntityFrameworkCore.Query[10103]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Query: '(from MetInfo <generated>_3 in [mg] select (Nullable<DateTime>)[<generated>_3].RecvTime).FirstOrDefa...' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
dbug: Microsoft.EntityFrameworkCore.Query[10104]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      Optimized query model:
      'from IGrouping<int, MetInfo> mg in
          (from MetInfo met in DbSet<MetInfo>
          order by [met].RecvTime desc
          select [met]).GroupBy([met].MMSI, [met])
      join ShipList sl in DbSet<ShipList>
      on
          (from MetInfo <generated>_1 in [mg]
          select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault() equals [sl].MMSI into ps
      from ShipList sl in
          (from ShipList <generated>_1 in [ps]
          select [<generated>_1]).DefaultIfEmpty()
      select new {
          (int)
              (from MetInfo <generated>_1 in [mg]
              select (Nullable<int>)[<generated>_1].MMSI).FirstOrDefault(),
          (DateTime)
              (from MetInfo <generated>_3 in [mg]
              select (Nullable<DateTime>)[<generated>_3].RecvTime).FirstOrDefault(),
          [sl]?.Name
       }'
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'GroupBy([met].MMSI, [met])' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'join ShipList sl in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ShipList]) on {from MetInfo <generated>_1 in [mg] select Convert([<generated>_1].MMSI, Nullable`1) => FirstOrDefault()} equals [sl].MMSI' could n
ot be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'from ShipList sl in {[ps] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'FirstOrDefault()' could not be translated and will be evaluated locally.
dbug: Microsoft.EntityFrameworkCore.Query[10107]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      (QueryContext queryContext) => IEnumerable<<>f__AnonymousType1<int, DateTime, string>> _InterceptExceptions(
      |__ source: IEnumerable<<>f__AnonymousType1<int, DateTime, string>> _Select(
      |   |__ source: IEnumerable<TransparentIdentifier<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>, ValueBuffer>> _SelectMany(
      |   |   |__ source: IEnumerable<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>> _GroupJoin(
      |   |   |   |__ outer: IEnumerable<IGrouping<int, MetInfo>> _GroupBy(
      |   |   |   |   |__ source: IEnumerable<MetInfo> _ShapedQuery(
      |   |   |   |   |   |__ queryContext: queryContext,
      |   |   |   |   |   |__ shaperCommandContext: SelectExpression:
      |   |   |   |   |   |       SELECT [met].[StnID], [met].[DAC], [met].[FI], [met].[Latitude], [met].[Longitude], [met].[MMSI], [met].[RecvTime]
      |   |   |   |   |   |       FROM [MetInfos] AS [met]
      |   |   |   |   |   |       ORDER BY [met].[MMSI], [met].[RecvTime] DESC,
      |   |   |   |   |   |__ shaper: BufferedEntityShaper<MetInfo>),
      |   |   |   |   |__ keySelector: (MetInfo met) => met.MMSI,
      |   |   |   |   |__ elementSelector: (MetInfo met) => met),
      |   |   |   |__ inner: IEnumerable<ValueBuffer> _ShapedQuery(
      |   |   |   |   |__ queryContext: queryContext,
      |   |   |   |   |__ shaperCommandContext: SelectExpression:
      |   |   |   |   |       SELECT [sl].[MMSI], [sl].[Name]
      |   |   |   |   |       FROM [ShipLists] AS [sl],
      |   |   |   |   |__ shaper: ValueBufferShaper),
      |   |   |   |__ outerKeySelector: (IGrouping<int, MetInfo> mg) => Nullable<int> FirstOrDefault(IEnumerable<Nullable<int>> _Select(
      |   |   |   |   |__ source: mg,
      |   |   |   |   |__ selector: (MetInfo <generated>_1) => (Nullable<int>)<generated>_1.MMSI)),
      |   |   |   |__ innerKeySelector: (ValueBuffer sl) => Nullable<int> TryReadValue(sl, 0, ShipList.MMSI),
      |   |   |   |__ resultSelector: (IGrouping<int, MetInfo> mg | IEnumerable<ValueBuffer> ps) => TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>> CreateTransparentIdentifier(
      |   |   |       |__ outer: mg,
      |   |   |       |__ inner: ps)),
      |   |   |__ collectionSelector: (TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>> t0) => IEnumerable<ValueBuffer> DefaultIfEmpty(t0.Inner),
      |   |   |__ resultSelector: (TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>> t0 | ValueBuffer sl) => TransparentIdentifier<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>, ValueBuffer> CreateTransparentIde
ntifier(
      |   |       |__ outer: t0,
      |   |       |__ inner: sl)),
      |   |__ selector: (TransparentIdentifier<TransparentIdentifier<IGrouping<int, MetInfo>, IEnumerable<ValueBuffer>>, ValueBuffer> t1) => new {
      |           (int)Nullable<int> FirstOrDefault(IEnumerable<Nullable<int>> _Select(
      |           |__ source: t1.Outer.Outer,
      |           |__ selector: (MetInfo <generated>_1) => (Nullable<int>)<generated>_1.MMSI)),
      |           (DateTime)Nullable<DateTime> FirstOrDefault(IEnumerable<Nullable<DateTime>> _Select(
      |           |__ source: t1.Outer.Outer,
      |           |__ selector: (MetInfo <generated>_3) => (Nullable<DateTime>)<generated>_3.RecvTime)),
      |           !(t1.Inner.IsEmpty) ? string TryReadValue(t1.Inner, 1, ShipList.Name) : default(string)
      |        }),
      |__ contextType: BloggingContext,
      |__ logger: DiagnosticsLogger<Query>,
      |__ queryContext: queryContext)
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [met].[StnID], [met].[DAC], [met].[FI], [met].[Latitude], [met].[Longitude], [met].[MMSI], [met].[RecvTime]
      FROM [MetInfos] AS [met]
      ORDER BY [met].[MMSI], [met].[RecvTime] DESC
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [met].[StnID], [met].[DAC], [met].[FI], [met].[Latitude], [met].[Longitude], [met].[MMSI], [met].[RecvTime]
      FROM [MetInfos] AS [met]
      ORDER BY [met].[MMSI], [met].[RecvTime] DESC
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [sl].[MMSI], [sl].[Name]
      FROM [ShipLists] AS [sl]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [sl].[MMSI], [sl].[Name]
      FROM [ShipLists] AS [sl]
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'Test' on server '(localdb)\mssqllocaldb'.
dbug

My code:

public class MetInfo
{
    [Key]
    public int StnID { get; set; }
    public int MMSI { get; set; }
    public DateTime RecvTime { get; set; }
    public Int16? DAC { get; set; }
    public Int16? FI { get; set; }
    public double Longitude { get; set; }
    public double Latitude { get; set; }    
}

public class ShipList
{
    [Key]
    public int ShipListID { get; set; }
    public int? MMSI { get; set; }
    public string Name { get; set; }
    public int? IMO { get; set; }
    public string CallSign { get; set; }
    public double? Latitude { get; set; }
    public double? Longitude { get; set; }
    public DateTime RecvTime { get; set; }
}

public class BloggingContext : DbContext
{
    private static readonly LoggerFactory Logger
        = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(
                @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
    
    public DbSet<MetInfo> MetInfos { get; set; }
    public DbSet<ShipList> ShipLists { get; set; }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new MetInfo
                {
                    MMSI = 1
                },
                new MetInfo
                {
                    MMSI = 1
                },
                new MetInfo
                {
                    MMSI = 2
                },
                new MetInfo
                {
                    MMSI = 3
                },
                new ShipList
                {
                    MMSI = 1,
                },
                new ShipList
                {
                    MMSI = 1,
                },
                new ShipList
                {
                    MMSI = 2,
                },
                new ShipList
                {
                    MMSI = null,
                },
                new ShipList
                {
                    MMSI = 4,
                }
            );

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var results = (from met in context.MetInfos
                orderby met.RecvTime descending
                group met by met.MMSI into mg
                join sl in context.ShipLists on mg.FirstOrDefault().MMSI equals sl.MMSI into ps from sl in ps.DefaultIfEmpty()
                select new { mg.FirstOrDefault().MMSI, mg.FirstOrDefault().RecvTime, sl.Name }).ToList();            
        }
    }
}

My output:

The output should display all MMSI since it was Grouped but your MMSI shows two MMSI with value of 1 and it does not show MMSI with value of 4 and null. Have you tried it with EF6? I think the result will be different

[smitpatel]

@ajcvickers - Your output is correct according to whatever seed data you have provided. i.e. there is no error there.
@mnhafiz23 - There is no MetInfo with with MMSI = 4, then why would the GroupBy produce result with 4?. value of 4 is on right side of left outer join hence filter out. Same is the case with null. Since it is GroupJoin-DefaultIfEmpty-SelectMany case, you would get as many rows as right side which would match to left side.
The Groupby would produce only 4 rows since there are only 4 rows of MetInfo and everything else on right filtered out or nulled out, output is going to have only 4 rows.

We need full repro with seed data to understand why the output is incorrect.

[mnhafiz23]

@smitpatel Ok. I'll try provide an example with seed. The thing that made me open this issue is because you will get a different result with EF6 using the same linq query and it gives out the expected result but EF Core does not

[mnhafiz23]

@ajcvickers my mistake. Ignore my previous reply to you. I get mixed up the object type when you're seeding but I was expecting the MMSI should be group.

[ajcvickers]

@mnhafiz23 I think we will need a full repro--that is a runnable project/solution or complete code listing--before we will be able to make any progress on this.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.