Entity Framework slowness when there should be speed

Question

I have a problem where adding a child object to a parent object is exceptionally slow when it should not be. There are tens of thousands of child objects (33k records in this case), but none of those are children of the parent object in question.

When I add the first child to the parent it takes more than one minute to complete:

public class ParentEntity // POCO generated by EF TT4 templates
{
    public virtual int Id { get; set; }
    public virtual ICollection<ChildEntity> ChildEntities  {}
} 

public class ChildEntity // POCO generated by EF TT4 templates
{
    public virtual int Id { get; set; }
    public virtual int ParentEntityId { get; set; }
    public virtual ParentEntity ParentEntity { get; set; }
    public virtual Warehouse Warehouse  { get; set; }
    public virtual WarehouseLocation  WarehouseLocation  { get; set; }
}

public class Warehouse { // etc } // another POCO class
public class WarehouseLocation { // etc } // another POCO class

// somewhere in a controller action method...
var parent = _parentEntityService.GetBy(id);
var child = new ChildEntity{ ParentEntityId = id, 
                             WarehouseId = id2, WarehouseLocationId = id3 };

// ChildEntities.Add() takes more than one minute to add the 
// first and only child to this parent
// why would this be so incredibly slow?

parent.ChildEntities.Add(child);

What is the best way to approach finding a speed problem in EntityFramework?

Update: EFProf shows that it issues three SQL queries:

SELECT * FROM ChildEntities where ParentId = id
SELECT * FROM ChildEntities where WarehouseId = id2
SELECT * FROM ChildEntities where WarehouseLocation = id3

Why does it load these for every single ChildEntity, when it should just load them for the current child only?

Edit 2: As per @LadislavMrnka the extra queries are caused by the template's Fixup method. But when I comment out those methods and comment out the call to Fixup it is still slow. Is this not the correct way to remove the fixup (it looks like it is removed to me):

public class ChildEntity {
public virtual Warehouse Warehouse
{
    get { return _warehouse; }
    set
    {
        if (!ReferenceEquals(_warehouse, value))
        {
            var previousValue = _warehouse;
            _warehouse = value;
            //FixupWarehouse(previousValue); // commented out
        }
    }
}

Answer 1

This is your problem:

var child = new ChildEntity 
               { 
                   Foo = "", 
                   Bar = "", 
                   ParentEntityId = id, 
                   WarehouseId = 1, 
                   WarehouseLocationId = 1 
               };

parent.ChildEntities.Add(child);

IMHO it is all about fixup collections hidden in code generated by POCO template. Fixup + lazy loading = performance problems. Fixup tries to make everything in your model in sync. It means that if you set one side of navigation property or FK property it will try to make sure that navigation property on opposite side of relation reflects the change as well. The problem is that if the navigation property is not loaded it will trigger lazy loading. In your case it looks like setting Warehouse has first fixed up the navigation property in ChildEntity and after that tried to fixup navigation property on Warehouse instance but its child entities collection was not loaded => lazy loading causing

SELECT * FROM ChildEntities where WarehouseId = some id

The same happened in case of WarehouseLocation. The first query is result of adding child to not loaded collection on parent entity.

The solution is either to modify template and get rid of all fixups (for example DbContext POCO template for EFv4.1+ don't use fixups anymore) or turn off lazy loading for this operation by calling:

context.ContextOptions.LazyLoadingEnabled = false;

// Your insert logic here

context.ContextOptions.LazyLoadingEnalbed = true;

You can even wrap the code in custom IDisposable like:

public class DisableLazyLoadingScope : IDisposable
{
    private readonly ObjectContext context;

    public DisableLazyLoadingScope(ObjectContext context)
    {
        this.context = context;
        context.ContextOptions.LazyLoadingEnabled = false;
    }

    public void Dispose()
    {
        context.ContextOptions.LazyLoadingEnabled = true;
    }
}

And use it like:

using (new DisableLazyLoadingScope(context)
{
    // Your insert logic here
}

Answer 2

This helped me enormously:

context.Configuration.AutoDetectChangesEnabled = false;

I'm not sure if this goes for you too, but if I understand your question correctly it's slow for you when you just do the Add?

That will still allow you to insert objects, but EF will take much less effort in navigating the object hierarchy. I believe this was problematic when updating entities though, but I don't know for sure.

A second method (assuming you use DbContext) is:

using (var dbCtx = new MyDataContext())
{
  var ctx = ((IObjectContextAdapter)dbCtx).ObjectContext;

  var customers = ctx.CreateObjectSet<Customer>();

  customers.AddObject(customer);
}

Because ObjectContext handles things differently internally with regards to changes. I can't find the source on that right now, I'll try to find it later.