Set operations: support when placed after client evaluation in projection

[roji]

Test case:

public virtual Task Client_eval_Union_FirstOrDefault(bool isAsync)
    => AssertFirstOrDefault<Customer>(
        isAsync, cs => cs
            .Select(c => ClientSideMethod(c))
            .Union(cs));

Can probably be punted post-3.0 (can be easily worked around).

[cyptus]

the following query is working as expected but fails in combination with union with the exception System.InvalidOperationException: 'Can't process set operations after client evaluation, consider moving the operation before the last Select() call (see issue #16243)':

var query1 = context.Articles.Where(x => x.IsArchived == true).Select(x => new ArticleDto()
{
    ArticleId = x.ArticleId,
    IsArchived = true,
    Comments = x.Comments.Select(c => new CommentDto()
    {
        CommentId = c.CommentId
    })
});

is working, but failing with union:

var query1 = context.Articles.Where(x => x.IsArchived == true).Select(x => new ArticleDto()
{
    ArticleId = x.ArticleId,
    IsArchived = true,
    Comments = x.Comments.Select(c => new CommentDto()
    {
        CommentId = c.CommentId
    })
});

var query2 = context.Articles.Where(x => x.IsArchived == false).Select(x => new ArticleDto()
{
    ArticleId = x.ArticleId,
    IsArchived = false,
    Comments = x.Comments.Select(c => new CommentDto()
    {
        CommentId = c.CommentId
    })
});

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

when removing Comments selection the query works fine within union.

Is this the same issue like from @roji 's example as the comments selection itself is not a client evaulation?
When using InMemoryDatabase instead of SQL this works fine.

Full example code: https://gist.github.com/cyptus/ef80cb7a58aa08ab06da4cd42da1ec06

[roji]

Yes, your query is the same as what I posted originally. You should be able to rewrite this query to work by moving the Union earlier in your query, which also makes for less duplication:

context.Articles
    .Where(x => x.IsArchived == true)
    .Union(context.Articles.Where(x => x.IsArchived == false)
    .Select(x => new ArticleDto() {
        ArticleId = x.ArticleId,
        IsArchived = x.IsArchived,
        Comments = x.Comments.Select(c => new CommentDto()
        {
            CommentId = c.CommentId
        })
    })
    .ToList();

(of course, in your particular query you don't need a Union at all since you're just doing it over IsArchived true/false, but I'm assuming this is only a minimal example of some sort).

[Maleaume]

Your example is not complete because you request on same table
I have the use case with two tables with one different column but i want to make a union in a common object. I Can't I have the same error

[TableA]
class A {
    public string NameA;
    public string DescriptionA;
}

[TableB]
class B {
    public string NameB;
    public string DescriptionB;
}

class UnionClass
{
    public string Name;
    public string Description;
}

I would like to

var queryableA  = Context.A.select( x => new UnionClass{ Name =x.NameA,Description = x.DescriptionA);
var queryableB = Context.Bselect( x => new UnionClass{ Name =x.NameB,Description = x.DescriptionB);

var queryUnion = queryableA.Union(queryableB)
var result = queryUnion .Select( x=> x.Name.contains("RE));

[brandonsmith86]

I have the same use-case as @Maleaume. How do you suggest we workaround the issue in this case?

[BeejeeDS]

I have the same scenario as @Maleaume.
I even want to add paging to it, so i'll be needing a Skip and Take after the Union.

[roji]

@Maleaume @brandonsmith86 @BeejeeDS the only issue here is with set operations after a client projection - as long as you write your query to place the union before that, there's no issue. You can typically do this by projecting to an anonymous type (which works fine and doesn't require client translation) and doing everything you need on that. If you need to project out to your own type, do that at the end.

For example:

var result = ctx.A
    .Select(a => new { Name = a.NameA, Description = a.DescriptionA })
    .Union(ctx.B
        .Select(b => new { Name = b.NameB, Description = b.DescriptionB }))
    .Where(x => x.Name.StartsWith("a"))
    // Add any further operations to be executed in the database here
    .Select(x => new UnionClass
    {
        Name = x.Name,
        Description = x.Description
    })
    .ToList();

[fingers10]

I have the same issue but I'm trying Union for two Join queries.

var result = await _context.Stage.Join(_context.User, s => s.UserNo, u => u.UserNo, (s, u) => new { s, u })
           .Select(c => new { Detail = $"{c.s.Description.Trim()} - {c.u.UserDescription.Trim()}", Id = c.s.Id })
              .Union(
                _context.Stage.Join(_context.UserGroup, s => s.UserGroupNo, ug => ug.UserGroupNo, (s, ug) => new { s, ug })
           .Select(c => new { Detail = $"{c.s.Description.Trim()} - {c.ug.GroupDesription.Trim()}", Id = c.s.Id })
                          )
                          .Where(c => c.Id == Id)
                          .Select(x => x.Detail)
                          .ToListAsync();

and I get the same error. Am I doing anything wrong here? Please assist

[roji]

@fingers10's error repros for me, although removing the Union shows the query working without any sort of client evaluation. @smitpatel any ideas?

Repro code

class Program
{
    static async Task Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var result = await ctx.Stage
            .Join(ctx.User, s => s.UserNo, u => u.UserNo, (s, u) => new { s, u })
            .Select(c => new { Detail = $"{c.s.Description.Trim()} - {c.u.UserDescription.Trim()}", Id = c.s.Id })
            .Union(
                ctx.Stage
                    .Join(ctx.UserGroup, s => s.UserGroupNo, ug => ug.UserGroupNo, (s, ug) => new { s, ug })
                    .Select(c => new { Detail = $"{c.s.Description.Trim()} - {c.ug.GroupDesription.Trim()}", Id = c.s.Id })
            )
            .Where(c => c.Id == 8)
            .ToListAsync();
    }
}

public class BlogContext : DbContext
{
    public DbSet<Stage> Stage { get; set; }
    public DbSet<User> User { get; set; }
    public DbSet<UserGroup> UserGroup { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Stage
{
    public int Id { get; set; }
    public int UserNo { get; set; }
    public int UserGroupNo { get; set; }
    public string Description { get; set; }
}

public class User
{
    public int Id { get; set; }
    public int UserNo { get; set; }
    public string UserDescription { get; set; }
}

public class UserGroup
{
    public int Id { get; set; }
    public int UserNo { get; set; }
    public string GroupDesription { get; set; }
    public int UserGroupNo { get; set; }
}

[smitpatel]

Interpolated string is client evaluation.

[roji]

Of course, I somehow missed that, thanks...

@fingers10 you'll need to rewrite your query to move the trimming out to after the Select:

var result = await ctx.Stage
    .Join(ctx.User, s => s.UserNo, u => u.UserNo, (s, u) => new { s, u })
    .Select(c => new { Detail = c.s.Description, Description = c.u.UserDescription, Id = c.s.Id })
    .Union(
        ctx.Stage
            .Join(ctx.UserGroup, s => s.UserGroupNo, ug => ug.UserGroupNo, (s, ug) => new { s, ug })
            .Select(c => new { Detail = c.s.Description, Description = c.ug.GroupDesription, Id = c.s.Id })
    )
    .Where(c => c.Id == 8)
    .Select(c => new { Detail = $"{c.Detail.Trim()} - {c.Description.Trim()}" })
    .ToListAsync();

FWIW that also removes the needless duplication.

[fingers10]

@roji I'm getting this error - Set operations over different store types are currently unsupported, after rewriting the query as you mentioned.

[roji]

@fingers10 you need to post your full query (and preferably model as well) for me to be able to help. The query I posted above worked for me.

[fingers10]

@roji No query is getting generated for me and I get that exception. Here are my models,

Stage:

public class stage
{  
    public int tmpno { get; set; }
    public int uno { get; set; }
    public int ugno { get; set; }
    public int stageno { get; set; }
    public string stagedes { get; set; }   
}

User:

public class user
{ 
    public int usrno { get; set; }
    public string usrid { get; set; }
}

UserGroup:

public class usergroup
{
    public int usrgrpno { get; set; }
    public string grpdesc { get; set; }
}

LINQ:

var result = await _context.stage.Join(_context.user, s => s.uno, u => u.usrno, (s, u) => new { s, u })
                .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.u.usrid, TemplateId = c.s.tmpno })
                //.AsEnumerable()
            .Union(
                _context.stage.Join(_context.usergroup, s => s.ugno, ug => ug.usrgrpno, (s, ug) => new { s, ug })
                .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.ug.grpdesc, TemplateId = c.s.tmpno })
                )
            .Where(c => c.TemplateId == templateId)
            .Select(x => $"{x.StageDescription.Trim()} - {x.UserDescription.Trim()}")
            .ToListAsync();

If I add AsEnumerable() before Union it works but Union happens on client side.

[roji]

Your query executes without errors for me, see below. Note that I had to explicitly specify keys.

Full repro

class Program
{
    static async Task Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var templateId = 8;
        
        var result = await ctx.stage.Join(ctx.user, s => s.uno, u => u.usrno, (s, u) => new { s, u })
            .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.u.usrid, TemplateId = c.s.tmpno })
            //.AsEnumerable()
            .Union(
                ctx.stage.Join(ctx.usergroup, s => s.ugno, ug => ug.usrgrpno, (s, ug) => new { s, ug })
                    .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.ug.grpdesc, TemplateId = c.s.tmpno })
            )
            .Where(c => c.TemplateId == templateId)
            .Select(x => $"{x.StageDescription.Trim()} - {x.UserDescription.Trim()}")
            .ToListAsync();
    }
}

