Insert record into DB in one to many relation model

Question

I am just starting with SQL and trying to get my first example working. I have two tables with one to many relation:

public class Customer
{
    public Guid Id { get; set; }
    public string CompanyName { get; set; }
    public string Address { get; set; }
    public virtual ICollection<Project> Projects { get; set; }
}

and

public class Project
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string ProjectType { get; set; }
    public Customer Customer { get; set; }
}

Then I am executing following command through API ProjectsController:

[HttpPost]
public async Task<IActionResult> Post(Project project)
{
    Customer newCustomer = _context.Customers
                                   .FirstOrDefault(c => c.Id == project.Customer.Id);

    Project newProject = new Project
                             {
                                 Name = project.Name,
                                 ProjectType = project.ProjectType,
                                 Customer = newCustomer,
                             };

    _context.Projects.Add(newProject);

    await _context.SaveChangesAsync();

    return Ok(newProject.Id);
}

However as I already have a customer with this ID in the database, I am getting an exception (while creating new project, there is no need to create new customer; customers are selected from a dropdown box on the project creation dialog):

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_ProjectStates'. Cannot insert duplicate key in object 'dbo.ProjectStates'. The duplicate key value is (057cfca4-934b-40a4-a3f8-44840e532f4a). The statement has been terminated.

How to do it the right way?

https://stackoverflow.com/questions/16433338/inserting-new-records-with-one-to-many-relationship-in-sqlalchemy Try this one. — Gohar Latif, Dec 02 '21 at 14:12

score 1 · Accepted Answer · answered Dec 02 '21 at 14:33

You should add CustomerId property for your Project entity so it would look like this:

public class Project
  {
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string ProjectType { get; set; }
    public Guid CustomerId { get; set; }
    public Customer Customer { get; set; }
  }

Then when you'are adding new project you are passing CustomerId without passing Customer object - it is only navigation property for example if you want to fetch Project info with Customer included.

[HttpPost]
public async Task<IActionResult> Post(Project project)
{
  //You can use FirstOrDefaultAsync here
  Customer newCustomer = await _context.Customers.FirstOrDefaultAsync(c => c.Id == project.CustomerId);

  // You should also add validation here because when there is no customer with that id, you'll recive NullReferenceException while assigning CustomerId so you can add something like this
  if(newCustomer is null)
  {
    return NotFound();  
  }

  Project newProject = new Project
  {
    Name = project.Name,
    ProjectType = project.ProjectType,
    CustomerId = newCustomer.Id,
  };

  //You can use AddAsync here
  await _context.Projects.AddAsync(newProject); 
  await _context.SaveChangesAsync();
  return Ok(newProject.Id);
}

score 1 · Answer 2 · answered Dec 02 '21 at 14:42

Try this, Just change _context.Projects.Add(newProject); to newCustomer.Projects.Add(newProject);

Also, remove the the Customer = newCustomer, assignment as we are adding/assigning project to existing customer.

On a whole your new method looks like this. (with above two changes)

[HttpPost]
public async Task<IActionResult> Post(Project project)
{
    Customer newCustomer = _context.Customers
                                   .FirstOrDefault(c => c.Id == project.Customer.Id);

    Project newProject = new Project
                             {
                                 Name = project.Name,
                                 ProjectType = project.ProjectType
                             };

    newCustomer.Projects.Add(newProject);

    await _context.SaveChangesAsync();

    return Ok(newProject.Id);
}

Hope you understood what exactly is the change.

Insert record into DB in one to many relation model

2 Answers2