C# Entity Framework 4 Navigation properties causing slow performance on commit

Question

I apologize for the lack of detail in this question - the first thing I need help on is knowing where to look to find more detail.

I have a problem with enity framework 4 navigation properties apparently causing poor performance when committing changes:

this.ObjectContext.SaveChanges();

Is taking 30+ seconds when one of the navigation properties (Receipts table) contains around 8000 rows (which is not many, so should be fine).

I have used the SQL profiler and can see that EF issues a select * from Receipts and that it is very slow:

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
// full field list cut for brevity 
FROM [dbo].[Receipts] AS [Extent1]
WHERE [Extent1].[WarehouseId] = @EntityKeyValue1',
N'@EntityKeyValue1 int',@EntityKeyValue1=1

At the moment I can't even see why it needs to select all rows from this table when ObjectContext.SaveChanges() is called.

It does need to insert 1 row into this table, but that doesn't explain why it does a select all first - and doesn't explain why that select takes so long (the same query takes < 1 second in query manager)

So my question right now - I don't exactly know what the problem is yet - is:

• Where/how can I look for more details about the problem? I can't debug into ObjectContext.SaveChanges(), so I don't know whats happening inside it.
• Why would EF try to select * from Receipts?
• Why is it so slow? The exact same query copied + pasted into query manager is nearly instant

EDIT:

I have confirmed that it is the receipt code that is slow by commenting out the call to this method:

    private void AddReceipt(PurchaseInvoice invoice, 
                               PurchaseInvoiceLine invoiceLine)
    {
        if (invoice != null && invoiceLine != null)
        {
            Product product = invoiceLine.Product;
            if (product != null)
            {
                Receipt receipt = new Receipt{ foo = bar };
                WarehouseDetail detail = new WarehouseDetail{ foo = bar };
                receipt.WarehouseDetails.Add(detail);
                invoice.Receipts.Add(receipt);
            }
        }
    }

But I still cannot see why this causes EF to issue that select * query.

I believe that it might be a lazy loading issue caused by invoice.Receipts.Add(receipt). Because before that line invoice.Receipts is empty, and in order to .Add to the Receipts, it must first load the collection. BUT that does not explain why it is selecting by warehouseId=1, when it should be selecting by the invoiceId.

EDIT 2:

I have "fixed" the problem by replacing the EF code in this method with direct SQL commands. This is not a great idea - I should not be throwing SQL around when I've got an otherwise perfectly good ORM. But right now I still do not understand why EF was running the select * query

    private void AddReceipt(PurchaseInvoice invoice, 
                               PurchaseInvoiceLine invoiceLine)
    {
        if (invoice != null && invoiceLine != null)
        {
            Product product = invoiceLine.Product;
            if (product != null)
            {
                Receipt receipt = new Receipt{ foo = bar };
                WarehouseDetail detail = new WarehouseDetail{ foo = bar };
                int id = SqlHelper.AddWarehouseDetail(detail);
                receipt.WarehouseDetailId = id;
                SqlHelper.AddReceipt(receipt);
            }
        }
    }

Answer 1

Since this is an insert, it refreshes your object, by selecting the value back and repopulating the object. Now let me answer your questions that you laid out:

1. You shouldn't need to debug instead of SaveChanges(), what you see probably wouldn't make much sense anyways.

2. It is not actually doing a select * from Receipts. It is doing a select * from Receipts where WarehouseId = 1. So for some reasons you object is pulling all the Receipts for the Warehouse with the Id of 1.

3. This could depend on so many things, that you really can't get into it now. But one place to start is to check the ping rate between your app box and your db box. Also check that the RAM isn't full on the db box. That is where I would start, and that is the usual problem for what you are describing.

A good tool to debug EF is the EF Profiler. http://efprof.com This will help you alot more than SQL profiler will.

Answer 2

You issue is with the "Navigation Property" on your "Warehouse" entity. Remove this navigation property. The relationship will still be there, but it will not query all receipts with that warehouse anymore when you create a receipt entity. I had the same issue and this solved my issue.