Union not supported?

[SoftCircuits]

SQL has a UNION operator. So I'm not sure I understand why Union() doesn't appear to work.

var test = await DbContext.StorageAdjustments
    .Select(a => new StorageUpdateModel
    {
        TimeStamp = a.TimeStamp,
        Change = a.Volume,
        Volume = 0,
        Product = a.Product.Name,
        UpdateType = StorageUpdateType.Adjustment,
        Notes = $"{a.UserName}{(!string.IsNullOrWhiteSpace(a.Comments) ? $" : {a.Comments}" : null)}",
    })
    .Union(DbContext.StorageTrucks
        .Select(t => new StorageUpdateModel
        {
            TimeStamp = t.TimeStamp,
            Change = t.Volume,
            Volume = 0,
            Product = null,
            UpdateType = StorageUpdateType.Truck,
            Notes = $"BOL {t.Truck.FormattedBol}",
        }))
    .ToListAsync();

System.InvalidOperationException: 'Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.'
   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.<ExecuteAsync>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.<ToListAsync>d__65`1.MoveNext()
   at Pegasus.Pages.Transloading.StorageLocations.DetailsModel.<OnGetAsync>d__15.MoveNext() in D:\Users\jwood\source\repos\Railtrax\Pegasus\Pages\Transloading\StorageLocations\Details.cshtml.cs:line 64

EF Core version: 6.06
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.3.2)

[roji]

@SoftCircuits EF Core does translate Union(). However, in the example above you're using string interpolation (`$"..."), which is something that isn't translatable to SQL; since that occurs before the Union() operator, EF isn't able to translate that to SQL (as the exception says).

You should be able to get this to work by avoiding string interpolation above, e.g. replace $"BOL {t.Truck.FormattedBol}" with "BOL " + t.Truck.FormattedBol (which is translatable), and the first interpolation with: Notes = string.IsNullOrWhitespace(a.Comments) ? a.UserName : a.Username + " : " + a.Comments.

Another option is to project out to a temporary anonymous type, and project again after the Union() operator to perform the string interpolation.

[SoftCircuits]

@roji Thanks, I guess I knew that could be a potential issue, but I was expecting a different error there.

However, when I change it, I continue to have issues.

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 = "",
    })
    .Union(DbContext.StorageTrucks
        .Where(t => t.StorageId == id && t.TimeStamp >= start)
        .Select(t => new StorageUpdateModel
        {
            TimeStamp = t.TimeStamp,
            Change = t.Volume,
            Product = ""
            UpdateType = StorageUpdateType.Truck,
            Notes = "BOL ",
        }))
    .ToListAsync();

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.<ExecuteAsync>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.<ToListAsync>d__65`1.MoveNext()
   at Pegasus.Pages.Transloading.StorageLocations.DetailsModel.<OnGetAsync>d__15.MoveNext() in D:\Users\jwood\source\repos\Railtrax\Pegasus\Pages\Transloading\StorageLocations\Details.cshtml.cs:line 65

I can't see where columns have different store types. This isn't related to the strings being different lengths is it?

[roji]

This isn't related to the strings being different lengths is it?

No, definitely shouldn't be. It should be the database type of one of the column there (Timestamp, Volume...). I'd put my money on a.Product.Name, which you're trying to join with an empty string literal - try removing pairs until you isolate which one it is.

[SoftCircuits]

@roji Yes, you are correct sir. Thank you. However, a.Product.Name is (nvarchar(120), not null). Isn't the only difference there the lengths of the strings?

Is this the same as #19129? Any workarounds?

[SoftCircuits]

Looks like Product = Convert.ToString(a.Product.Name) prevents the error. However, it appears there are memory usage concerns. I think it uses NVARCHAR(MAX), but am not sure as it's kind of a black box.

Been a long and frustrating day. The following raw SQL does exactly what I need. It's bad enough that I need to go to raw SQL, but I can't even do this because ExecuteSqlRaw() doesn't return a query. I'd even be happy even to write my own mapper to the object I need (it's not an entity), but I can't do that because I have no way to run a query and access the results. <Sigh!>

SELECT sa.[TimeStamp],
	sa.[Volume] AS [Change],
	p.[Name] AS [Product],
	1 AS [UpdateType],
	sa.[UserName] AS [UpdateData],
	sa.[Comments] AS [UpdateData2]
FROM [StorageAdjustments] sa
JOIN [Products] p ON sa.[ProductId] = p.[Id]
WHERE [StorageId] = @storageId and [TimeStamp] >= @startDate

UNION ALL

SELECT st.[TimeStamp],
	st.[Volume] AS [Change],
	NULL AS [Product],
	2 AS [UpdateType],
	CONVERT(NVARCHAR, t.[BillOfLading]) AS [UpdateData],
	CONVERT(NVARCHAR, t.[ManualEntry]) AS [UpdateData2]
FROM [StorageTrucks] st
JOIN [Trucks] t ON st.[TruckId] = t.[Id]
WHERE [StorageId] = @storageId and [TimeStamp] >= @startDate

UNION ALL

SELECT sr.[TimeStamp],
	sr.[Volume] AS [Change],
	NULL AS [Product],
	3 AS [UpdateType],
	r.[RailcarNumber] AS [UpdateData],
	NULL AS [UpdateData2]
FROM [StorageRailcars] sr
JOIN [Railcars] r ON sr.[RailcarId] = r.[Id]
WHERE [StorageId] = @storageId and [TimeStamp] >= @startDate

[roji]

Looks like Product = Convert.ToString(a.Product.Name) prevents the error. However, it appears there are memory usage concerns.

Can you elaborate on this? Where are you seeing memory concerns?

I think it uses NVARCHAR(MAX), but am not sure as it's kind of a black box.

Logging should make it easy to know exactly which SQL is being generated by EF.

The following raw SQL does exactly what I need. It's bad enough that I need to go to raw SQL, but I can't even do this because ExecuteSqlRaw() doesn't return a query.

ExecuteSqlRaw is for executing non-queries: are you aware of FromSqlRaw?

(though I still think you should be able to use the Convert.ToString() workaround and keep using LINQ rather than raw SQL)

[SoftCircuits]

@roji The discussion about potential problems with Convert.ToString() can be read here. But I'll readily acknowledge I don't quite understand the issue. I wrote my query in raw SQL, and it worked without specifying string sizes. So I'm not sure what I'm looking for in the generated SQL.

I am familiar with FromSqlRaw(). But it only allows me to return entity types. I'm doing a union on a custom type.

[roji]

Duplicate of #19129

[roji]

Thanks for the link. I wouldn't assume that the plan/memory warning regarding CONVERT necessarily affects your case; at the very least I'd analyze the specific SQL here and see if the same warning is issued.

In any case, this is basically a dup of #19129 (and very specifically of #19129 (comment)).