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

[Varorbc]

Include your code

using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace HelloEFCore
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            var testContext = new TestContext();

            var test1 = testContext.Test1.Select(a => new Test
            {
                Id = a.Id,
                Name = "Test1"
            });

            var test2 = testContext.Test2.Select(a => new Test
            {
                Id = a.Id,
                Name = a.Name
            });

            var test = test1.Union(test2).ToList();
            foreach (var item in test)
            {
                Console.WriteLine($"{item.Id}\t{item.Name}");
            }
        }
    }

    public partial class TestContext : DbContext
    {
        public TestContext()
        {
        }

        public TestContext(DbContextOptions<TestContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Test1> Test1 { get; set; }
        
        public virtual DbSet<Test2> Test2 { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("");
            }
        }
    }

    public class Test
    {
        public Guid Id { get; set; }

        public string Name { get; set; }
    }

    public class Test1
    {
        [Key]
        public Guid Id { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }
    }

    public class Test2
    {
        [Key]
        public Guid Id { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }
    }
}

Include stack traces

Unhandled exception. System.InvalidOperationException: Unable to translate set operation when matching columns on both sides have different store types.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplySetOperation(SetOperationType setOperationType, SelectExpression select2, Boolean distinct)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyUnion(SelectExpression source2, Boolean distinct)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateUnion(ShapedQueryExpression source1, ShapedQueryExpression source2)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at HelloEFCore.Program.Main(String[] args) in E:\Projects\HelloEFCore\HelloEFCore\Program.cs:line 27
   at HelloEFCore.Program.<Main>(String[] args)

Include provider and version information

EF Core version:5.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system:win 10
IDE: Visual Studio 2019 16.9

[roji]

Duplicate of #19129

[Varorbc]

        var test1 = testContext.Test1.Select(a => new Test
        {
            Id = a.Id,
            Name = "Test1"
        });

@roji If I remove the assignment of the Name variable, the following exception will appear. How do I set the default value?

System.InvalidOperationException:“Unable to translate set operation when both sides don't assign values to same properties in the nominal type. Please make sure that the properties are inclued on both sides, consider assigning default value if the property doesn't require a specific value.”

[roji]

You can't perform a set operation over different properties - SQL requires the same set of properties on both sides.

Unfortunately, set operations are somewhat limited at the moment... @smitpatel note that in the above a constant is being used (and not a column with a different facet).

[smitpatel]

Since constant has nowhere to infer type mapping it uses nvarchar(max) compared to the column which is nvarchar(50). Exception is logically right.

[roji]

It may be correct in this case to infer the constant mapping from the column on the other side of the set operation, like how we do with binary operators. But this can be done as part of #19129 when the time comes...

[smitpatel]

like how we do with binary operators.

Those are orthogonal concepts and it is responsibility of set operations to align the types across column, which is what #19129 tracks specifically.