EfCore 3.1 Query Translation Issue: When using into keyword

[iby-dev]

I have simplified an older EfCore 2.2 query when migrating to Efcore 3.1, which is refusing to be translated at all

Source Code

Below is what the query looks like:

            var results = (
                from k in Set
                join broker in _ctx.Set<Contact>().AsNoTracking() on k.BrokerContactId equals broker.ContactId
                join brokF in _ctx.Set<Broker>().AsNoTracking() on broker.BrokerFirmId equals brokF.BrokerFirmId
                join brokerDetails in _ctx.Set<ContactContactDetail>().AsNoTracking().ToList()
                    on broker.ContactId equals brokerDetails.ContactId into bdetails

                join agent in _ctx.Set<Contact>().AsNoTracking() on k.UserReferenceNumber equals agent.UserReferenceNumber
                join agentDetails in _ctx.Set<ContactContactDetail>().AsNoTracking().ToList()
                    on agent.ContactId equals agentDetails.ContactId into adetails

                where k.KfiId == kfiId

                select new
                {
                    Application = k,
                    Agent = agent,
                    AgentDetails = adetails,
                    Broker = broker,
                    BrokerFirm = brokF,
                    BrokerDetails = bdetails
                }).ToList();

You can see, my where statement is simple; only require one row. Inbound KfiId is unique.

If i remove, the into statements above, of which there 2, the above queries does work however i get duplicates in my result set, that is because, the join that produces the range variable bdetails pulls back 4 rows. So a group by or a simple projection should ought to fix this but it does not.

Error

Re-instating the into statements, i get the below error:

Processing of the LINQ expression 'DbSet<KeyFactsIllustration>
    .Join(
        outer: DbSet<Contact>, 
        inner: k => k.BrokerContactId, 
        outerKeySelector: broker => broker.ContactId, 
        innerKeySelector: (k, broker) => new { 
            k = k, 
            broker = broker
         })
    .Join(
        outer: DbSet<Broker>, 
        inner: <>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.broker.BrokerFirmId, 
        outerKeySelector: brokF => brokF.BrokerFirmId, 
        innerKeySelector: (<>h__TransparentIdentifier0, brokF) => new { 
            <>h__TransparentIdentifier0 = <>h__TransparentIdentifier0, 
            brokF = brokF
         })
    .GroupJoin(
        outer: __p_0, 
        inner: <>h__TransparentIdentifier1 => <>h__TransparentIdentifier1.<>h__TransparentIdentifier0.broker.ContactId, 
        outerKeySelector: brokerDetails => brokerDetails.ContactId, 
        innerKeySelector: (<>h__TransparentIdentifier1, bdetails) => new { 
            <>h__TransparentIdentifier1 = <>h__TransparentIdentifier1, 
            bdetails = bdetails
         })' by '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.

Workarounds Tried

I have tried .ToList .AsEnumerable but, no matter where i place it, i get the same error.

So I am really puzzled why such a simple use case, is failing: 😕 - i did read the breaking changes docs too.

[smitpatel]

Duplicate of #17068