public class BlogContext : DbContext
{
    public DbSet<stage> stage { get; set; }
    public DbSet<user> user { get; set; }
    public DbSet<usergroup> usergroup { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class stage
{  
    [Key]
    public int tmpno { get; set; }
    public int uno { get; set; }
    public int ugno { get; set; }
    public string stagedes { get; set; }   
}

public class user
{ 
    [Key]
    public int usrno { get; set; }
    public string usrid { get; set; }
}

public class usergroup
{
    [Key]
    public int usrgrpno { get; set; }
    public string grpdesc { get; set; }
}

[fingers10]

@roji Here is model builder configuration,

Stage Entity has composite key is that because of this it is not working?

Stage:

modelBuilder.Entity<stage>(entity =>
{
    entity.HasKey(e => new { e.tmpno, e.stageno });
});

And For User and UserGroup:

public class user
{ 
    [Key]
    public int usrno { get; set; }
    public string usrid { get; set; }
}

public class usergroup
{
    [Key]
    public int usrgrpno { get; set; }
    public string grpdesc { get; set; }
}

[fingers10]

@roji any suggestions/inputs from your end?

[roji]

@fingers10 this is working fine with composite keys too, see below. If you're still having trouble, can you please submit a complete runnable code sample which throws, rather than fragments?

Repro that works

class Program
{
    static async Task Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var templateId = 8;
        
        var result = await ctx.stage.Join(ctx.user, s => s.uno, u => u.usrno, (s, u) => new { s, u })
            .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.u.usrid, TemplateId = c.s.tmpno })
            //.AsEnumerable()
            .Union(
                ctx.stage.Join(ctx.usergroup, s => s.ugno, ug => ug.usrgrpno, (s, ug) => new { s, ug })
                    .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.ug.grpdesc, TemplateId = c.s.tmpno })
            )
            .Where(c => c.TemplateId == templateId)
            .Select(x => $"{x.StageDescription.Trim()} - {x.UserDescription.Trim()}")
            .ToListAsync();
    }
}

