Insert list into multiple tables with foreign key relationship Entity Framework

Question

I have Document table with DocumentID (PK) & DocumentName columns. Another table Connector_Document with ConnectorID (PK), DocumentID (FK) & some other columns.

I have a List<Document> & ConnectorId as input. I want to save List<Document> in Document table, get DocumentID of all these & save in Connector_Document table with the provided connectorID & fetched DocumentId.

With following input

List<Document> documents = new List<Document>{new Document{ DocumentName ="test1"},
new Document{DocumentName ="test2"}};
int connectorId = 5

My output should be:

Document Table

DocumentID      DocumentName
-----------------------------
    10              test1
    11              test2

Connector_Document table:

ConnectorID    DocumentID
      5             10
      5             11

I know I can loop through the List, add in document table get it's PK & use the same to insert in Connector_Document table. But, I don't think this is the efficient way & I am expecting some way should be provided by EF for this. Someone help me in this.

Had it been a single object instead of list, I could have done this:

using (Entities context = new Entities())
{
    using (var dbContextTransaction = context.Database.BeginTransaction())
    {
        context.Documents.Add(document);
        context.SaveChanges();

        if (connectorDocument != null)
        {
            connectorDocument.DocumentID = document.DocumentID;
            context.Connector_Document.Add(connectorDocument);
            context.SaveChanges();
        }

        dbContextTransaction.Commit();
    }
}

I am using Entity Framework 6.

Gasper · Accepted Answer · 2016-09-27 15:51:22Z

You can use AddRange to save the Documents list the way you want. But because you need a separate object for each Connector_Document row, you will still need a way to create them. I would suggest something like this:

using (Entities context = new Entities())
{
    using (var dbContextTransaction = context.Database.BeginTransaction())
    {
        context.Documents.AddRange(documents);
        context.SaveChanges();

        var connectorDocuments = from doc in documents
                                 select new Connector_Document 
                                 {
                                     ConnectorId = connectorId,
                                     DocumentId = doc.DocumentId
                                 };
        context.Connector_Document.AddRange(connectorDocuments);
        context.SaveChanges();
        dbContextTransaction.Commit();
    }
}

Sampath · Accepted Answer · 2016-09-27 15:54:57Z

0

You don't need a transaction here. Hence you have defined the relationships correctly, below code should work without any issue.You don't need to manipulate data insertion manually for the related objects.Those are done by the EF.

using (Entities context = new Entities())
      {

          foreach (var d in documents)
            {
               context.Documents.Add(d);
               context.SaveChanges();
            }
      }

edited Sep 27, 2016 at 15:54

answered Sep 27, 2016 at 15:48

Sampath

66.2k70 gold badges327 silver badges461 bronze badges

Collectives™ on Stack Overflow

Insert list into multiple tables with foreign key relationship Entity Framework

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related