SQLBulk Copy With Related Entities (Entity Framework)

.net c# entity-framework sqlbulkcopy sql-server-2008

Question

Say I possess the following entities. (For the sake of simplicity, the main attributes have been greatly simplified.)

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 need to put more than 600,000 of these entries into SQL Server 2008 Express R2, which uses Entity Framework 4.4 and takes a while.

I would like to utilizeSQLBulkCopy to bulk insert these data, but because that doesn't (at least not out of the box) allow complicated kinds, I'm looking for other feasible solutions.

If required, I'm willing to modify my current model.

EDIT: In this case, would the The MSDN AsDataReader Extension method be useful?

1
1
3/21/2013 11:35:58 AM

Popular Answer

We finally settle on a code-first produced database with EF and strongly typed generated datasets to be utilized for SQLBulkCopy while experiencing the same problem.

(Those classes were produced using xsd util from a 1–10 GB XML file; we never really programmed them.) I'm attempting to remember a time when we didn't produce typed datasets from the same XSD, but it doesn't appear to be relevant to your problem.)

You may either modify the code to utilize automatically produced strongly typed datasets or use an object-to-object mapper to convert your EF POCO objects to datasets as attributes with the same names. Depending on how you obtained those 600k+ records.

Here is a link on creating datasets with strong types.

Here is a quick tutorial on using SqlBulkInsert.

1
3/21/2013 11:24:06 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow