Union with EF Core returns unable to translate set operation since both operands have different 'Include' operations

user_2072676 0
Viewed 5279

In a .NET Core 5 WebAPI project with EF Core 5, I'm trying to do a union on a LINQ query but I always get an error "unable to translate". The two entities I'm trying to concatenate are the same and also in the same order for the definition of the fields, so I can't understand what's the issue and why it can't translate into a SQL UNION:

IQueryable <MonthlyAggregatedPrice> monthlyAggregatedPrices = 
(from map in db.MonthlyAggregatedPrices
  where map.Adm0Code == adm0Code
  orderby map.CommodityPriceDate descending
  select map).Union(
                    from f in db.ST_PewiPriceForecasts
                    join cm in db.Commodities on f.CommodityID equals cm.CommodityID
                    join m in db.Markets on f.MarketID equals m.MarketId
                    join u in db.CommodityUnits on f.CommodityUnitID equals u.CommodityUnitID
                    join pt in db.PriceTypes on f.PriceTypeID equals pt.PriceTypeID
                    join cu in db.Currencies on f.CurrencyID equals cu.CurrencyID
                    where f.Adm0Code == adm0Code
                    select new MonthlyAggregatedPrice
                    {
                        CommodityId = f.CommodityID,
                        MarketId = f.MarketID,
                        PriceTypeId = f.PriceTypeID,
                        CommodityUnitId = f.CommodityUnitID,
                        CurrencyId = f.CurrencyID,
                        CommodityName = cm.CommodityName,
                        MarketName = m.MarketName,
                        PriceTypeName = pt.PriceTypeName,
                        CommodityUnitName = u.CommodityUnitName,
                        CurrencyName = cu.CurrencyName,
                        Adm0Code = adm0Code,
                        CountryISO3 = countryInfo.Iso3Alpha3,
                        CountryName = countryInfo.Name,
                        CommodityPrice = 0,
                        OriginalFrequency = "monthly",
                        CommodityPriceSourceName = "",
                        CommodityPriceObservations = null,
                        CommodityDateMonth = f.PriceForecastMonth,
                        CommodityDateYear = f.PriceForecastYear,
                        CommodityPriceDate= f.PriceDate,
                        CommodityPriceFlag = "forecast"
                    });

And the MonthlyAggregatedPrice entity is:

public partial class MonthlyAggregatedPrice
{
    public int CommodityId { get; set; }
    public int MarketId { get; set; }
    public int PriceTypeId { get; set; }
    public int CommodityUnitId { get; set; }
    public int CurrencyId { get; set; }
    public string CommodityName { get; set; }
    public string MarketName { get; set; }
    public string PriceTypeName { get; set; }
    public string CommodityUnitName { get; set; }
    public string CurrencyName { get; set; }
    public int Adm0Code { get; set; }
    public string CountryISO3 { get; set; }
    public string CountryName { get; set; }
    public decimal CommodityPrice { get; set; }
    public string OriginalFrequency { get; set; }
    public string CommodityPriceSourceName { get; set; }
    public int? CommodityPriceObservations { get; set; }
    public int CommodityDateMonth { get; set; }
    public int CommodityDateYear { get; set; }
    public DateTime CommodityPriceDate { get; set; }
    public string CommodityPriceFlag { get; set; }
}

It must be a IQueryable because later I should apply more filters on the data

*** UPDATE *** Even if I try to explicitly create the object in the first query I get the following error:

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

