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