EF 6未检索触发器更新的字段 [英] EF 6 not retrieving trigger updated fields

问题描述

我有一个简单的表，其中包含两个通过触发器更新的字段。

I've got a simple table with two fields that are updated via triggers.

CREATE TABLE [dbo].[CarType](
    [idCarType] [int] IDENTITY(1,1) NOT NULL,
    [CarTypeName] [nvarchar](50) NOT NULL,
    [CR_Date] [datetime2](7) NOT NULL CONSTRAINT [DF_CarType_CR_Date]  DEFAULT (getdate()),
    [LU_Date] [datetime2](7) NOT NULL CONSTRAINT [DF_CarType_LU_Date]  DEFAULT (getdate()),
 CONSTRAINT [PK_CarType] PRIMARY KEY CLUSTERED (    [idCarType] ASC)
)
END
GO
EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER [dbo].[CarType_Insert] ON [dbo].[CarType]
  AFTER INSERT AS 
BEGIN
    UPDATE dbo.CarType
      SET dbo.CarType.CR_Date=getdate(), dbo.CarType.LU_Date=getdate()
         FROM dbo.CarType
         INNER JOIN Inserted ON dbo.CarType.idCarType = Inserted.idCarType
END' 
GO

EXEC dbo.sp_executesql @statement = N'
CREATE TRIGGER [dbo].[CarType_Update] ON [dbo].[CarType]
  AFTER UPDATE AS 
BEGIN
    UPDATE dbo.CarType
      SET  dbo.CarType.LU_Date=getdate()
         FROM dbo.CarType
         INNER JOIN Inserted ON dbo.CarType.idCarType = Inserted.idCarType
END' 
GO

当我使用以下方法添加新内容时：

When I do an add new with:

CarEntities db = new CarEntities();
CarType car = new CarType()
{
  CarTypeName = "Ford"
};
db.CarTypes.Add(carType);
db.SaveChanges();

carType.Dump(); //extension to print out type as JSON

CarType newCar = db.CarTypes.AsNoTracking().Where(e => e.idCarType == carType.idCarType).First();
newCar.Dump(); //extension to print out type as JSON

carType的扩展名返回为：

carType comes back as:

{
    "idCarType": 8,
    "CarTypeName": "Ford",
    "CR_Date": "0001-01-01T00:00:00",
    "LU_Date": "0001-01-01T00:00:00"
}

，但newCar返回为：

but newCar comes back as:

{
    "idCarType": 8,
    "CarTypeName": "Ford",
    "CR_Date": "2014-12-18T07:53:27.773",
    "LU_Date": "2014-12-18T07:53:27.773"
}

但仅当我添加> AsNoTracking 部分。

有什么办法可以用ADD获取更新的字段，而不是强制执行数据库查询？

Any idea how to get the updated fields with the ADD as opposed to forcing a database query?

推荐答案

当数据库哑且将所有逻辑留给EF代码时，实体框架最有效。当触发器和存储的作业等数据库功能对数据进行更改时，除非并显式重新查询数据，否则Entity Framework不会知道更改。因此，您必须编写代码来强制EF在保存数据后刷新数据以获取这些更改。

Entity Framework works best when the database is "dumb" and leaves all the logic to the EF code. When database features such as triggers and stored jobs make changes to data, Entity Framework doesn't know about the changes unless and until it explicitly requeries the data. So you'd have to write code to force EF to refresh the data after you save it to get these changes.

首先，尝试将CR_Date和LU_Date列配置为视为数据库生成的。

First, try configuring the CR_Date and LU_Date columns to be treated as database-generated.

Property(p => p.CR_Date).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

但是我不确定这是否会触发触发器所做的数据更改。如果不是这样，则需要强制重新加载。

But I'm not certain if that will pick up data changes made by triggers. If it does not then you'll need to force a reload.

为此，请在DbContext类中重写SaveChanges方法。抓住已添加和已修改条目的列表，继续进行保存，然后从数据库中强制更新这些条目：

To do that, override the SaveChanges method in your DbContext class. Grab a list of the added and modified entries, let the save proceed and then force an update from the database for those entries:

public override int SaveChanges()
{

    var entriesToReload = ChangeTracker.Entries<ITrackedEntity>().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified).ToList();

    int rowCount;
    rowCount = base.SaveChanges();

    if (rowCount > 0)
    {
        entriesToReload.ForEach(e=>e.Reload());
    }

    return rowCount;

}

或者，如果您有使用数据库的能力，

Alternatively, if you have the luxury of working with a database that is only used by your EF application, you can write EF code that will update your CR_Date and LU_Date in the application instead of in database triggers.

首先，创建一个接口，该EF代码仅由EF应用程序使用，您可以编写EF代码，以在应用程序中而不是在数据库触发器中更新CR_Date和LU_Date。定义您的更新跟踪字段，并确保所有包含这些日期的实体都实现该接口。

First, create an interface that defines your update tracking fields and make sure all of your entities that include these dates implement that interface.

public interface ITrackedEntity
{
    DateTime CR_Date { get; set; }
    DateTime LU_Date { get; set; }
}

然后在DbContext类中，您可以覆盖SaveChanges方法并更新它们字段基于它们在ChangeTracker中的状态：

Then in your DbContext class, you can override the SaveChanges method and update those fields based on their status in the ChangeTracker:

foreach (var entry in ChangeTracker.Entries<ITrackedEntity>())
{

    if (entry.Entity.DataStatus == EntityDataStatus.Added)
    {
        entry.Entity.CR_Date = DateTime.Now;
        entry.Entity.LU_Date = DateTime.Now;
    }
    if (entry.Entity.DataStatus == EntityDataStatus.Modified)
    {
        entry.Entity.LU_Date = DateTime.Now;
    }
}

这篇关于EF 6未检索触发器更新的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！