question

LiamCHolmes-8850 avatar image
0 Votes"
LiamCHolmes-8850 asked ·

Not all FK's updating con savechanges

Afternoon,
Hope someone can help me, I have the following model:

75151-thisone.png




The rough code (I know its a mess) at present while I am trying to workout what is going on is as follows to add a note and link it to the changelog:

         public async Task CreateNote(NoteViewModel noteViewModel, int customerID, int userID)
         {
             var customer = await _unitOfWork.CustomerRepository.GetAsync(customerID);
    
             customer.CustomerNotes = new Collection<CustomerNote>()
             {
                 new CustomerNote()
                 {
                     Note = noteViewModel
                 }
             };
    
             await AddChanges(customer, userID, "Create Note");
    
             _unitOfWork.CustomerRepository.Update(customer);
    
             await _unitOfWork.Complete();
         }
    
         private async Task<Customer> AddChanges(Customer customer, int userID, string changeType)
         {
             customer.CustomerChangeLogs.Add(
                 new CustomerChangeLog()
                 {
                     ChangeLog = new ChangeLog()
                     {
                         ChangeType       = changeType,
                         ChangedByID      = userID,
                         ChangeLogDetails = await FindChanges(customer)
                     }              
             });
             return customer;
         }

FindChanges(customer), just iterates through the new details and old details and checks what has been modified, and returns a list and is working as expected.

My issues is that all of the PK/FK's are adding apart from the one with the red arrow at which point I get the following error:

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CustomerNote_CustomerChangeLog_CustomerChangeLogID". The conflict occurred in database "Test", table "Logs.CustomerChangeLog", column 'CustomerChangeLogID'. The statement has been terminated.

Not sure how much other code or detail to post up, so if you need anything else please let me know.

Cheers,

Models:

 namespace Data.Entities
 {
     using Data.Entities.Shared;
     using System.Collections.Generic;
     using System.Collections.ObjectModel;
     using System.ComponentModel.DataAnnotations;
    
     public class Customer
     {
         [Key]
         public int CustomerID { get; set; }
         [Required]
         [MaxLength(128)]
         public string Name { get; set; }
         public int AddressID { get; set; }
         public int ContactDetailID { get; set; }
         public int? ScheduleID { get; set; }
         public int CompanyID { get; set; }
    
         public virtual Address Address { get; set; }
         public virtual ContactDetail ContactDetail { get; set; }
         public virtual Schedule Schedule { get; set; }
         public virtual Company Company { get; set; }
    
         public virtual ICollection<CustomerNote> CustomerNotes { get; set; }
         public virtual ICollection<CustomerChangeLog> CustomerChangeLogs { get; set; } = new Collection<CustomerChangeLog>();
         public virtual ICollection<Site> Sites { get; set; }
     }
 }
    
 namespace Data.Entities
 {
     using Entities.Shared;
     using System.ComponentModel.DataAnnotations;
    
     public class CustomerChangeLog
     {
         [Key]
         public int CustomerChangeLogID { get; set; }
         public int CustomerID { get; set; }
         public int ChangeLogID { get; set; }
    
         public virtual Customer Customer { get; set; }
         public virtual ChangeLog ChangeLog { get; set; }
         public virtual CustomerNote CustomerNote { get; set; }
     }
 }
    
 namespace Data.Entities
 {
     using Data.Entities.Shared;
     using System.ComponentModel.DataAnnotations;
    
     public class CustomerNote
     {
         [Key]
         public int CustomerNoteID { get; set; }
         public int CustomerID { get; set; }
         public int NoteID { get; set; }
         public int CustomerChangeLogID { get; set; }
    
         public virtual Customer Customer { get; set; }
         public virtual Note Note { get; set; }
         public virtual CustomerChangeLog CustomerChangeLog { get; set; }
     }
 }
sql-server-generaldotnet-entity-framework-core
thisone.png (45.5 KiB)
· 2
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My understanding of the code above is that you are trying to insert data into the database without actually using SQL?

Well, use can use Profiler to spy on your application to see what SQL statements it actually produces. Maybe that can help you to figure out what you have missed.

Then again, an SQL Server person, I can't but feel that it would be all simpler if you sent the SQL statements directly.

0 Votes 0 ·

Thanks for that.

I am using entity framework / code first.

The issue is here:

                  new CustomerNote()
                  {
                      Note = noteViewModel
                  }
    
 because if i do this this:
    
                  new CustomerNote()
                  {
                      Note = noteViewModel
 CustomerChangeLogID = 1 (arbitrary number)
                  }

it will insert, in the profiler it looks like 0 is being sent to SQL.

Using entity framework with navigational properties etc... in place I was under the impression that it would populate that for me on the savechangesasync().

I'm not sure what to do or start to look for an answer on this

0 Votes 0 ·

1 Answer

LiamCHolmes-8850 avatar image
0 Votes"
LiamCHolmes-8850 answered ·

Well I got it, I had to have a copy of the model in both locations when writing to the DB.

So quick and dirty, i will tidy it up later:

         public async Task CreateNote(NoteViewModel noteViewModel, int customerID, int userID)
         {
             var customer = await _unitOfWork.CustomerRepository.GetAsync(customerID);
    
             customer.CustomerNotes = new Collection<CustomerNote>()
             {
                 new CustomerNote()
                 {
                     Note = noteViewModel,
                 }
             };
    
             await GetChanges(customer, userID, "Create Note");
    
             customer.CustomerNotes.First().CustomerChangeLog = customer.CustomerChangeLogs.First();
    
             _unitOfWork.CustomerRepository.Update(customer);
    
             await _unitOfWork.Complete();
         }
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.