linq 由于两个操作数具有不同的“Include”操作，因此与EF Core的联合返回无法转换设置操作

kx5bkwkv  于 2023-03-21  发布在  其他
关注(0)|答案(4)|浏览(88)

在一个使用EF Core 5的.NET Core 5 WebAPI项目中，我尝试在LINQ查询上进行联合，但总是收到错误消息“unable to translate”。我尝试连接的两个实体是相同的，并且字段定义的顺序也相同，所以我不明白问题出在哪里，为什么它不能转换为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"
                    });

而MonthlyAggregatedPrice实体是：

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

它必须是一个IQueryable，因为稍后我将对数据应用更多的过滤器

***UPDATE***即使我尝试在第一个查询中显式创建对象，也会收到以下错误：

“当两端的匹配列具有不同的存储类型时，无法转换设置操作。”

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

4条答案

按热度按时间
rqcrx0a6

rqcrx0a61#

当我在使用实体框架和Oracle时遇到同样的问题时，我找到了一个简单的解决方案。

(
    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)
    }
)

即使我的实体已经是一个字符串，我也必须在两端转换它，以便它们查看相同的数据类型。

赞(0）分享  回复(0）举报  2023-03-21
kyks70gy

kyks70gy2#

我知道这是一个较老的职位，但我遇到了类似的问题，并决定张贴一个变通方案。

(
    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
    }
)

类型问题与UserName列有关，这些列是varchar。解决方案是创建一个函数，将这些函数强制转换为指定的varchar。因此，我们创建如下的强制转换函数：

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

然后在OnModelCreating中注册：

SqlFunctions.Register(modelBuilder);

并将查询重写为：

(
    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)
    }
)

这将最终将UserName列转换为如下形式：

CAST(x.UserName as varchar(32))

指定null作为参数将使用varchar（max），如果你需要nvarchar或其他类型，你可以很容易地修改提供的函数来适应它。

赞(0）分享  回复(0）举报  2023-03-21
goucqfw6

goucqfw63#

经过几次尝试，我发现实体框架在UNION操作符上有错误，如果添加几个字段，它会变得混乱。
例如，以下查询基于整数和字符串字段集（全部正确填写在数据库中），但不起作用，它返回“* 当两端的匹配列具有不同的存储类型时，无法转换集合操作。*"：

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();

但是如果你尝试减少字段的数量，它开始工作得很好。我已经看到，基于一个大约10k行的数据结果，在5个字段之后，EF开始在运行UNION查询时引发错误。如果你用一个.toList（）单独执行查询，然后应用UNION，它工作得很好。
此外，如果您尝试执行EF生成的SQL，这是正确的，您不会在SQL Server或PostgreSQL中得到任何错误。

安全使用UNION的唯一方法是在数据库中创建视图。

赞(0）分享  回复(0）举报  2023-03-21
toe95027

toe950274#

在尝试修复错误后，我了解到EF不会转换类型，除非它们相同。您可能会看到两个字段都是字符串，并认为EF应该生成SQL查询，但长度和SQL字段类型必须相同。
因此，我的解决方案是将不同长度的字符串或空字符串等转换为精确的数据类型。
就像图中一样，尽管fleet_number和description都是字符串，但由于它们在sql中的数据类型不同，所以无法正常工作，因此，我将它们都转换为字符串，它将生成我需要的查询。
working code example

赞(0）分享  回复(0）举报  2023-03-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com