CODEWITHSUNDEEP
entity-frameworkbulkinsert
Jez
Jez

Reputation: 29993

Entity framework - inserting by ID

I'm in a situation where I'm importing lots of "link" records from an XML file, and I want to insert them in my SQL link table using Entity Framework. My link table is literally just 2 columns, both of which are FKs and constitute the PK:

[UserAssessmentId] [int] NOT NULL
[AnswerId] [int] NOT NULL

The way I'm used to doing inserts involves the following:

  1. Get the UserAssessment entity from the DB for userAssessmentId.
  2. Get the Answer entity from the DB for answerId.
  3. Add the Answer entity to the UserAssessment entity's Answers collection.
  4. Repeat 2 and 3 for each answerId to add.
  5. Call context.SaveChanges().

The trouble is that this is extremely DB intensive when adding hundreds of answers; EF has to get the record for each answer it is adding to the link table! I just want to insert a record with a given userAssessmentId, and a given answerId, and not go through the trouble of getting the entity first. EF needn't worry about whether the IDs I'm inserting are valid; just assume they are. Is there a way to get EF to do this or do I need to just use plain SQL?

Upvotes: 0

Views: 169

Answers (1)

Richard Deeming
Richard Deeming

Reputation: 31198

The simplest option would probably be to create a separate context and a simple entity to represent your link table.

[Table("Name of the link table")]
public class UserAssessmentAnswer
{
   public int UserAssessmentId { get; set; }
   public int AnswerId { get; set; }
}

public class UserAssessmentAnswerContext : DbContext
{
   public UserAssessmentAnswerContext() 
      : base("Connection string for the real context")
   {
   }

   public IDbSet<UserAssessmentAnswer> UserAssessmentAnswers
   {
      get { return Set<UserAssessmentAnswer>(); }
   }
}

Then you can use the new context and entity to insert your data:

using (var context = new UserAssessmentAnswerContext())
{
   context.UserAssessmentAnswers.Add(new UserAssessmentAnswer
   {
      UserAssessmentId = ...,
      AnswerId = ...
   });
   ...
   context.SaveChanges();
}

EDIT
You'll need to turn off database initialization for the new context. In your configuration file, add:

<entityFramework>
   <contexts>
      <context 
         type="YourNamespace.UserAssessmentAnswerContext, YourAssembly" 
         disableDatabaseInitialization="true"
      />
   </contexts>
</entityFramework>

Or, you can add the following code to your startup:

Database.SetInitializer<UserAssessmentAnswerContext>(null);

Upvotes: 2

Related Questions