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

Question

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"
                    });

Try splitting the two IQueryable and execute them individually. Inspect the generated SQL, run the SQLs directly on the server, and check their resultsets to see if they are actually union-able. — NPras
@NPras Done it: I've pasted the 2 queries in SQL and tried to UNION them and it works fine in SQL Server. So it seems that EF Core gets confused on Union() — Giox
Actually you need Concat instead of Union. But, I think, error will be the same. Union is Distinct operation and ordering will be lost. — Svyatoslav Danyliv
Unions have always been a pain point in EF - core or otherwise. If the data is relatively small, doing in-memory concat instead of on the server might be an option. — NPras
@SvyatoslavDanyliv Yeah, firstly I've tried with concat, receiving the same error — Giox

Giox Giox · Accepted Answer · 2021-03-29T11:10:25

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.

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

1 Answers