使用计算列更新实体时出错

本文关键字:实体 出错 更新 计算 | 更新日期: 2023-09-27 18:28:05

我想更新一个具有计算列的实体。不幸的是,我得到了SqlException:

SqlException:无法在OUTPUT子句中引用列"inserted.OpenCount",因为该列定义包含子查询或引用执行用户或系统数据访问的函数。默认情况下,如果函数没有架构化,则假定它执行数据访问。请考虑从列定义中删除子查询或函数,或从OUTPUT子句中删除列。

当我尝试这样做时:

[HttpGet("{id:int}/[action]")]
public async Task<IActionResult> SetPublished(int id, string returnUrl)
{
    // load article
    var article = await Data.Articles.SingleOrDefaultAsync(r => r.Id == id);
    if (article == null)
        return HttpNotFound();
    // toggle published 
    if (!article.IsPublished)
        article.IsPublished = true;
    else
        article.IsPublished = false;
    await Data.SaveChangesAsync();
    // redirect
    if (returnUrl != null)
        return LocalRedirect(returnUrl);
    return RedirectToAction(nameof(Index));
}

我确实尝试排除具有[NotMapped]属性的此列,但这没有帮助。

有没有办法在不改变数据库结构的情况下解决这个问题?

以下是该列的声明方式:

[Table("Articles")]
public class Article : IdEntity
{
    /// <summary>
    /// Gets or sets publish date and time.
    /// </summary>
    [DataType(DataType.DateTime)]
    [Display(Name = "Дата публикации")]
    public virtual DateTime Date { get; set; }
    /// <summary>
    /// Gets or sets article name.
    /// </summary>
    [Display(Name = "Название")]
    [Required(ErrorMessage = "Введите название")]
    [MaxLength(50, ErrorMessage = "Название должно быть не более 50 символов")]
    public virtual string Name { get; set; }
    /// <summary>
    /// Gets or sets URL name.
    /// </summary>
    [Display(Name = "Название в URL")]
    [MaxLength(50, ErrorMessage = "Название в URL должно быть не длиннее 50 символов")]
    [RegularExpression(RegexConstants.UrlName, ErrorMessage = "Название в URL может содержать только символы латинского алфавита, цифры, символы тире и подчеркивания")]
    public virtual string UrlName { get; set; }
    /// <summary>
    /// Gets or sets article full text.
    /// </summary>
    [Display(Name = "Текст")]
    [Required(ErrorMessage = "Введите текст")]
    public virtual string Text { get; set; }
    /// <summary>
    /// Gets or sets if article is published. If not when one should not be available on public website.
    /// </summary>
    [Display(Name = "Опубликована")]
    public virtual bool IsPublished { get; set; }
    /// <summary>
    /// Gets nubmer of article opens.
    /// </summary>
    [NotMapped]
    [Display(Name = "Количество открытий")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public virtual int OpenCount { get; protected set; }

使用计算列更新实体时出错

我已经为这个问题挣扎了好几天了。我的研究表明,在这一点上,有两种方法可以将一个领域识别为";计算的";EF。

  1. 对于单个更新(Add()、Addrange()、通过DbContext更新),EF不会保存,而只检索该列的值
  2. 对于批量更新,EF.BulkExtensions将在列上混合使用Fluent配置和数据注释。设置PropertiesToExclude或PropertiesToInclude仍将尝试从数据大容量加载到TEMP表后触发的MERGE查询中输出值,从而导致相同的异常

https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-注释https://github.com/borisdj/EFCore.BulkExtensions/blob/master/EFCore.BulkExtensions/TableInfo.cs

我最终添加了一个SPROC来处理计算列的计算,并简单地将其用作任何其他绑定到DB模型属性。

我确信触发器也可以工作,尽管它们不是我的第一选择