Union can't match two string columns

Question

I have the following query.

var test = await DbContext.StorageAdjustments
    .Where(a => a.StorageId == id && a.TimeStamp >= start)
    .Select(a => new StorageUpdateModel
    {
        TimeStamp = a.TimeStamp,
        Change = a.Volume,
        Product = a.Product.Name,
        UpdateType = StorageUpdateType.Adjustment,
        Notes = "BOL",
    })
    .Union(DbContext.StorageTrucks
        .Where(t => t.StorageId == id && t.TimeStamp >= start)
        .Select(t => new StorageUpdateModel
        {
            TimeStamp = t.TimeStamp,
            Change = t.Volume,
            Product = "xxx",
            UpdateType = StorageUpdateType.Truck,
            Notes = "BOL",
        }))
    .ToListAsync();

But it throws an 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.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.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.d__65`1.MoveNext()
   at Pegasus.Pages.Transloading.StorageLocations.DetailsModel.d__15.MoveNext() in D:\Users\jwood\source\repos\Railtrax\Pegasus\Pages\Transloading\StorageLocations\Details.cshtml.cs:line 65

If I change Product = a.Product.Name, to Product = "xxx", it works without an exception.

I really don't understand this. a.Product.Name is defined in the database as (nvarchar(120), not null). Does anyone know why Union() can't combine two string columns, or how to coherce them to the same type?

Jonathan Wood · Answer

It looks like a change to address the underlying issue was being considered for version 3.0 on GitHub.

I don't understand why it wasn't considered important enough to implement even in version 7.0.

It makes it extremely difficult to workaround such an issue with the only valid workaround being to perform the Union() on the client side, along with its obvious performance disadvantages.

Update

There was one workaround by using Product = Convert.ToString(a.Product.Name) and it actually works for me. However, it seems to be using NVARCHAR(MAX) and people posted comments that it can cause resource problems.

Union can't match two string columns

1 Answers

Update