Entity Framework selectively save tracked entities from context and bulk insert others

bulkinsert c# entity-framework sqlbulkcopy

Question

I'm fairly new to Entity Framework. I've come across a scenario where I have my entities:

public class Company{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<Project> Projects{ get; set; }
}

public class Project
{
    public int Id { get; set; }
    public List<ProjectRecord> ProjectRecords { get; set; }
    public DateTime ProjectDate { get; set; }
}

public class ProjectRecord
{
    public int Id { get; set; }
    public virtual Project Project{ get; set; }
    public string Status { get; set; }
}

I create my context, load the Employer then pass the employer object to another class which adds a Project to my employer entity, and then I load approximately 300,000 project records from a file and add this list of project records to the project.

Entity Framework is tracking all this, and although it isn't particularly fast it isn't too bad.

The slow part is when I call SaveChanges() on the context. I wanted to be able to detach the list of project Records from my context and save the Project without them, and then use the bulk insert extension provided from https://efbulkinsert.codeplex.com/ to bulk insert the ProjectRecords seperately. However I could not find a way to detach ProjectRecords.

I therefore tried bulk inserting the entire Project entity:

        context.BulkInsert(context,
            context.ChangeTracker.Entries()
                .Where(x => x.State == EntityState.Added)
                .Select(x => x.Entity)
                .OfType<Project>()
                .ToList());

        context.SaveChanges();

However, this seems to take a long time to do nothing to the database. I therefore think I'm going about it completely wrong.

I don't have the context available when I add the Project to the Employer entity and I don't want to make it available if I can avoid it, as I'm trying to keep them separate.

Is there any way to improve performance, use the bulk insert extension or sql bulk copy from where I'm calling SaveChanges on my context?

Any help is greatly appreciated.

Accepted Answer

A suggestion would be to not attach the ProjectRecords to the context in the first place when loading them from if that's possible. You could use something like a

Dictionary<Project, List<ProjectRecord>>

to hold them and to help in retrieving the Project and setting ProjectRecord.Project when you save.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why