Perform an Association from one to many with code first entity framework

Question

I am using EF 6.1 with code first, and trying to define a class of Invoice, which can consist of a number of Timesheet instances.

What I would like it to do is INSERT a new Invoice row into the database (which it does), and update the Timesheet table so that the foreign key for InvoiceId changes from NULL to the Id of the newly created invoice. Unfortunately, what is happening instead is that new rows are inserted into the timesheet table, and these are associated with the newly generated invoice.

My classes are:

public class TimeSheet
{
    public int Id { get; set; }
    public DateTime WeekStart { get; set; }
    public List<TimeSheetLine> TimeSheetLines { get; set; }

    public int? InvoiceId { get; set; }
    public virtual Invoice Invoice { get; set; }
}

public class Invoice
{
    public int Id { get; set; }
    public int? AgencyId { get; set; }
    public int? ClientId { get; set; }
    public int Rate { get; set; }
    public Client Client { get; set; }
    public Agency Agency { get; set; }
    public List<TimeSheet> Timesheets { get; set; }
}

And a mapping class:

 public class TimeSheetMap : EntityTypeConfiguration<TimeSheet>
{
    public TimeSheetMap()
    {
        this.HasKey(x => x.Id);
        this.ToTable("TimeSheet");                       
        this.HasOptional(x => x.Invoice)
            .WithMany(x => x.Timesheets)
            .HasForeignKey(x => x.InvoiceId);
    }
}

edit Code to build the Invoice Object:

public bool GenerateInvoice(InvoiceVM invoice)
    {
        //create an Invoice record from the InvoiceVM
        Invoice inv = new Invoice();
        List<TimeSheet> sheets = new List<TimeSheet>();

        foreach (var item in invoice.TimesheetList)
        {
            TimeSheet ts = manager.GetTimesheetById(item.Id);
            sheets.Add(ts);
        }
        inv.Timesheets = sheets;

        //save the invoice
        manager.GenerateInvoice(inv);
        return true;
    }

edit 2 manager class

        public bool GenerateInvoice(Invoice invoice)
    {
        bool ret = false;
        using (DataContext dc = new DataContext())
        {
            dc.Invoices.Add(invoice);
            dc.SaveChanges();
            ret = true;
        }

        return ret;
    }

Any obvious changes I can make to get this to work?

Answer 1

It appears as though you are using one context to pull your invoices from the database and a separate context to save the invoice. The DataContext inside the GenerateInvoice function doesn't know that the TimeSheets are already in the database, because they were retrieved by a different context, in manager.GetTimesheetById(item.Id);.

There are a few ways you could handle this process, here are two options:

1. Perform your lookups and attachment inside manager.GenerateInvoice(), instead of inside the ViewModel. This would be the cleanest approach, but would require the manager to deal with the TimeSheets. i.e.:

   Public bool GenerateInvoice(Invoice invoice, List<TimeSheet> timesheets)
   {
       bool ret = false;
       using (DataContext dc = new DataContext())
       {
           foreach (var item in timesheets)
           {
               TimeSheet ts = dc.GetTimesheetById(item.Id);
               invoice.TimeSheets.Add(ts);
           }
           dc.Invoices.Add(invoice);
           dc.SaveChanges();
           ret = true;
       }
       return ret;
   }
   

2. Perform two separate database calls, one to insert an Invoice, returning the invoice Id, and a second to update the timesheets.

   public int GenerateInvoice(Invoice invoice)
   {
       using (DataContext dc = new DataContext())
       {
           dc.Invoices.Add(invoice);
           dc.SaveChanges();
       }
       return invoice.Id;
   }
   
   public bool GenerateInvoice(InvoiceVM invoice)
   {
       //create an Invoice record from the InvoiceVM
       Invoice inv = new Invoice();
       //handle other fields for invoice here  
   
       //create invoice, with no timesheets
       int invoiceId = manager.GenerateInvoice(inv);
   
       //loop through the timesheets and assign the invoice to them.
       using (DataContext dc = new DataContext())
       {
           foreach (var item in invoice.TimesheetList)
           {
               TimeSheet ts = dc.GetTimesheetById(item.Id);
               ts.InvoiceId = invoiceId;
           }
           dc.SaveChanges();
       }
       return true;
   }
   

You will likely need to refactor either of these solutions to fit your full domain model, but hopefully these approaches can push you in the right direction.

Answer 2

Fundamentally the problem here was I was doing this inside each repository method

using (DataContext dc = new DataContext())

This meant that the object tracking was totally lost. Based on my experience I wouldn't recommend doing this.

I have moved to a module level DbContext instead, which can be used to group operations into a Unit of Work. As soon as I did this all the generated SQL started working just as I thought it should, with the related entities being updated properly in the database.