Complex request with external list and "except" or "all" operator could not be translated

[julienGrd]

Hello guys, i actually migrate an old .net framework/Entity Framework 6 app to .NET 7 Core 7 with Ef Core

I have a request wich failed in EF Core with this error

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<Histo_Enf>()
    .Join(
        inner: DbSet<Histo>(), 
        outerKeySelector: h => EF.Property<int?>(h, "HistoID"), 
        innerKeySelector: h0 => EF.Property<int?>(h0, "ID"), 
        resultSelector: (o, i) => new TransparentIdentifier<Histo_Enf, Histo>(
            Outer = o, 
            Inner = i
        ))
    .Where(h => !(__lAttIntervNums_0
        .Except(DbSet<Histo_Interv>()
            .Where(h1 => EF.Property<int?>(h.Inner, "ID") != null && object.Equals(
                objA: (object)EF.Property<int?>(h.Inner, "ID"), 
                objB: (object)EF.Property<int?>(h1, "HistoID")))
            .Select(h1 => h1.IntervID))
        .Any()))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. 

This the request. Both lTest and lTest2 failed (suppose to be functionnaly equivalent, lTest is the original request, lTest2 is how i rewrite to make it mode understandable. lTest wortk perfectly in the old entity framework 6 app (didnt try for lTest2)

List<short> lAttIntervNums =new List<short>{ 2, 3 };
 var lTest = pContainer.Histos_Enfs.Where(lHe => !lAttIntervNums.Except(lHe.Histo.HIntervs.Select(lHi => lHi.IntervID)).Any()).ToList();//fail
 var lTest2 = pContainer.Histos_Enfs.Where(lHe => lAttIntervNums.All(i => lHe.Histo.HIntervs.Any(lHi => lHi.IntervID == i))).ToList();//fail

What do you think about that ? is it suppose to work or it show a problem on my model ?

thanks !

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[julienGrd]

@ajcvickers you will find a small repro project here : https://github.com/julienGrd/TestViewEfCore

create the database with the script.sql, configure your connexion string in program.cs and run the app. it will fail on the first request (the second request fail too).

Thanks !

[roji]

The queries above apply arbitrary LINQ operators (Except, All) to a parameterized list - this isn't supported in EF 6/7, which only have specific support for Contains.

Support for this was implemented in the upcoming 8.0 release (see #30426) - you can try out 8.0.0-rc.1 and see how those queries work.

[roji]

Duplicate of #30426