Entity Framework writes in Context but not Database

Question

We use an UnitOfWork approach for an application based on EF 6.2.0 in C# with a SQL Server 2017 Express (14.0.1000) database backend.

Our OwnContext.cs inherited from DbContext should fill the fields Id, ChangedDate and ChangedUser automatically, which is part of every entity in our db model:

public partial class OwnContext : DbContext {

    public override int SaveChanges()
    {
        bool hasChanges = false;

        this.ChangeTracker.DetectChanges();

        #region Fill Created, Changed

        foreach (var item in this.ChangeTracker.Entries()
            .Where(x => (x.State == EntityState.Added || x.State == EntityState.Modified || x.State == EntityState.Deleted)))
        {
            if (item.State == EntityState.Deleted)
            {
                hasChanges = true;
            }
            else
            {
                // Id
                PropertyInfo pI = item.Entity.GetType().GetProperty("Id");
                if (pI != null)
                {
                    Guid id = (Guid)pI.GetValue(item.Entity, null);
                    if ((Guid)id == Guid.Empty)
                    {
                        id = Guid.NewGuid();
                        pI.SetValue(item.Entity, id);
                    }
                }

                DateTime now = DateTime.Now;

                // Timestamp & User
                pI = item.Entity.GetType().GetProperty("CreatedDate");
                if (pI != null)
                {
                    var date = pI.GetValue(item.Entity, null);
                    // Only if empty on new records
                    if (date == null || date.Equals(DateTime.MinValue))
                    {
                        pI.SetValue(item.Entity, now);
                        pI = item.Entity.GetType().GetProperty("CreatedUser");
                        if (pI != null)
                        {
                            pI.SetValue(item.Entity, Environment.UserName); 
                        }
                    }
                    pI = item.Entity.GetType().GetProperty("ChangedDate");
                    if (pI != null)
                    {
                        pI.SetValue(item.Entity, now);
                    }
                    pI = item.Entity.GetType().GetProperty("ChangedUser");
                    if (pI != null)
                    {
                        pI.SetValue(item.Entity, Environment.UserName); 
                    }
                }

                hasChanges = true;

            }

        }

        #endregion

        int countChanges = 0;

        if (hasChanges)
        {
            // Call SaveChanges Method from Context;
            try
            {
                countChanges = base.SaveChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
        return countChanges;
    }
}

For new records that works perfectly, but not for modified ones.

The new datetime value in the ChangedDate field appears only in the context but does not write to database. If I step through the code, I see item.State is EntityState.Modified and the countChanges return value is 1 and the value in item.Entity everything seems correct. If query the object the new value is visible but the value in database does not change, so if I dispose my context the old value will reappear.

Really confusing is the fact that the field value I change in the ui to test it is changed in the db, but not the field ChangedDate.

Why?

Here is a example of one entity class and the configuration, created by Devarts Entity Devloper:

public partial class Abo {

    public Abo()
    {
        OnCreated();
    }

    #region Properties

    public virtual global::System.Guid Id
    {
        get;
        set;
    }

    // ...

    public virtual global::System.Nullable<System.DateTime> ChangedDate
    {
        get;
        set;
    }

    public virtual global::System.Nullable<System.DateTime> CreatedDate
    {
        get;
        set;
    }

    public virtual string CreatedUser
    {
        get;
        set;
    }

    public virtual string ChangedUser
    {
        get;
        set;
    }
}   


public partial class AboConfiguration : EntityTypeConfiguration<Abo>
{

    public AboConfiguration()
    {
        this
            .HasKey(p => p.Id)
            .ToTable("Abos", "dbo");
        // Properties:
        this
            .Property(p => p.Id)
                .IsRequired()
                .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None)
                .HasColumnType("uniqueidentifier");

        // ...
        this
            .Property(p => p.CreatedDate)
                .HasColumnType("datetime");
        this
            .Property(p => p.ChangedDate)
                .HasColumnType("datetime");
        this
            .Property(p => p.CreatedUser)
                .HasMaxLength(32)
                .HasColumnType("varchar");
        this
            .Property(p => p.ChangedUser)
                .HasMaxLength(32)
                .HasColumnType("varchar");

        OnCreated();
    }

    partial void OnCreated();

}

The SQL DDL of the corresponding table:

CREATE TABLE [dbo].[Abos](
    [Id] [uniqueidentifier] NOT NULL,
/* .. */
    [ChangedDate] [datetime] NULL,
    [CreatedDate] [datetime] NULL,
    [ChangesUser] [varchar](32) NULL,
    [CreatedUser] [varchar](32) NULL
 CONSTRAINT [PK_dbo.Abos] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Looking at the wrong databse? It is a known issue when using the internal db server that the website runs a COPY that gets reset on every project start. — TomTom, Jul 23 '18 at 07:58
We use a ConnectString from app.config to establish the db connection. `connectionString="Data Source=localhost\SQLEXPRESS; Initial Catalog=Abo; Integrated security=true;MultipleActiveResultSets=True;"` How a wrong db server could be used? Btw. its a WinForms app. — Pixel Hunter, Jul 23 '18 at 08:04
I don't see any Unit of Work here. UoW means creating a new context, doing stuff with it, calling `SaveChanges()` and disposing it. This looks like an attempt to *bypass* `SaveChanges` (why?) by ... reimplementing the autogenerated ID feature? All of these things are already available in EF, easily configurable with attributes or in the context's configuration methods. Are you trying to add *auditing columns* to an entity? — Panagiotis Kanavos, Jul 23 '18 at 08:17
Where is the *context's* configuration? Does the the `ChangedDate` property exist in the *entity*? Is it mapped in the configuration? Why implement the auditing properties in the *client* instead of using triggers and default constraints on the server? — Panagiotis Kanavos, Jul 23 '18 at 08:20
We use the UoW approach to have a several functions on each entity e.g. like `GetNextNr` but only to have it once implemented, but this is offtopic here and works well. The reason for this inherited `class OwnContext` is to implement the auto filling of `ChangedDate` and `ChangedUser` fields. — Pixel Hunter, Jul 23 '18 at 08:26
Sure there is a configuration an a mapped field in there. Again for new records that works perfectly! — Pixel Hunter, Jul 23 '18 at 08:32
@PixelHunter so you are asking about *auditing* fields, not UoW. There are [duplicate SO questions](https://stackoverflow.com/questions/43314620/entity-framework-core-creation-and-update-fields) that show how to do this. The fields *must* exist on the property. Btw `Id` should be configured as an autogenerated property, not in the SaveChanges override. The best/safest way to implement the audit fields is *triggers*, but you can use the SaveChanges override if you accept lost changes because some job or process will *always* have to be run outside your application. — Panagiotis Kanavos, Jul 23 '18 at 08:36
@PixelHunter other than that, you didn't provide enough info to replicate the problem - entity class, context configuration, table schema? — Panagiotis Kanavos, Jul 23 '18 at 08:36
@PixelHunter btw SQL Server offers [change tracking](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-2017) in all versions since 2008 at least and all editions. Instead of two properties that *don't* explain what happened and don't cover deletions, you get the changes, date, reason, keys, and possibly even the column values for a configurable window in the past. The feature is cheap so it can be used to find table changes easily. — Panagiotis Kanavos, Jul 23 '18 at 08:39
@PixelHunter History tables are another option, also more useful than `CreatedBy/CreatedOn` fields, although they are more expensive and before SQL Server 2017 required triggers to implement. With the system-versioned *temporal tables* in 2017, you can implement a history table easily by making your tables temporal — Panagiotis Kanavos, Jul 23 '18 at 08:40
@PanagiotisKanavos Change Tracking seems a bit over for our purpose, we want only to see when and who did the last change, there is no need to see *what* was changed. Of course the are the fields ChangeDate and ChangeUser as property in the entity class and in the db table, I added the source into my question. Tiggers might me a solution but I am not very used to it, and I like to have the implementation only in one place, so I prefer to have it in c# if possible. — Pixel Hunter, Jul 23 '18 at 09:27
@PanagiotisKanavos The reason why I have implemented Id generation in the SaveChanges is that I expericences that I can access it then rightaway, not have to query for it, sometimes that is useful for me. Do you see any disadvanteges in this approach? — Pixel Hunter, Jul 23 '18 at 09:27
I guess that at this point, the property changes on the entity object received by the change tracker are not DTO relevant. Have you tried to apply the changes through "item.CurrentValues" ? — Patrick, Jul 23 '18 at 10:30
@Patrick Great! That's the solution, please add that as answer. `item.CurrentValues["ChangedDate"] = DateTime.Now;` does the job! — Pixel Hunter, Jul 23 '18 at 11:55

score 1 · Accepted Answer · answered Jul 23 '18 at 13:24

1

At this point, the property changes on the entity object received by the change tracker are not DTO relevant. You need to apply the changes through the DbEntityEntry.CurrentValues Property instead.

item.CurrentValues["ChangedDate"] = DateTime.Now;

answered Jul 23 '18 at 13:24

Patrick

668
4
11

score 0 · Answer 2 · answered Jul 23 '18 at 09:33

0

You need to call context.savechanges to save the data to db

answered Jul 23 '18 at 09:33

Vilsad P P

1,529
14
23

1

Please look in the last try..catch there you will find a `base.SaveChanges()` – Pixel Hunter Jul 23 '18 at 09:36
thanks, missed it, but it should not be base.SaveChanges(), it should be this.SaveChanges() since the tables are being tracked is on this context, not in the base context. – Vilsad P P Jul 23 '18 at 11:28
I don't think so, because my method should override the `Context.SaveChanges()` from the context but finally I have to call it with `base.SaveChanges()`. Your suggestion to call `this.SaveChanges()` would call my own method recusively which I don't want. – Pixel Hunter Jul 23 '18 at 12:01

Entity Framework writes in Context but not Database

2 Answers2