public class BlogContext : DbContext
{
    public DbSet<stage> stage { get; set; }
    public DbSet<user> user { get; set; }
    public DbSet<usergroup> usergroup { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<stage>(entity =>
        {
            entity.HasKey(e => new { e.tmpno, e.stageno });
        });
    }
}

public class stage
{  
    public int tmpno { get; set; }
    public int stageno { get; set; }
    public int uno { get; set; }
    public int ugno { get; set; }
    public string stagedes { get; set; }   
}

public class user
{ 
    [Key]
    public int usrno { get; set; }
    public string usrid { get; set; }
}

public class usergroup
{
    [Key]
    public int usrgrpno { get; set; }
    public string grpdesc { get; set; }
}

[fingers10]

@roji Sorry for the delayed response. Here is the complete entity model and query with entity configuration.

User:

public class user
{ 
    [Key]
    public int usrno { get; set; }
    public string usrid { get; set; }
    public string usrpswd { get; set; }
    public int usrgrpno { get; set; }
    public string usrsts { get; set; }
    public int usrcrtdat { get; set; }
    public int usrexpdat { get; set; }
    public Int16 adminprivi { get; set; }
}

UserGroup:

public class usergroup
{
    [Key]
    public int usrgrpno { get; set; }
    public string grpdesc { get; set; }
    public int grpsec { get; set; }
    public string schtmpid { get; set; }
    public string idxtmpid { get; set; }
    public string savtmpid { get; set; }
    public string cdarctmp { get; set; }
    public string cdidxtmp { get; set; }
    public string cdschtmp { get; set; }
    public string cdsavtmp { get; set; }
    public int? flrno { get; set; }
    public string f1 { get; set; }
    public string f2 { get; set; }
    public string f3 { get; set; }
}

