Why am I getting new records created in table a when I edit a record of another table?-CodePudding

I have an Invoice object that has a list of items and other properties. Whenever I execute the service that edits an invoice, each item get doubled for some reason.

For example, here's a snapshot of the items table after creating an invoice with two items: Items table

And here's a snapshot of it after executing EditInvoice service: Items table after editing

Data Model

Invoice

public class Invoice
    {
        public string InvoiceId { get; set; } = GenerateID.GenerateInvoiceID();
        public string Description { get; set; }
        public List<InvoiceItem> Items { get; set; }
        public DateTime InvoiceDate { get; set; }
        public string PaymentTerms { get; set; }
        public DateTime PaymentDue { get; set; }
        public int TotalPrice { get; set; }
        public string Status { get; set; } = "pending";
        public Client Client { get; set; }
        public string ClientId { get; set; }
        

        public string BillFromAddress { get; set; }
        public string BillFromCity { get; set; }
        public string BillFromCountry { get; set; }
        public string BillFromPostal { get; set; }

    }

InvoiceItem

public class InvoiceItem
    {
        public string InvoiceItemId { get; set; } = GenerateID.GenerateItemID();
        public string InvoiceId { get; set; }
        [Required]
        public string Name { get; set; }
        [Required]
        public int Quantity { get; set; }
        [Required]
        public int Price { get; set; }

        public InvoiceItem()
        {
        }

        public InvoiceItem(string itemName, int quantity, int price)
        {
            Name = itemName;
            Quantity = quantity;
            Price = price;
        }

    }

My InputModel

public class InputModel
    {
        [Required]
        public string Description { get; set; }

        [Required]
        [DataType(DataType.Date)]
        public DateTime InvoiceDate { get; set; }

        public string PaymentTerms { get; set; }
        public DateTime PaymentDue { get; set; }


        public Client Client { get; set; }
        

        
        public List<InvoiceItem> Items { get; set; } = new List<InvoiceItem>(16);


        [Required]
        public string BillFromAddress { get; set; }
        [Required]
        public string BillFromCity { get; set; }
        [Required]
        public string BillFromCountry { get; set; }
        [Required]
        public string BillFromPostal { get; set; }


        public void PopulateItems()
        {
            for (int i = 0; i < Items.Capacity; i  )
            {
                Items.Add(new InvoiceItem());
            }
        }
    }

My Page Models

CreateInvoiceModel

public class CreateInvoiceModel : PageModel
    {
        
        public readonly InvoiceService _service;

        [BindProperty]
        public InputModel Input { get; set; }

        public CreateInvoiceModel(InvoiceService service)
        {
            _service = service;
        }


        public void OnGet()
        {
            Input = new InputModel();
            Input.PopulateItems();
        }


        public async Task<IActionResult> OnPost()
        {

            if (ModelState.IsValid)
            {
                _service.AddInvoice(Input);
                return RedirectToPage("/Index");
            }

            return Page();

        }

    }

EditInvoiceModel

public class EditInvoiceModel : PageModel
    {

        public readonly InvoiceService _service;

        [BindProperty]
        public InputModel Input { get; set; }

        public string InvoiceId { get; set; }

        public EditInvoiceModel(InvoiceService service)
        {
            _service = service;
        }

        
        public async void OnGet(string id)
        {
            Invoice invoice = await _service.GetInvoice(id);
            InvoiceId = invoice.InvoiceId;
            Input = new InputModel();

            Input.Items = invoice.Items;

            Input.BillFromAddress = invoice.BillFromAddress;
            Input.BillFromCity = invoice.BillFromCity;
            Input.BillFromPostal = invoice.BillFromPostal;
            Input.BillFromCountry = invoice.BillFromCountry;

            Input.Client = invoice.Client;

            Input.InvoiceDate = invoice.InvoiceDate;
            Input.PaymentTerms = invoice.PaymentTerms;
            Input.Description = invoice.Description;

        }

        public  async Task<IActionResult> OnPost(string id)
        {
           if(ModelState.IsValid)
            {
                _service.EditInvoice(Input, id);
                return RedirectToPage("/ViewInvoice", new { id = id });
            }

            return Page();
        }

    }

Services

AddInvoice

