CODEWITHSUNDEEP
c#entity-frameworkoptimizationentity-framework-6
so_user
so_user

Reputation: 43

Insert list into multiple tables with foreign key relationship Entity Framework

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.

Upvotes: 3

Views: 2785

Answers (2)

Sampath
Sampath

Reputation: 65870

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

Upvotes: 0

Gasper
Gasper

Reputation: 3030

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

Upvotes: 2

Related Questions