none
How to modify a field in a table when a new record is created in another table? RRS feed

  • Question

  • Sir,

    I am developing an application in ASP.net MVC using Entity frameworks, I have the following tables:

    1. Pensioner (PensionerId (int Primary Key), Name(string), UpdatedOn(DateTime), Approve(bit))

    2. Spouse(SpouseId (int Primary Key), PensionerId(Foreign Key) Name(string))

    3. DeathEvent (EventId (int Primary Key), PensionerId (Foreign Key), Date(DateTime))

    What I want to do is whenever a user adds or edit data in tables Spouse and DeathEvent the fields UpdatedOn and Approve in Pensioner table should accordingly get modified by setting UpdatedOn with the current date and the field Approve as false.

    >>EDIT: 26/04/2017 22:37

    I observed that there are some fields on which I have put custom validation which I call through ModelMetaData.cs file.

    The problem is that when we edit any field in the record and try to save, an exception is thrown: for example, a custom validation rule defined for say Name field is to restrict duplicate entry becomes troublesome while editing the record for it will find the record with the same name.

    So, is there any way to manage custom validations which, checks for a duplicate entry in rest of the records and not the record that is being edited.

    >> 

    Please help.

    Best Regards,

    Arun


    • Edited by ArunKhatri Wednesday, April 26, 2017 5:06 PM
    Tuesday, April 25, 2017 3:56 PM

All replies

  • Hi ArunKhatri,

    >>What I want to do is whenever a user adds or edit data in tables Spouse and DeathEvent the fields UpdatedOn and Approve in Pensioner table should accordingly get modified by setting UpdatedOn with the current date and the field Approve as false.

    You could retrieve related Pensioner and update it, like this:

     var spouse = new Spouse() { SpouseId = 1, PensionerId = 1,Name = "Test" };
    db.Spouses.Add(spouse);
    var pension = db.Pensioners.Find(1);
    pension.UpdatedOn = DateTime.Now;
    pension.Approve = false;
    db.SaveChanges();

    >>So, is there any way to manage custom validations which, checks for a duplicate entry in rest of the records and not the record that is being edited.

    According to your description, the issue is more related asp.net mvc, I would suggest that you could post the issue on asp.net MVC forum for suitable support.

    https://forums.asp.net/1146.aspx/1?MVC

    Thanks for your understanding.

    Best regards,

    Cole Wu


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Thursday, April 27, 2017 6:53 AM
    Moderator
  • A clean & generic solution would be to track created and modified fields automatically. You start by creating a base class and your DbSet classes inherit from it. Then you Override SaveChanges in the DbContext and intercept your entities as they are saved, then update their created & modified fields automatically. You can also do additional validation checks and catch validation exceptions by trapping DbEntityValidationException

    example (not tested)

    public class BaseEntity
    {
        public DateTime? DateCreated { get; set; }
        public DateTime? DateModified { get; set; }
    }
    
    public class Pensioner : BaseEntity
    {
    	public int PensionerId { get; set; }
    	public string Name { get; set; }
    }
    
    public class YourContext : DbContext
    {
        public DbSet<Pensioner> Pensioners { get; set; }
    
        public override int SaveChanges()
        {
            AddTimestamps();
            return base.SaveChanges();
        }
     
        public override int SaveChanges()
        {
            AddTimestamps();
            return await base.SaveChanges();
        }
    
        private void AddTimestamps()
        {
            var entities = ChangeTracker.Entries().Where(x => x.Entity is BaseEntity && (x.State == EntityState.Added || x.State == EntityState.Modified));
    
            foreach (var entity in entities)
            {
                if (entity.State == EntityState.Added)
                {
                    ((BaseEntity)entity.Entity).DateCreated = DateTime.UtcNow;
                }
    
                ((BaseEntity)entity.Entity).DateModified = DateTime.UtcNow;
            }
        }
    }


    william xifaras


    Thursday, April 27, 2017 4:02 PM