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

For EFCore:100% successful
check your db table design. every column you use to union must have same type.
eg: table a has column A nvarchar(10)
table b has column A nvarchar(100) will have error.
it must be 
table A has column a nvarchar(10)
table B has column a nvarchar(10)

Answer 2

It looks like double casting (opposed to using Convert.ToString) also works:

(
    from item in _context.Table1
    select new SomeDto
    {
        UserName = (string)(object)item.UserName
    }
)
.Union
(
    from item in _context.Table2
    select new SomeDto
    {
        UserName = (string)(object)item.UserName
    }
)

Answer 3

This error comes if you use concat or union case.
I got this error Microsoft.EntityFrameworkCore 7.0.8. The same code was working EntityFramework without any error. After too many attempts I changed my string property again to string Convert.ToString(r.ReceteId) because this property was "char" type in my database.
For my code :
RecordNo = r.ReceteId
RecordNo = Convert.ToString(r.ReceteId), (This Column already string property but in database it was char)
Then problem solved.

                        var recetequery = (from r in _myContext.DbRecetes
                           where bracns.Contains(r.SubeId) 

                           join rt in _myContext.Receteteslimats on r.ReceteId equals rt.ReceteId
                           where rt.IslemZamani >= startDate && rt.IslemZamani < endDate && rt.Aktif == true


                           join ib in _myContext.DbIlacbilgileris on r.ReceteId equals ib.ReceteId
                           where ib.IlacBilgileriBarkod == barkod
                           join h in _myContext.DbHasta on r.ReceteTckn equals h.HastaTckn

                           join k in _myContext.DbKullanicibilgis on r.ReceteKaydedenId equals k.KullaniciId
                        
                           join s in _myContext.Subelers on r.SubeId equals s.SubeId


                           select new DrugActionDto
                           {
                               ActionType = "Sgk",
                               Date = rt.IslemZamani, 
                               BrancId = r.SubeId,
                               BranchName = s.SubeAdi,
                               RecordNo = Convert.ToString(r.ReceteId), //r.ReceteId (This Column already string property but in database it was char)
                               TcId = r.ReceteTckn,
                               Patient = h.HastaAd + " " + h.HastaSoyad,
                               Barkod = ib.IlacBilgileriBarkod,
                               Count = ib.IlacBilgileriAdet * -1,
                               User = k.KullaniciAd + " " + k.KullaniciSoyad,
                               Delivery = r.TeslimatDurum == 1 ? "Kargo" : r.TeslimatDurum == 0 ? "Elden" : ""
                           });
        

        var nakitQuery = (from n in _myContext.Nakits
                          where bracns.Contains(n.SubeId) && n.Aktif == true && n.TeslimatDurum >= 0
                          && n.IslemTarihi >= startDate && n.IslemTarihi < endDate
                          && !aktarimTcs.Contains(n.Tc) 

                          join ni in _myContext.Nakitilacs on n.NakitSatisId equals ni.NakitSatisId
                          where ni.Barkod == barkod
                          join h in _myContext.DbHasta on n.Tc equals h.HastaTckn

                          join k in _myContext.DbKullanicibilgis on n.Kaydeden equals k.KullaniciId

                          join s in _myContext.Subelers on n.SubeId equals s.SubeId

                          select new DrugActionDto
                          {
                              ActionType = "Nakit",
                              Date = n.IslemTarihi,
                              BrancId = n.SubeId,
                              BranchName = s.SubeAdi,
                              RecordNo = Convert.ToString(n.NakitSatisId), //n.NakitSatisId.ToString()
                              TcId = n.Tc,
                              Patient = h.HastaAd + " " + h.HastaSoyad,
                              Barkod = ni.Barkod,
                              Count = ni.Adet * -1,
                              User = k.KullaniciAd + " " + k.KullaniciSoyad,
                              Delivery = n.TeslimatDurum == 1 ? "Kargo" : n.TeslimatDurum == 0 ? "Elden" : ""
                          });

        var mecs = recetequery.Concat(nakitQuery);
        var tr = await mecs.ToListAsync();

Answer 4

After trying to fix the error, I've understood that EF won't convert types unless they are identical. You might see both fields are strings and think EF should generate the SQL query but the lengths and SQL field types must be identical.

So, my solution for different length strings or empty string etc. was converting both of the values to exact data type.

Just like in the image, even tho fleet_number and description both are strings it does not work since their data type on sql are not identical. So, I converted both to string and it generates the query I need.

working code example

Answer 5

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.

Answer 6

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.

Answer 7

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.