SQLBulk Copy With Related Entities (Entity Framework)

Question

Say I have the below entities. (Heavily Simplified for brevity, but the key properties are included)

public  class Crime 
{

       [Key]
       public int CrimeId {get;set;}
       public virtual ICollection<Victim> Victims {get;set;}
       public virtual CrimeDetail Detail {get;set}

}

public class Victim
{
       [Key]
       public int VictimId {get;set;}
       public string VictimCategory {get;set;}
}

public class CrimeDetail
{
       [Key]
       public int id {get;set;}
       public string DetailText {get;set;}
}

I have 600,000+ of these records to insert into SQL Server 2008 Express R2, which takes quite some time using Entity Framework 4.4.

Ideally I'd like to use SQLBulkCopy to batch insert these records, but since that doesn't support complex types (At least not out of the box), then I'm trying to find some other potential solutions.

I'm open to changing my model if necessary.

EDIT: would the AsDataReader Extension method from MSDN help in this instance?

vittore · Accepted Answer · 2013-03-21 11:24:06Z

1

When having the same issue we end up having code-first generated database with EF and strongly typed generated datasets to be used for SQLBulkCopy.

(We never really coded those classes, they were generated using xsd util from xsd definition of 1-10gb xml file. I'm trying to recall right now when we havent generated typed datasets from the same xsd, but that seems irrelevant to your issue.)

Depending on how you are getting those 600k+ records you either can change the code to use generated strongly-typed datasets or use some object-to-object mapper to map your EF POCO objects to datasets as properties going to be named the same.

Here is a link on generating strongly typed datasets.

Here is an example how to use SqlBulkInsert.

edited Mar 21, 2013 at 11:24

answered Mar 21, 2013 at 11:18

vittore

17.6k6 gold badges46 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MrBliz Over a year ago

Can you expand on that a little?

MrBliz Over a year ago

The records are coming in from another database, and are then mapped to our Code First generated entities. Thanks for the tips, i'll let you know how i get on.

vittore Over a year ago

If your another database is in sqlserver as well, it does make sense to do mapping in sql.

MrBliz Over a year ago

Sadly, they aren't. The records come from Oracle, and we do additional processing on the records before they're saved to our database

vittore Over a year ago

If there is a need of regularly sending about a million records and performance is an issue, maybe you should link oracle server to sql server, pull all the data to the sql schema similar to your oracle one and do processing in sql server already. You'll be able to automate it with sql server agent jobs.

Collectives™ on Stack Overflow

SQLBulk Copy With Related Entities (Entity Framework)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related