Insert list into multiple tables with foreign key relationship Entity Framework

Question

Insert list into multiple tables with foreign key relationship Entity Framework

c# entity-framework entity-framework-6 optimization

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.

so_user

View more on Stack Overflow

Gasper · Accepted Answer · 9/27/2016 3:51:22 PM

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();
    }
}

Insert list into multiple tables with foreign key relationship Entity Framework

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related