public async void AddInvoice(InputModel input)
        {
            Invoice invoice = new Invoice();


            invoice.Description = input.Description;
            invoice.Items = input.Items;
            invoice.InvoiceDate = input.InvoiceDate;
            invoice.PaymentTerms = input.PaymentTerms;
            invoice.Client = input.Client;
            

            invoice.BillFromAddress = input.BillFromAddress;
            invoice.BillFromCity = input.BillFromCity;
            invoice.BillFromCountry = input.BillFromCountry;
            invoice.BillFromPostal = input.BillFromPostal;

            //Attaching the invoice id to each item in the invoice
            foreach (var item in invoice.Items)
            {
                item.InvoiceId = invoice.InvoiceId;
            }

            //IndexModel.invoices.Add(invoice);
            _context.Add(invoice);
            await _context.SaveChangesAsync();
        }

EditInvoice

public async void EditInvoice(InputModel input, string id)
        {
            var invoice = await _context.Invoices.FindAsync(id);

            if (invoice == null) { throw new Exception("Unable to find the invoice"); }
            
            invoice.Items = input.Items;

            invoice.Description = input.Description;
            invoice.InvoiceDate = input.InvoiceDate;
            invoice.PaymentTerms = input.PaymentTerms;
            invoice.Client = input.Client;
            

            invoice.BillFromAddress = input.BillFromAddress;
            invoice.BillFromCity = input.BillFromCity;
            invoice.BillFromCountry = input.BillFromCountry;
            invoice.BillFromPostal = input.BillFromPostal;

           
            await _context.SaveChangesAsync();
        }

CodePudding user response：

Your code has multiple problems:

First of all in your InvoiceItem you have the following line

public string InvoiceItemId { get; set; } = GenerateID.GenerateItemID();

which means that whenever n InvoiceItem is instanciated a new Id is generated which is not correct because there is a difference between actually creating an InvoiceItem and just creating an instance of the class InvoiceItem. A new Id should only be generated if a new InvoiceItem is created but not for example if an existing InvoiceItem is loaded from the database (in both cases an instance of the class InvoiceItem is created but only in the first one an actual InvoiceItem shall be created). So removing the id generation from the property declaration and only performing is when an InvoiceItem shall actually be created will fix this part of the problem.

The second problem is in EditInvoice where you call

var invoice = await _context.Invoices.FindAsync(id);

// ...
            
invoice.Items = input.Items;

in the first line you are loading the invoice from the database but you are not including the invocies and therefore they do not get loaded and EF does not even know they exist. So when you are calling invoice.Items = input.Items you are assigning the list with the new InvoiceItems with the newly generated ids (as explained above) and they will therefore be added to the database and hence duplicated.

So instead of replacing the whole list you should be editing existing items and only adding InvoiceItems which have actually been created to the list.

CodePudding user response：

Instead of

invoice.Items = input.Items;

You can work with:

For adding a new InvoiceItem record, presume the invoice item doesn't have InvoiceId (foreign key value), use .Add() as adding a new record.
For the existing InvoiceItem record, presume the invoice item has InvoiceId (foreign key value), set the entity, and modify its state as Modified. Make sure that the entity's Id (primary key (value)) matches with the record in the database table. Reference: Attaching an existing but modified entity to the context

public async void EditInvoice(InputModel input, string id)
{
    var invoice = await _context.Invoices.FindAsync(id);

    if (invoice == null) { throw new Exception("Unable to find the invoice"); }
            
    foreach (var item in input.Items)
    {
        if (String.IsNullOrEmtpy(item.InvoiceId))
        {
            invoice.Add(item);
        }
        else
        {
            context.Entry(item).State = EntityState.Modified;
        }
    }

    invoice.Description = input.Description;
    invoice.InvoiceDate = input.InvoiceDate;
    invoice.PaymentTerms = input.PaymentTerms;
    invoice.Client = input.Client;
            

    invoice.BillFromAddress = input.BillFromAddress;
    invoice.BillFromCity = input.BillFromCity;
    invoice.BillFromCountry = input.BillFromCountry;
    invoice.BillFromPostal = input.BillFromPostal;

           
    await _context.SaveChangesAsync();
}

Or you can work as:

foreach (var item in input.Items)
{
    context.Entry(item).State = String.IsNullOrEmtpy(item.InvoiceId) 
        ? EntityState.Added 
        : EntityState.Modified;
}

Reference: Insert or update pattern

Although the provided documentation is regarding Entity Framework 6, it supports in Entity Framework Core.