Can't process set operations after client evaluation efcore

user_2744722 0
Viewed 5123

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

entity-framework-core .net-core c#
4 Answers

Add .AsEnumerable() for the first query before .Union would fix the issue

to be as following

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,
          }).AsEnumerable()
          .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);
user_7404520

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)

user_106463160
user_12800680

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
    }
    )
user_168657990
Related