Insert Into two Tables with Foreign Key relationship

Question

In my MVC application, I have two entities called Ticket and Attachment, and when inserting a new ticket record, I also want to insert new records for attachments with the FK of previously inserted Ticket's ID.

I have a look at some samples as this page, but I need to use a loop for the multiple attachments in a single method so that users can attach multiple files when creating a new ticket. Could you give a sample stored procedure or a sample method for Entity Framework to solve this problem?

Here are these two entities:

Ticket:

public class Ticket
{
    [Key] 
    public int ID { get; set; }

    public string Comment { get; set; }

    //Navigation Property
    public virtual ICollection<Attachment> Attachments { get; set; }

}

Attachment:

public class Attachment
{
    [Key]
    public int ID { get; set; }

    //Foreign key for Ticket
    public int TicketID { get; set; }   

    public byte[] FileData { get; set; }

    public string FileMimeType { get; set; }

    //Navigation Property 
    public virtual Ticket Ticket { get; set; }
}

Answer 1

Take a look at AssociationAttribute in Entity Framework. It will allow you to set an object to a property in order to set the foreign key. If you add something like:

public class Attachment {
    ...
    [Association(Name = "ticket", ThisKey = "TicketId", OtherKey = "Id", IsForeignKey = true)]
    public Ticket Ticket { get; set;}
    [Column(DBType = "INT NOT NULL" ...]
    public int TicketId { get; set; }
    ...
}

to your class, then you should be able to do assign a ticket object to you attachment object's "Ticket" property like this:

public UploadTicket(string ticketComment, List<Attachment> attachments) {
    (using db = new DataContext()) {
        var ticket = new ticket();
        ticket.Comment = ticketComment;
        db.Tickets.InsertOnSubmit(ticket);
        foreach (var att in attachments) {
            var attachment = new attachment();
            attachment.Ticket = ticket; // <- this will assign the TicketId property automatically upon insert
            attachment.FileData = att.FileData;
            attachment.FileMimeType = att.FileMimeType;
            db.Attachments.InsertOnSubmit(attachment);
        }
        db.SubmitChanges();
    }
}

Answer 2

Try something like this (this is simplified, of course - but it shows the basic mechanism that should be used - instantiating objects and connecting them via their navigation properties, and saving only once for the whole object graph):

// establish the DbContext
using (TicketModel ctx = new TicketModel())
{
    // create a ticket
    Ticket t1 = new Ticket
    {
        Comment = "This is ticket #1"
    };

    // add two attachments to it
    Attachment a1 = new Attachment { FileMimeType = "text/json" };
    t1.Attachment.Add(a1);

    Attachment a2 = new Attachment { FileMimeType = "application/octet-stream" };
    t1.Attachment.Add(a2);

    // add the ticket to the context
    ctx.Ticket.Add(t1);

    // save everything
    ctx.SaveChanges();
}

With this, you should have a ticket in your database, and two connected attachments (with their TicketID column set to the correct value for the ticket)

Answer 3

By improving the mechanism that marc_s suggested I solved the problem. For those who need such a kind of solution I posted the final code:

Method in Controller:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult _Create([Bind(Exclude = null)] IssueViewModel ticketViewModel, IEnumerable<HttpPostedFileBase> files)
{
    try
    {
        if (ModelState.IsValid)
        {
            ticketViewModel.FileAttachments = new List<FileAttachment>();
            foreach (var upload in files)
            {
                if (upload != null && upload.ContentLength > 0)
                {
                    if (upload.ContentType == "application/pdf" 
|| upload.ContentType == "application/vnd.openxmlformats-officedocument.word" 
|| upload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                    {
                        FileAttachment fileAttachment = new FileAttachment
                        {
                            Created = DateTime.Now,
                            FileMimeType = upload.ContentType,
                            FileData = new byte[upload.ContentLength]
                        };
                        upload.InputStream.Read(fileAttachment.FileData, 0, upload.ContentLength);
                         ticketViewModel.FileAttachments.Add(fileAttachment);
                    }
                    else
                    {
                        return PartialView("_Create");
                    }
                }
            }
            repository.SaveTicket(ticketViewModel.Issue, ticketViewModel.FileAttachments);
            return RedirectToAction("Completed");
        }
    }
    catch (RetryLimitExceededException /* dex */)
    {
        //Log the error (uncomment dex variable name and add a line here to write a log.)
        ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists, see your system administrator.");
    }      
    return View(ticketViewModel);
}

Method in Data Layer:

public void SaveTicket(Ticket ticket, IEnumerable<FileAttachment> fileAttachment)
{
    context.Tickets.Add(ticket);
    foreach (FileAttachment f in fileAttachment)
    {
        context.FileAttachments.Add(f);
    }
    context.SaveChanges();
}