IQueryable < MonthlyAggregatedPrice > monthlyAggregatedPrices = 
(from map in db.MonthlyAggregatedPrices
where map.Adm0Code == adm0Code
orderby map.CommodityPriceDate descending
select new MonthlyAggregatedPrice
{
    CommodityId = map.CommodityId,
    MarketId = map.MarketId,
    PriceTypeId = map.PriceTypeId,
    CommodityUnitId = map.CommodityUnitId,
    CurrencyId = map.CurrencyId,
    CommodityName = map.CommodityName,
    MarketName = map.MarketName,
    PriceTypeName = map.PriceTypeName,
    CommodityUnitName = map.CommodityUnitName,
    CurrencyName = map.CurrencyName,
    Adm0Code = adm0Code,
    CountryISO3 = countryInfo.Iso3Alpha3,
    CountryName = countryInfo.Name,
    CommodityPrice = map.CommodityPrice,
    OriginalFrequency = map.OriginalFrequency,
    CommodityPriceSourceName = map.CommodityPriceSourceName,
    CommodityPriceObservations = map.CommodityPriceObservations,
    CommodityDateMonth = map.CommodityDateMonth,
    CommodityDateYear = map.CommodityDateYear,
    CommodityPriceDate = map.CommodityPriceDate,
    CommodityPriceFlag = map.CommodityPriceFlag
}).Union(
                    from f in db.ST_PewiPriceForecasts
                    join cm in db.Commodities on f.CommodityID equals cm.CommodityID
                    join m in db.Markets on f.MarketID equals m.MarketId
                    join u in db.CommodityUnits on f.CommodityUnitID equals u.CommodityUnitID
                    join pt in db.PriceTypes on f.PriceTypeID equals pt.PriceTypeID
                    join cu in db.Currencies on f.CurrencyID equals cu.CurrencyID
                    where f.Adm0Code == adm0Code
                    select new MonthlyAggregatedPrice
                    {
                        CommodityId = f.CommodityID,
                        MarketId = f.MarketID,
                        PriceTypeId = f.PriceTypeID,
                        CommodityUnitId = f.CommodityUnitID,
                        CurrencyId = f.CurrencyID,
                        CommodityName = cm.CommodityName,
                        MarketName = m.MarketName,
                        PriceTypeName = pt.PriceTypeName,
                        CommodityUnitName = u.CommodityUnitName,
                        CurrencyName = cu.CurrencyName,
                        Adm0Code = adm0Code,
                        CountryISO3 = countryInfo.Iso3Alpha3,
                        CountryName = countryInfo.Name,
                        CommodityPrice = 0,
                        OriginalFrequency = "monthly",
                        CommodityPriceSourceName = "",
                        CommodityPriceObservations = null,
                        CommodityDateMonth = f.PriceForecastMonth,
                        CommodityDateYear = f.PriceForecastYear,
                        CommodityPriceDate=dt,
                        CommodityPriceFlag = "forecast"
                    });
entity-framework-core .net-core linq c#
3 Answers

I found a simple solution when I encountered this same issue using Entity Framework and Oracle. I copied part of @Phil A.'s answer for ease.

(
    from item in _context.Table1
    select new SomeDto
    {
        // Some other fields trimmed for readability
        UserName = Convert.ToString(item.UserName)
    }
)
.Union
(
    from item in _context.Table2
    select new SomeDto
    {
        // Some other fields trimmed for readability
        UserName = Convert.ToString(item.UserName)
    }
)

Even if my entity was already a string, I had to convert it on both sides so they're looking at the same data type.

user_46693110

After several tries, I've found that Entity Framework is buggy on the UNION operator and it gets confused if you add several fields.

For example, the following query, based on a fieldset of integers and strings, all correctly filled in in the database, doesn't work and it returns "Unable to translate set operation when matching columns on both sides have different store types.":

