EF 6 not retrieving trigger updated fields

Question

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 comes back as:

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

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

but only if I add the AsNoTracking part.

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

OK - I'm not exactly thrilled with the return to the DB, but that's probably unavoidable. I use a metadata class as outlined here: [link](http://stackoverflow.com/questions/8932193/how-to-add-data-annotation-for-entities-automatically-created-by-data-first) and just added a `public partial class MyDBEntities : DbContext {...` — Christopher G. Lewis, Jan 03 '15 at 00:55

JC Ford · Answer 1 · 2014-12-18T15:39:50.537

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.

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.

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.

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

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

Sure, but I can't guarantee that my EF library is the only thing updating this database. — Christopher G. Lewis, Dec 18 '14 at 15:02
I added some code that should force a refresh after the SaveChanges — JC Ford, Dec 18 '14 at 15:29
overriding the SaveChanges method doesn't result in an updated record. Stepping through the code does show it hits the lambda, but no changes are made to the entity model. — Christopher G. Lewis, Jan 03 '15 at 00:37
OK - had to remove the generic and use `var entriesToReload = ChangeTracker.Entries()...` for it to work — Christopher G. Lewis, Jan 03 '15 at 00:49
The generic function only returns entries that inherit from the passed-in type. So if your entities aren't implementing the ITrackedEntity then the no entries will ever be returned and the code within the foreach won't execute. — JC Ford, Jan 04 '15 at 09:06
I just tried doing this and base.SaveChanges() gives me entity errors saying that the Column that is autogenerated via trigger is requiered. — ThunD3eR, Nov 26 '18 at 10:39

score 0 · Answer 2 · answered Nov 26 '18 at 11:00

I know that this answer has an accepted answer but the above did not work for me.

I did this instead:

    string insertSql=
    "INSERT(fields)VALUES(Myvalues)";

_dbContext.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sql);

The trigger gets triggered on the insert and auto generates the fields that need to be generated.

EF 6 not retrieving trigger updated fields

2 Answers2