使用计算列更新实体时出错
本文关键字:实体 出错 更新 计算 | 更新日期: 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。
- 对于单个更新(Add()、Addrange()、通过DbContext更新),EF不会保存,而只检索该列的值
- 对于批量更新,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模型属性。
我确信触发器也可以工作,尽管它们不是我的第一选择