var tmp = ((from map in db.MonthlyAggregatedPrices
           where map.Adm0Code == adm0Code
           select new UnionTestDto
           {
               CommodityId = map.CommodityId,
               MarketId = map.MarketId,
               PriceTypeId = map.PriceTypeId,
               CommodityUnitId = map.CommodityUnitId,
               CurrencyId = map.CurrencyId,
               CommodityName = map.CommodityName,
               MarketName = map.MarketName,
               PriceTypeName = map.PriceTypeName,
               CommodityUnitName = map.CommodityUnitName,
               CurrencyName = map.CurrencyName
           }).Union(from f in db.ST_PewiPriceForecasts
                    join cm in db.Commodities on f.CommodityID equals cm.CommodityID
                    join m in db.Markets on f.MarketID equals m.MarketId
                    join u in db.CommodityUnits on f.CommodityUnitID equals u.CommodityUnitID
                    join pt in db.PriceTypes on f.PriceTypeID equals pt.PriceTypeID
                    join cu in db.Currencies on f.CurrencyID equals cu.CurrencyID
                    where f.Adm0Code == adm0Code
                    select new UnionTestDto
                    {
                        CommodityId = f.CommodityID,
                        MarketId = f.MarketID,
                        PriceTypeId = f.PriceTypeID,
                        CommodityUnitId = f.CommodityUnitID,
                        CurrencyId = f.CurrencyID,
                        CommodityName = cm.CommodityName,
                        MarketName = m.MarketName,
                        PriceTypeName = pt.PriceTypeName,
                        CommodityUnitName = u.CommodityUnitName,
                        CurrencyName = cu.CurrencyName
                    })).ToList();

But if you try to reduce the number of fields it starts to work fine. I've seen that based on a data result of around 10k rows, after 5 fields EF start to raise errors in running the UNION query. If you execute the queries separately with a .toList() and then you apply UNION, it works fine.

Additionally, if you try to execute the SQL that EF generates, which is correct, you don't get any error in SQL Server or PostgreSQL.

The only way to safely work with UNION is to create a View in the database.

user_20726760

I know this is an older post, but I ran into a similar problem and decided to post a workaround. The query in question was:

(
    from item in _context.Table1
    select new SomeDto
    {
        // Some other fields trimmed for readability
        UserName = item.UserName
    }
)
.Union
(
    from item in _context.Table2
    select new SomeDto
    {
        // Some other fields trimmed for readability
        UserName = item.UserName
    }
)

The type issue was with UserName columns, which are varchars. The solution is to create a function that will cast these function to a specified varchar. Therefore, we create the cast function like this:

public static class SqlFunctions
{
    public static string CastToVarchar(string value, int? varcharLength) => value;

    public static void Register(ModelBuilder modelBuilder)
    {
        MethodInfo method = typeof(SqlFunctions).GetMethod(nameof(CastToVarchar));
        modelBuilder.HasDbFunction(method).HasTranslation(TranslateCastToVarchar);
    }

    private static SqlExpression TranslateCastToVarchar(IReadOnlyList<SqlExpression> args)
    {
        var fieldArgument = args[0] as ColumnExpression;
        var lengthArgument = args[1] as SqlConstantExpression;

        var length = lengthArgument?.Value ?? "max";

        var result = new SqlFunctionExpression
        (
            "CAST",
            new SqlExpression[]
            {
                new SqlFragmentExpression($"{fieldArgument.TableAlias}.{fieldArgument.Name} AS varchar({length})"),
            },
            nullable: false,
            argumentsPropagateNullability: new[] { true, true },
            typeof(string), // typeof(string)?
            new StringTypeMapping($"varchar({length})", DbType.String) // as varchar(length)
        );

        return result;
    }
}

Then register it in OnModelCreating:

SqlFunctions.Register(modelBuilder);

And rewrite the query as such:

(
    from item in _context.Table1
    select new SomeDto
    {
        // Some other fields
        UserName = SqlFunctions.CastToVarchar(item.UserName, 32)
    }
)
.Union
(
    from item in _context.Table2
    select new SomeDto
    {
        // Some other fields
        UserName = SqlFunctions.CastToVarchar(item.UserName, 32)
    }
)

This will end up translating the UserName columns as something like:

CAST(x.UserName as varchar(32))

And union will work. Specifying null as an argument will use varchar(max). If you need nvarchar or ther types instead, you can easily modify the provided function to accommodate that.

user_83978640
Related