'Can't process set operations after client evaluation efcore

Ef Core receiving error

System.InvalidOperationException: Can't process set operations after client evaluation, consider moving the operation before the last Select() call (see issue #16243) at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplySetOperation(SetOperationType setOperationType, SelectExpression select2, Boolean distinct)

when executing

 public async Task<object> GetUnitsForDataTableAsync() =>
        
            await context.Units
             .Where(x => !x.TractUnitJunctions.Any())
              .Select(x => new
              {
                  x.Id,
                  x.UnitName,
                  x.UnitAcres,
                  TractNum = String.Empty,
                  Wells = String.Empty,
                  NumOfWells = 0,
              })
              .Union(
                        context.TractUnitJunctions
                        .Select(x => new
                         {
                            Id = x.UnitId,
                            x.Unit.UnitName,
                            x.Unit.UnitAcres,
                            x.Tract.TractNum,
                            Wells = string.Join(", ", x.TractUnitJunctionWellJunctions
                                              .Select(z => $"{z.Well.WellNum} ({z.Well.ApiNum})")
                                        ),
                            NumOfWells = x.TractUnitJunctionWellJunctions.Count()
                 }))
                .ToListAsync().ConfigureAwait(false);

however the function works fine if I break it up into two queries.

 public async Task<object> GetUnitsForDataTableAsync()
        {
            var List1 = await context.Units
             .Where(x => !x.TractUnitJunctions.Any())
              .Select(x => new
              {
                  x.Id,
                  x.UnitName,
                  x.UnitAcres,
                  TractNum = String.Empty,
                  Wells = String.Empty,
                  NumOfWells = 0,
              })
             .ToListAsync().ConfigureAwait(false);

            var List2 = await context.TractUnitJunctions
                 .Select(x => new
                 {
                     Id = x.UnitId,
                     x.Unit.UnitName,
                     x.Unit.UnitAcres,
                     x.Tract.TractNum,
                     Wells = string.Join(", ", x.TractUnitJunctionWellJunctions
                                              .Select(z => $"{z.Well.WellNum} ({z.Well.ApiNum})")
                                        ),
                     NumOfWells = x.TractUnitJunctionWellJunctions.Count()
                 })
                .ToListAsync().ConfigureAwait(false);


            return List1.Concat(List2);
        }

I've researched that error a bit but I'm unsure how to refactor the first query to get around that error

c++sharp.net-coreentity-framework-core


Solution 1:[1]

Accepted answer while it works is flawed in that you're eager loading the first list.

This is a known bug which you should be able to resolve by moving the Where clause after the Union

As discussed here https://github.com/dotnet/efcore/issues/16243#issuecomment-622452276

Solution 2:[2]

This is a limitation of EF Core and I don't think it will be resolved ever. First list is easily translatable to the SQL, but second list is eager loading query with client side evaluation part and you cannot use it with Concat function. So you have to run two queries and make union on the client side.

Anyway this query can be easily written by the SQL (depends on dialect) without Concat and eager loading. Or via LINQ with appropriate extension (will provide solution on request)

Solution 3:[3]

Using LINQ to SQL let keyword
Today, I also encountered such a problem, which was solved in this way! You can try changing it like this:

    (from u in context.Units
    let TractNum= String.Empty
    let Wells = String.Empty  
    let NumOfWells = 0
    select new 
    {
       u.Id,
       u.UnitName,
       u.UnitAcres,
       TractNum 
       Wells
       NumOfWells
    }).union(
    from u in context.Units
    let TractNum= String.Empty
    let Wells = String.Empty  
    let NumOfWells = 0
    select new 
    {
       u.Id,
       u.UnitName,
       u.UnitAcres,
       TractNum 
       Wells
       NumOfWells
    }
    )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 stuartdotnet
Solution 2
Solution 3 ouflak

Related Questions