EF Navigation Property Looping Issue

I currently have a db that looks something like this

Person Table
Id
Name
AddressLookupTypeId


AddressLookupType Table
Id
Description
SortOrder

When i use entity framework 4.0's designer and add the two tables i get a navigation property on both of the classes ie:

Person Class
Id
Name
AddressLookupType - Nav Property


AddressStatusLookup Class
Id
Description
SortOrder
Pers开发者_运维百科on - Navigation Property

Now my person table has around 3+ million records and the address type has only about 50 or so records. When i update the status on any person and call save changes for some reason it takes forever to do an update; if i pause and debug in the middle of code execution i end up in a method called FixupAddressStatusLookup. I don't know what exactly is going on here, but it looks to me like its looping through a subset of Person records that have the same addressstatustype; that's just my best guess though. If i remove the Person navigation property from AddressStatusLookup Class in the designer than the code executes immediately. Can anyone explain what might be going on here? Why does having a navigation property to Person on my AddressStatusLookup cause such delays?

Thanks

Your fixup method looks probably something like this:

private void FixupAddressStatusLookup(AddressStatusLookup previousValue)
{
    if (previousValue != null && previousValue.Persons.Contains(this))
    {
        previousValue.Persons.Remove(this);
    }

    // more ...
}

It is called in the setter of the AddressStatusLookup navigation property in your Person class.

Now, imagine you have lazy loading enabled and you assign a new AddressStatusLookup to a Person entity (the setter is called and therefore the fixup method): previousValue.Persons.Something... causes lazy loading to run a query against the database which loads all persons which have a AddressStatusLookup of value previousValue - which is with your millions of persons and only 50 stati probably a list of thousands or hundred of thousands person records which get loaded before Contains is excuted at all.

I don't know how those autogenerated fixup methods could ever be compatible with lazy loading without causing such problems.

To solve the problem you could try:

• Remove the Person collection from your AddressStatusLookup class (as you already tried with success). It's usefulness is questionable anyway.
• Or: Don't work with lazy loading
• Or: Remove the fixup methods from the generated classes. (Perhaps it's possible to tweak the T4 templates and create a custom template to suppress generating these methods in the first place, but I am not sure.)

(If you don't have lazy loading enabled this answer is probably completely useless.)

This sounds like you are using the T4 POCO templates? If so, what you are seeing is the FixUp code running which fixes up the relationships from one end to the other. It basically doesn't work for non-trivial databases I find, as it traverses the many end of a relationships to ensure the reverse pointers are all set correctly. The best thing you can do is turn off LazyLoading in the context.ContextOptions and load relationships when you need them with Include etc.

EDIT: A little more explanation of what is going on can be found here http://blogs.msdn.com/b/efdesign/archive/2010/03/10/poco-template-code-generation-options.aspx

Also, the ADO.NET SelfTrackingEntity Generator generated objects don't have lazy loading, so also don't do this.

For that particular operation set Context.ContextOptions.LazyLoadingEnabled = false; once done with the save reset it to true.