EF Core : Union returns unable to translate set operation since both operands have different 'Include' operations

I'm working on a .NET Core 6 project with EF Core 6, and I'm attempting to perform a union operation on a LINQ query. However, I consistently encounter an error stating "unable to translate to SQL." The two entities I'm trying to concatenate are identical and in the same order regarding the definition of their fields. Therefore, I'm confuse as to why this issue arises and why it can't be translated into a SQL UNION , encountering the error "EF Core returns unable to translate set operation since both operands have different 'Include' operations".

After thorough investigation, we found that this error typically occurs when performing set operations (such as Union, Intersect, or Except) on IQueryable collections that have different 'Include' operations specified.

To illustrate this, let's consider an example:

var query1 = dbContext.Product.Include(t1 => t1.Category);
var query2 = dbContext.ProductBackUp.Include(t2 => t2.ProductModel);

var result = query1.Union(query2).ToList();
    

In this scenario, if Product and ProductBackUphave different navigation properties included (Category for Product and ProductModel for ProductBackUp), EF Core encounters difficulty in translating the set operation (Union) because the included navigation properties are not compatible.

The solution to this error involves ensuring that both IQueryable collections involved in the set operation have the same 'Include' operations specified. We need to same the 'Include' operations across both queries to achieve compatibility.

Here's an updated version of the example with consistent 'Include' operations:

var query1 = dbContext.Product.Include(t1 => t1.Category);
var query2 = dbContext.ProductBackUp.Include(t2 => t2.Category); // Consistent 'Include' operation

var result = query1.Union(query2).ToList();
    

By aligning the 'Include' operations for both IQueryable collections, we enable EF Core to translate the set operation without encountering the "unable to translate set operation since both operands have different 'Include' operations" error.

2

When we faced with the same issue using Entity Framework, we discovered a simple solution that proved to be quite effective. we implemented the following approach:

(
    from item in _context.Product1
    select new ProductDTO
    {
        Id=item.Id,
        Name= Convert.ToString(item.Name)
    }
)
.Union
(
    from item in _context.Product2
    select new ProductDTO
    {
        Id=item.Id,
        Name= Convert.ToString(item.Name)
    }
)
    

Even though our entity was already of type string, we found it necessary to explicitly convert it on both sides of the union operation. This ensured that both sides were operating on the same data type, effectively resolving the issue.

By ensuring consistency in data types on both sides of the union operation, we eliminate any potential mismatches and enable seamless execution of the query.

3

The error message "Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call" prompted us to check deeper into the feature of Entity Framework Core (EF Core) operations.

We discovered that this error typically occurs when performing set operations (such as Union, Intersect, or Except) on IQueryable collections after applying client-side projection (Select).

To check this scenario, let's consider an example:

var query = dbContext.Table.Select(item => new SomeDto
{
    // Projecting properties
    Property1 = item.Property1,
    Property2 = item.Property2
});

var result = query.Union(anotherQuery).ToList();
    

In this example, the error occurs because the Union operation is applied after client-side projection (Select), which EF Core cannot translate into SQL.

The solution to this error involves moving the set operation (Union, Intersect, Except) before the client-side projection (Select) call. By doing so, we allow EF Core to translate the entire query into SQL and execute it successfully.

Here's an correct version of the example with the set operation moved before the projection:

var query = dbContext.Table;

var result = query.Union(anotherQuery).Select(item => new SomeDto
{
    // Projecting properties
    Property1 = item.Property1,
    Property2 = item.Property2
}).ToList();
    

4

We encountered an error stating, "Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call." 

To resolve this issue, we need to ensure that the set operation occurs before the final 'Select' call in our query. This means moving the set operation earlier in the query chain, preferably before any projection operations.

Let's consider an example check below query:

var query = dbContext.ProductA
                    .Where(a => a.Condition)
                    .Select(a => new
                    {
                        Property1 = a.Property1,
                        Property2 = a.Property2
                    })
                    .Union(dbContext.ProductB
                                 .Where(b => b.Condition)
                                 .Select(b => new
                                 {
                                     Property1 = b.Property1,
                                     Property2 = b.Property2
                                 }));
                    .ToList();

In above example, the Union operation is performed after client-side projection, leading to the mentioned error, to fix this, we need to move the Union operation before the Select call:

var query = dbContext.ProductA

                    .Where(a => a.Condition)
                    .Union(dbContext.ProductB
                                 .Where(b => b.Condition))
                    .Select(a => new
                    {
                        Property1 = a.Property1,
                        Property2 = a.Property2
                    })
                    .ToList();