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

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.

CodePudding user response：

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()
    }