How do I mimic a SQL Outer Apply in Linq using Entity Framework?

Question

I would like to mimic a SQL OUTER APPLY using linq.

I have 2 tables: Main and Sub

The SQL looks something like this:

select 
  M.Id, M.Number, M.Stuff, SD.SubName 
from 
  Main as M
outer apply (
  select top 1 SubName
  from Sub S
  where M.Id = S.Id and M.Number = S.Number
) as SD

Based answers here and elsewhere like this one,I've tried too many iterations of Linq to put in here, but here's one:

var query1 = 
from m in dbContext.Main
join s in dbContext.Sub on new {m.Id, m.Number} equals new {s.Id, s.Number} into subs
select new
{
  m,
  SubName = subs.FirstOrDefault().SubName
}

This compiles fine, but when I run it I get this exception:

Processing of the LINQ expression 'DbSet<Main>
// EF's attempt to translate my query
'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

and a stack trace.

Does anyone have any suggestions on how to go about coding this the correct way?

I'm running .NET core 3.1 against SQL Server 2017.

Answer 1

Try the following queries. EF Core 3.1, should translate this to Outer Appply, but higher versions may use JOIN and ROW_NUMBER

var query1 = 
    from m in dbContext.Main
    from s in dbContext.Sub
        .Where(s => m.Id == s.Id && m.Number == s.Number)
        .Take(1)
        .DefaultIfEmpty()
    select new
    {
        m,
        SubName = s.SubName
    }

Or this variant:

var query1 = 
    from m in dbContext.Main
    select new
    {
        m,
        SubName = dbContext.Sub
            .Where(s => m.Id == s.Id && m.Number == s.Number)
            .Select(s => s.SubName)
            .FirstOrDefaut()
    }