Stage:

public class stage
{
    public int tmpno { get; set; }
    public int stageno { get; set; }
    public int uno { get; set; }
    public int ugno { get; set; }
    public int timing { get; set; }
    public int mustdo { get; set; }
    public int exptmpno { get; set; }
    public string stagedes { get; set; }
    public int timecc { get; set; }
    public int exceptioncc { get; set; }
    public int addstamp { get; set; }
    public string pretext { get; set; }
    public string trailtext { get; set; }
    public int showuser { get; set; }
    public int upd { get; set; }
    public int autoend { get; set; }
    public int fpage { get; set; }
    public int standHorAction { get; set; }
    public int jaemail { get; set; }   
}

Configuration:

modelBuilder.Entity<stage>(entity =>
{
    entity.HasKey(e => new { e.tmpno, e.stageno });
});

LINQ:

var result = _context.stage.Join(_context.user, s => s.uno, u => u.usrno, (s, u) => new { s, u })
                        .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.u.usrid, TemplateId = c.s.tmpno })
                        //.AsEnumerable()
                    .Union(
                        _context.stage.Join(_context.usergroup, s => s.ugno, ug => ug.usrgrpno, (s, ug) => new { s, ug })
                        .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.ug.grpdesc, TemplateId = c.s.tmpno })
                        )
                    .Where(c => c.TemplateId == templateId)
                    .Select(x => $"{x.StageDescription.Trim()} - {x.UserDescription.Trim()}")
                    .ToList();

And I get this error Set operations over different store types are currently unsupported, if I don't add AsEnumerable() to the first query

[roji]

@fingers10 once again, your code works just fine for me when I paste it into a Program.cs.

Please, take the time to create a full, runnable Program.cs - use the below code as your starting point.

Attempted repro

class Program
{
    static async Task Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();
              
        var templateId = 8;

        var result = ctx.stage.Join(ctx.user, s => s.uno, u => u.usrno, (s, u) => new { s, u })
            .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.u.usrid, TemplateId = c.s.tmpno })
            //.AsEnumerable()
            .Union(
                ctx.stage.Join(ctx.usergroup, s => s.ugno, ug => ug.usrgrpno, (s, ug) => new { s, ug })
                    .Select(c => new { StageDescription = c.s.stagedes, UserDescription = c.ug.grpdesc, TemplateId = c.s.tmpno })
            )
            .Where(c => c.TemplateId == templateId)
            .Select(x => $"{x.StageDescription.Trim()} - {x.UserDescription.Trim()}")
            .ToList();
    }
}

