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

Answer 1

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.