Unable to translate set operation when matching columns on both sides have different store types

[universorum]

I'm trying to write the UNION query with IEnumerable.Concat() and use SQLite provider to test it.

Consider the following entity:

public class Blog
{
    public int          Id      { get; set; }
    public List<Author> Authors { get; init; } = new List<Author>(0);
}

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

I want to get the Blog's id and Author's name like:

SELECT "t"."Name"
FROM "Blog" AS "a"
  INNER JOIN "Author" AS "t" ON "a"."Id" = "t"."BlogId"
UNION ALL
SELECT CAST("a0"."Id" AS TEXT) AS "Name"
FROM "Blog" AS "a0"

Here is the linq:

var source = context.Blogs;
var id     = source.Select(x => x.Id.ToString());
var author = source.SelectMany(x => x.Authors.Select(y => y.Name));
var result = await author.Concat(id).ToArrayAsync();

It's work fine when using SQLite provider (v7) but throw InvalidOperationException when using Pomelo(v7).
InvalidOperationException: Unable to translate set operation when matching columns on both sides have different store types.

I can force convert type from string to string to make linq work

var source = context.Blogs;
var id     = source.Select(x => x.Id.ToString());
var author = source.SelectMany(x => x.Authors.Select(y => Convert.ToString(y.Name)));
var result = await author.Concat(id).ToArrayAsync();

and this will break the SQLite provider
InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.

Further technical details

Operating system: Windows

Pomele result:
.NET SDK: v8
Target framework: v6
MySQL version: 8.0.27
Pomelo.EntityFrameworkCore.MySql version: 7.0.0
With EnableStringComparisonTranslations() called

SQLite result:
.NET SDK: v8
Target framework: v6 AND v8
Microsoft.EntityFrameworkCore.Sqlite: 7.0.14 AND 8.0.0

[universorum]

I also try string.ToString() to make convert

var source = context.Blogs;
var id     = source.Select(x => x.Id.ToString());
var author = source.SelectMany(x => x.Authors.Select(y => y.Name.ToString()));
var result = await author.Concat(id).ToArrayAsync();

And here is result (all result is under .NET v8 SDK/runtime):
SQLite 8.0.0: Pass
SQLite 7.0.14 and Pomelo v7
InvalidOperationException: Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.

[lauxjpn]

Here is the linq:

var source = context.Blogs;
var id     = source.Select(x => x.Id.ToString());
var author = source.SelectMany(x => x.Authors.Select(y => y.Name));
var result = await author.Concat(id).ToArrayAsync();

It's work fine when using SQLite provider (v7) but throw InvalidOperationException when using Pomelo(v7).

I am unable to reproduce this using Pomelo 7.0.0 and 8.0.1. Your original query seems to execute fine in both versions.

I used the following simple console program to test this:

Program.cs

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate;

public class Blog
{
    public int Id { get; set; }
    public List<Author> Authors { get; init; } = new List<Author>(0);
}

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

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1809";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion)
                // .UseSqlite(@"Data Source=Issue1809.db")
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>(
            entity =>
            {
                entity.HasData(
                    new Blog { Id = 1 },
                    new Blog { Id = 2 });
            });

        modelBuilder.Entity<Author>(
            entity =>
            {
                entity.HasData(
                    new Author
                    {
                        Id = 101,
                        Name = "John Doe",
                        BlogId = 1,
                    },
                    new Author
                    {
                        Id = 102,
                        Name = "Jane Doe",
                        BlogId = 2,
                    });
            });
    }
}

internal static class Program
{
    private static void Main()
    {
        using var context = new Context();

        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        // var result = context.Blogs.SelectMany(b => b.Authors.Select(y => y.Name))
        //     .Concat(context.Blogs.Select(b => b.Id.ToString()))
        //     .ToArray();
        
        var source = context.Blogs;
        var id     = source.Select(x => x.Id.ToString());
        var author = source.SelectMany(x => x.Authors.Select(y => y.Name));
        var result = author.Concat(id).ToArray();

        // Make order deterministic for asserts.
        result = result
            .OrderBy(s => s)
            .ToArray();

        Trace.Assert(result.Length == 4);
        Trace.Assert(result[0] == "1");
        Trace.Assert(result[1] == "2");
        Trace.Assert(result[2] == "Jane Doe");
        Trace.Assert(result[3] == "John Doe");
    }
}

Output (SQL)

warn: 06.03.2024 13:57:41.340 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 06.03.2024 13:57:41.688 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                
      DROP DATABASE `Issue1809`;                                                                                        
info: 06.03.2024 13:57:42.310 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                 
      CREATE DATABASE `Issue1809`;                                                                                      
info: 06.03.2024 13:57:42.499 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']                                 
      ALTER DATABASE CHARACTER SET utf8mb4;                                                                             
info: 06.03.2024 13:57:42.529 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (30ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Blog` (
          `Id` int NOT NULL AUTO_INCREMENT,
          CONSTRAINT `PK_Blog` PRIMARY KEY (`Id`)
      ) CHARACTER SET=utf8mb4;
info: 06.03.2024 13:57:42.566 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (36ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE `Author` (
          `Id` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `BlogId` int NOT NULL,
          CONSTRAINT `PK_Author` PRIMARY KEY (`Id`),
          CONSTRAINT `FK_Author_Blog_BlogId` FOREIGN KEY (`BlogId`) REFERENCES `Blog` (`Id`) ON DELETE CASCADE
      ) CHARACTER SET=utf8mb4;
info: 06.03.2024 13:57:42.576 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `Blog` (`Id`)
      VALUES (1),
      (2);
info: 06.03.2024 13:57:42.582 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO `Author` (`Id`, `BlogId`, `Name`)
      VALUES (101, 1, 'John Doe'),
      (102, 2, 'Jane Doe');
info: 06.03.2024 13:57:42.616 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (33ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX `IX_Author_BlogId` ON `Author` (`BlogId`);
info: 06.03.2024 13:57:44.372 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `a`.`Name`
      FROM `Blog` AS `b`
      INNER JOIN `Author` AS `a` ON `b`.`Id` = `a`.`BlogId`
      UNION ALL
      SELECT CAST(`b0`.`Id` AS char) AS `Name`
      FROM `Blog` AS `b0`

@universorum If you still believe that this is an issue in Pomelo, please change my sample code in a way that it reproduces the issue.

[universorum]

The exception will throw when type is VARCHAR(N), translation is good with longtext.

 modelBuilder.Entity<Author>(
            entity =>
            {
                entity.Property(e => e.Name).HasMaxLength(2000);
            });

Also, it seems to be fixed in 8.0.0/8.0.1

[lauxjpn]

@universorum Thanks, we are able to reproduce the issue now.

EF Core 7.0 checks explicitly that the store type of all the columns of the two queries of the set operation (e.g. UNION) are of the exact same type.

In the sample code, they the types are longtext and varchar(2000), so the check fails.

This was fixed for EF Core 8.0 with dotnet/efcore#30468.

Because this is an EF Core bug, was fixed for 8.0 and .NET/EF Core 7.0 is near its end-of-life, we will close this one.

The recommended solution is to upgrade to Pomelo 8.0.x, which is based on EF Core 8.0.x and therefore contains the fix.