public class BlogContext : DbContext
{
    public DbSet<stage> stage { get; set; }
    public DbSet<user> user { get; set; }
    public DbSet<usergroup> usergroup { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(...)
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<stage>(entity =>
        {
            entity.HasKey(e => new { e.tmpno, e.stageno });
        });
    }
}

public class user
{ 
    [Key]
    public int usrno { get; set; }
    public string usrid { get; set; }
    public string usrpswd { get; set; }
    public int usrgrpno { get; set; }
    public string usrsts { get; set; }
    public int usrcrtdat { get; set; }
    public int usrexpdat { get; set; }
    public Int16 adminprivi { get; set; }
}

public class usergroup
{
    [Key]
    public int usrgrpno { get; set; }
    public string grpdesc { get; set; }
    public int grpsec { get; set; }
    public string schtmpid { get; set; }
    public string idxtmpid { get; set; }
    public string savtmpid { get; set; }
    public string cdarctmp { get; set; }
    public string cdidxtmp { get; set; }
    public string cdschtmp { get; set; }
    public string cdsavtmp { get; set; }
    public int? flrno { get; set; }
    public string f1 { get; set; }
    public string f2 { get; set; }
    public string f3 { get; set; }
}

public class stage
{
    public int tmpno { get; set; }
    public int stageno { get; set; }
    public int uno { get; set; }
    public int ugno { get; set; }
    public int timing { get; set; }
    public int mustdo { get; set; }
    public int exptmpno { get; set; }
    public string stagedes { get; set; }
    public int timecc { get; set; }
    public int exceptioncc { get; set; }
    public int addstamp { get; set; }
    public string pretext { get; set; }
    public string trailtext { get; set; }
    public int showuser { get; set; }
    public int upd { get; set; }
    public int autoend { get; set; }
    public int fpage { get; set; }
    public int standHorAction { get; set; }
    public int jaemail { get; set; }   
}

[fingers10]

@roji Thanks. I'll get back to you with working sample.

[davidyee]

@roji I noticed a similar issue in our code when trying to union or concat two queries. The tip to use projection to an anonymous type seems to work mostly. However, it doesn't work if we have one of the projections with statically defined data.

