none
ctx.SaveChanges() creates new records for related tables RRS feed

  • Question

  • Ok, this is my first stab at EF Code first, so forgive me if this sounds stupid.

    I have a simple database and want to add a record in the main trans table. It has 2 foreign keys to 2 reference tables. I wire up the data entry screen in WPF (MVVM) and populate the entity. This entity has the correct references to existing records in the reference tables. 

    However when I call save changes it adds the trans record, but also ADDS new records to the reference tables (producing duplicates). I do not want to add records to the reference tables, but rather use the existing records.

    I'm sure it's a simple thing, but I am stuck.  Any help would be appreciated...

    Thanks,

    Wednesday, January 14, 2015 5:24 PM

Answers

  • I was working from a sample I got from WintellectNOW training. That is how they set up the objects and yes in the database it created, it only store the foreign key.

    But I found a way...

    Adding the items to the context first seemed to do the trick.   

    using (WmsAttendanceContext ctx = new WmsAttendanceContext())
    {
        ctx.Employees.Attach(_record.Employee);
        ctx.Reasons.Attach(_record.Reason);
        ctx.Records.Add(_record);
        ctx.SaveChanges();
    }

    Thanks for your input, I do appreciate the feedback...

    Thursday, January 15, 2015 9:24 PM
  • ctx.Employees.Attach(_record.Employee);
    ctx.Reasons.Attach(_record.Reason);

    That above makes no sense. No disprespect but it doesn't. If the Record table in fact has two colums with constrants on them that the record couldn't be added without id(s) pointing to two records in the reference tables, then all you needed to be doing is setting those ID(s) in the Record table colums for the refernece tables and inserting the Record object. They are just look-up tables is what they are and adding of records to those tables with their respective objects should be done completly away from saving a Record object.

    And if you were using ADO.NET, SQL Command objects and T-SQL, the Record would be populated with the ID(s) of the reference table records needed, and the record is inserted into the table.

    You need to be looking at this from a SQL Server standpoint and not an ORM standpoint, as to what is happing behind the scenes. What the Attach is doing in this is kind of questinable, and it doesn't seem applicable. It may have worked. But did it really do anything but allow the code to execute without blowing up and did nothing in reality? However, I never use code first. I am a database first guy.

    On DB first and EF, there can be a relashionshipt between a primary table and a look-up table where adding the primary record also did an add of a record in the look-up table that should not be happenig. The simple solution is to remove the relashionship off of the ORM model that stopped the add action of a record to the look-up table. But cthe onstraint was kept between the SQL Server database tables.

    Thursday, January 15, 2015 10:00 PM

All replies

  • I have a simple database and want to add a record in the main trans table. It has 2 foreign keys to 2 reference tables. I wire up the data entry screen in WPF (MVVM) and populate the entity. This entity has the correct references to existing records in the reference tables.

    How can that be that you have existing records that belong to the parent when the parent never existed until you added a new parent? This seems questionable.  

    Wednesday, January 14, 2015 9:13 PM
  • Thank you for the response. I probably didn't phrase it clearly...

    I have a simple Warehouse attendance app that will record when people are absent and the reason. I have 2 fixed reference tables. One is a list of employees, the other is a list of reasons (s - Sick, V - Vacation, etc...)

    User enters a date, then selects a employee from a drop down and a reason from a drop down.

    01/08/2015      Joe Smith     S - Sick

    Both the employee object and reason object (in my record class) point to valid existing records from their reference tables.(i.e employee Joe smith has an Id of 57) 

    When I call ctx.SaveChanges();

    it adds the record to the attendance file, but also adds a record to the employee and the reason table. I now have 2 Joe Smiths in the employee table as well as 2 S - Sick Reason codes.  Joe Smith was added again and now the employee object points to Joe Smith with an Id of 58.

    Here is the record class:

    public class Record
    {
     
        public int Id { getset; }      
        public virtual Employee Employee { getset; }
        public virtual Reason Reason { getset; }
        public DateTime Date { getset; }
        public string Notes { getset; }
     
        public Record()
        {
            Date = DateTime.Now;
        }
        public string DayOfWeek
        {
            get { return Date.DayOfWeek.ToString(); }
        }
    }

    Wednesday, January 14, 2015 9:43 PM
  • publicvirtualEmployee Employee { get; set; }
    publicvirtualReason Reason { get; set; }

    The above objects have nothing to do with Record -- not really. Maybe, they should not be a part of Record. I would assume that there would be two properties in the Record object that point to an Employee and Reason record. The reference is behind the scene with the refernce to the ohter two records in the other tables where you couldn't add a Record object without the two reference properties in Record pointing  to a primary-key of the records in the two reference tables.  SQL Server would  blow up the transaction of saving a Record object to the Record table without the correct data in the respective columns pointing to two records in the reference tables.  

    The objects above should not be a part of Record, becuase otherwise, you are going to have the situation you have now.

    All you want is two properties in the Record object that will hold data pointing to the two reference table records by their ID(s). Of course, there would be two colums in the Record table using foreign-key constraints pointing to the two reference tables.  

    Wednesday, January 14, 2015 10:39 PM
  • I was working from a sample I got from WintellectNOW training. That is how they set up the objects and yes in the database it created, it only store the foreign key.

    But I found a way...

    Adding the items to the context first seemed to do the trick.   

    using (WmsAttendanceContext ctx = new WmsAttendanceContext())
    {
        ctx.Employees.Attach(_record.Employee);
        ctx.Reasons.Attach(_record.Reason);
        ctx.Records.Add(_record);
        ctx.SaveChanges();
    }

    Thanks for your input, I do appreciate the feedback...

    Thursday, January 15, 2015 9:24 PM
  • ctx.Employees.Attach(_record.Employee);
    ctx.Reasons.Attach(_record.Reason);

    That above makes no sense. No disprespect but it doesn't. If the Record table in fact has two colums with constrants on them that the record couldn't be added without id(s) pointing to two records in the reference tables, then all you needed to be doing is setting those ID(s) in the Record table colums for the refernece tables and inserting the Record object. They are just look-up tables is what they are and adding of records to those tables with their respective objects should be done completly away from saving a Record object.

    And if you were using ADO.NET, SQL Command objects and T-SQL, the Record would be populated with the ID(s) of the reference table records needed, and the record is inserted into the table.

    You need to be looking at this from a SQL Server standpoint and not an ORM standpoint, as to what is happing behind the scenes. What the Attach is doing in this is kind of questinable, and it doesn't seem applicable. It may have worked. But did it really do anything but allow the code to execute without blowing up and did nothing in reality? However, I never use code first. I am a database first guy.

    On DB first and EF, there can be a relashionshipt between a primary table and a look-up table where adding the primary record also did an add of a record in the look-up table that should not be happenig. The simple solution is to remove the relashionship off of the ORM model that stopped the add action of a record to the look-up table. But cthe onstraint was kept between the SQL Server database tables.

    Thursday, January 15, 2015 10:00 PM