The LINQ expression could not be translated after upgrading to .NET 6 from .NET 5

[Jase7]

Hi, there!

I encountered an issue in the same project after upgrading to .NET 6 / EF Core 6

This is the little piece of code with .NET 5 / EF Core 5 that worked well:

var customerIds = _db.Customers.Where(
        x => x.CustomerCarriers.Any(c=> c.CarrierCode == filter.CarrierCode))
    .Select(x => x.Id);

var depotIds = _db.LoadingOrderDistributions.Where(
        x => customerIds.Contains(x.CustomerId) && x.Published)
    .Select(x => x.DepotId);

query = query.Where(x => depotIds.Contains(x.Id));

Here's an example of the query resulting is:

SELECT [d].[Code], [d].[DepotType], [d].[Description], [d].[UnloadingOrder]
FROM [Depots] AS [d]
WHERE ([d].[Enabled] = CAST(1 AS bit)) AND EXISTS (
    SELECT 1
    FROM [LoadingOrderDistributions] AS [l]
    WHERE (([l].[Enabled] = CAST(1 AS bit)) AND ([l].[CustomerId] IN (
        SELECT [c].[Id]
        FROM [Customers] AS [c]
        WHERE ([c].[Enabled] = CAST(1 AS bit)) AND EXISTS (
            SELECT 1
            FROM [CustomerCarriers] AS [c0]
            WHERE (([c0].[Enabled] = CAST(1 AS bit)) AND ([c].[Id] = [c0].[CustomerId])) AND ([c0].[CarrierCode] = '3'))
    ) AND ([l].[Published] = CAST(1 AS bit)))) AND ([l].[DepotId] = [d].[Id]))
ORDER BY [d].[Description]
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

After the upgrade to .NET 6 / EF Core we encountered an issue with this little piece of code and the consequent translation to SQL.
Here's is the exception resulting:

System.InvalidOperationException: The LINQ expression 'DbSet<Customer>()
          .Where(c => c.Enabled)
          .Where(c => DbSet<CustomerCarrier>()
              .Where(c0 => c0.Enabled)
              .Where(c0 => EF.Property<int?>(c, "Id") != null && object.Equals(
                  objA: (object)EF.Property<int?>(c, "Id"),
                  objB: (object)EF.Property<int?>(c0, "CustomerId")))
              .Any(c0 => c0.CarrierCode == __filter_CarrierCode_0))
          .Select(c => c.Id)
          .Contains(EntityShaperExpression:
              JSV.AMFresh.Production.API.Models.LoadingOrderDistribution
              ValueBufferExpression:
                  ProjectionBindingExpression: EmptyProjectionMember
              IsNullable: False
          .CustomerId)' could not be translated

Obviously changing the query to client evaluation works fine changing the code to:

var customerIds = _db.Customers.Where(
        x => x.CustomerCarriers.Any(c => c.CarrierCode == filter.CarrierCode))
    .Select(x => x.Id);

var depotIds = _db.LoadingOrderDistributions.Where(
        x => customerIds.Contains(x.CustomerId) && x.Published)
    .Select(x => x.DepotId)
    .ToList();

query = query.Where(x => depotIds.Contains(x.Id));

Is this maybe a regression?

EF Core version: 6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.0.0

[ajcvickers]

@smitpatel Possible duplicate of #26593?

[smitpatel]

Yes, it is duplicate. Query contains recursive Contains (no pun intended).