On some investigation it seems that it only works when the projection is using actual data from the query/tables. I assume this might be caused as a result of the underlying type configuration being unknown when defining the field value manually (I don't know what the specific technical term in EF Core for this is but I assume it's related to the fluent/convention configuration we do in our application's DbContext).

For example defining anonymous type with a decimal Quantity set to a fixed value and trying to do a concat or union fails:

DbContext.MyModel.Select(e => new { 
  Name = e.Name, // concat/union with actual columns from the table/query works
  Quantity = 1M  // adding static fields like this does not
});

While there are no compile errors because of the type match, the runtime error occurs only when I define static data for at least one column like the above example. Same issue occurs even with other types such as a string.

Is this a known issue being tracked elsewhere?

[roji]

@davidyee I couldn't reproduce this issue with the code below. Can you please open a new issue with a runnable code sample that shows the issue, along with the full exception you're seeing etc?

Attempted repro

Code:

class Program
{
    static void Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var results = ctx.Blogs
            .Where(b => b.Name == "a")
            .Select(b => new { Blog = b, SomeConstant = 1 })
            .Union(ctx.Blogs
                .Where(b => b.Name == "b")
                .Select(b => new { Blog = b, SomeConstant = 2 }))
                .ToList();
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

SQL:

SELECT [b].[Id], [b].[Name], 1 AS [SomeConstant]
FROM [Blogs] AS [b]
WHERE [b].[Name] = N'a'
UNION
SELECT [b0].[Id], [b0].[Name], 2 AS [SomeConstant]
FROM [Blogs] AS [b0]
WHERE [b0].[Name] = N'b'

[CemYz-ux]

@roji It seems like, the solutions mentioned above do not work if I use sub selects inside the queries.

In my example, the properties I want to select represent Lists instead of strings.

I have tried using anonymus types, but that doesn't work, for the sub selects.

Entities:

class entityA {
    public List<VersionA> Versions;
    public string CreatedBy;
}

class entityB {
    public List<VersionB> Versions;
    public string CreatedBy;
}

class VersionA {
    public string TitleA;
    public Instant EffectiveTo;
}

class VersionB {
    public string TitleB;
    public Instant EffectiveTo;
}

class UnionDto{
    public string Title;
    public Guid Creator;
}

Setting up the query like this:

var queryA = databaseContext.Set<entityA>()
                .Select(entity => new
                {
                    Versions = entity.Versions
                        .Where(version => version.EffectiveTo > now)    /* Filtering newest entity Version */ 
                        .Select(versionDetail => new               /* Selecting only the Title of this Version */
                        {
                            Title = versionDetail.TitleA
                        })
                        .ToList(),
                    Creator = entity.CreatedBy,
                });

var queryB = databaseContext.Set<entityB>()
                .Select(entity => new
                {
                    Versions = entity.Versions
                        .Where(version => version.EffectiveTo > now)
                        .Select(versionDetail => new 
                        {
                            Title = versionDetail.TitleB
                        })
                        .ToList(),
                    Creator = entity.CreatedBy,
                });

Executing the query:

var unionDto = await queryA
                .Union(queryB)
                .Where(q => q.Creator == someUserGuid)
                .Skip(0)
                .Take(20)
                .Select(x => new UnionDto
                {
                    Versions = x.Versions,
                    Creator = x.Creator,
                })
                .ToListAsync();

Is there a workaround available?

[damikun]

@Maleaume @brandonsmith86 @BeejeeDS the only issue here is with set operations after a client projection - as long as you write your query to place the union before that, there's no issue. You can typically do this by projecting to an anonymous type (which works fine and doesn't require client translation) and doing everything you need on that. If you need to project out to your own type, do that at the end.

For example:

var result = ctx.A
    .Select(a => new { Name = a.NameA, Description = a.DescriptionA })
    .Union(ctx.B
        .Select(b => new { Name = b.NameB, Description = b.DescriptionB }))
    .Where(x => x.Name.StartsWith("a"))
    // Add any further operations to be executed in the database here
    .Select(x => new UnionClass
    {
        Name = x.Name,
        Description = x.Description
    })
    .ToList();

Hi @roji Doing this way i will lost ability to use Interfaces.. Do I ?

Because image that i need to query 2 different types and have ability to pass it to Interface...

>     .Select(x => new UnionClass
>     {
>         Name = x.Name,
>         Subtype=  // **Type1 or Type2**
>     })

For now i`m doing this ugly way:

            // Load JobAssignedUser
            List<JobAssignedUser> jobsresponse = await _dbContextPool.Users
                .Where(s => keys.Contains(s.ID))
                .Include(e => e.ConnectedJobs).ThenInclude(e => e.Job)
                .SelectMany(e => e.ConnectedJobs)
                .ToListAsync();

            // Load IssueAssignedUser
            List<IssueAssignedUser> issueresponse = await _dbContextPool.Users
                .Where(s => keys.Contains(s.ID))
                .Include(e => e.ConnectedIssues).ThenInclude(e => e.Issue)
                .SelectMany(e => e.ConnectedIssues)
                .ToListAsync();

            //Get IAssigned
            return jobsresponse
            .Where(t => t.Job is { })
            .ToLookup(t => t.UserID, t =>
            new GQL_Job() {
                ID = t.Job.ID,
                Name = t.Job.Name,
                Description = t.Job.Description,
                Status = t.Job.Status
            }! as IAssigned).Concat(issueresponse
                    .Where(t => t.Issue is { })
                    .ToLookup(t => t.UserID, t =>
                    new GQL_Issue() {
                        ID = t.Issue.ID,
                        Name = t.Issue.Name,
                        Description = t.Issue.Description,
                        Status = t.Issue.Status
                    }! as IAssigned)).SelectMany(lookup => lookup.Select(value => new { lookup.Key, value }))
                            .ToLookup(x => x.Key, x => x.value);

But i wanna do something like this:

            var q1 = _dbContextPool.Users
                .Where(s => keys.Contains(s.ID))
                .Include(e => e.ConnectedJobs).ThenInclude(e => e.Job)
                .SelectMany(e => e.ConnectedJobs.Select(e => new GQL_UserAssigned() {
                    UserID = e.UserID,
                    Assigned = new GQL_Job() {
                        ID = e.Job.ID,
                        Name = e.Job.Name,
                        Description = e.Job.Description,
                        Status = e.Job.Status
                    }   //  as IAssigned
                }));

            var q2 = _dbContextPool.Users
                .Where(s => keys.Contains(s.ID))
                .Include(e => e.ConnectedIssues).ThenInclude(e => e.Issue)
                .SelectMany(e => e.ConnectedIssues.Select(e => new GQL_UserAssigned() {
                    UserID = e.UserID,
                    Assigned = new GQL_Issue() {
                        ID = e.Issue.ID,
                        Name = e.Issue.Name,
                        Description = e.Issue.Description,
                        Status = e.Issue.Status
                    }   // as IAssigned
                }));

            var unions = q1.Union(q2);

            List<GQL_UserAssigned> result = await unions.ToListAsync(cancellationToken);


    //---------------------------------------------------

    public class GQL_UserAssigned {

        public long UserID { get; set; }

        public IAssigned Assigned { get; set; }

    }

    public class GQL_Job : IAssigned {
         .....
    }

    public class GQL_Issue : IAssigned {
          .....
    }

[MaximBalaganskiy]

The workaround cannot be applied if a column is of variant type (object in c# terms) and one of the queries returns null.
Casting (object)null is client evaluated and throws.

[nmoreaud]

I would like to

var queryableA  = Context.A.select( x => new UnionClass{ Name =x.NameA,Description = x.DescriptionA);
var queryableB = Context.Bselect( x => new UnionClass{ Name =x.NameB,Description = x.DescriptionB);

var queryUnion = queryableA.Union(queryableB)
var result = queryUnion .Select( x=> x.Name.contains("RE));

I have the same issue with inheritance. I guess it would be a use case for TPC but it is not supported yet and I use efcore 3 (next version doesn't have Oracle driver yet). I have an existing db with multiple tables that share 90% of their fields.
I have something like this:

class BaseClass
{
    public string Name;
    public string Description;
}

[TableA]
class A : BaseClass {
    public string FieldA;
}

[TableB]
class B : BaseClass {
    public string FieldB;
}


var queryableA  = Context.A.select( x => (BaseClass) x);
var queryableB = Context.B.select( x => (BaseClass) x);

var queryUnion = queryableA.Union(queryableB)
var result = Context.C.Where(c => queryUnion.Select(u => u.Name).Contains(c.Name));

The same request works fine if I retrieve the Name field instead of BaseClass. However, if I need 2 fields I am doomed!
I spend lot of time to make complex SQL queries compatible with efcore where I would have written by hand the same SQL request 10x faster ; this is the downside of ORM and multi-database compatibility.

[xrkolovos]

Hello.

Any thoughs on this issue? Is by any chance going to be in .net 6 wave?

[ajcvickers]

@xrkolovos This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 6.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[Pentadome]

For me, DateTimeOffset.ToString() caused .Concats to fail. However DateTimeOffset.Day.Tostring() + "-" + DateTimeOffset.Month.Tostring() + "-" + DateTimeOffset.Year.Tostring() did work.

Kinda silly, but I hope it helped someone

[roji]

For users arriving here, most of problematic user queries we've seen involve client evaluation (i.e. constructs which can't be translated to SQL) which can simply be moved after the set operation; that is the optimal solution (see above for examples).

The remaining cases where this cannot be done involve e.g. client evaluation only on one side of the set operation (e.g. the OP). Translating this would be possible only by synthesizing a discriminator allowing us to know which row comes from which side, and applying the client evaluation based on that. While possible, this is quite complex and we haven't seen any actual request for it.

For the record, for Concat (UNION ALL) it's also possible to evaluate the operator on the client-side, since it doesn't involve bring any excess data to the client. This isn't true for the other set operations.