Entity Framework runs through all records (60,000!) when updating property for one

Question

I am attempting to set the value of a single property for a POCO object, and it's looking like Entity is doing a retrieve of every record in the database table from which the POCO object was originally retrieved. Here's the code:

public void DeleteFile(int fileid)
{
    var context = GetContext(myTrans, false);
    FILE pocofile = (from f in context.FILES.All()
                     where f.File_Id == fileId
                     select f).FirstOrDefault();

    // the following line causes a retrieve of 60,000 records
    pocofile.State_Id = Global.DeletedStateId // global constant

    // additional code that is eventually reached after about 10 minutes
}

We have a FILES table that has a State_Id column that is mapped to a STATE table. So when I attempt to set the State_Id property above, it seems to set the first file ok, but judging by the breakpoints it's hitting in the FILE poco class, it looks like it's doing a retrieve for every file in the db once it sets the State_Id from the outset.

The FILE.cs class is also pasted below for reference. It's basically hitting a lot of the getters and setters in a 60,000 long loop, and if I set a breakpoint on one of them at any given time and check the File_Id in the debugger, it's got a new File_Id every time, which is how I know it's looping through all of them.

//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;    

namespace FORTRESSPOCO
{
    public partial class FILE
    {
        #region Primitive Properties

        public virtual int File_Id
        {
            get;
            set;
        }

        public virtual string Name
        {
            get;
            set;
        }

        public virtual string Description
        {
            get;
            set;
        }

        public virtual Nullable<System.DateTime> Expiration_Date
        {
            get;
            set;
        }

        public virtual bool Is_Directory
        {
            get;
            set;
        }

        public virtual string Path
        {
            get;
            set;
        }

        public virtual int Data_Asset_Id
        {
            get { return _data_Asset_Id; }
            set
            {
                if (_data_Asset_Id != value)
                {
                    if (Data_Asset != null && Data_Asset.Data_Asset_ID != value)
                    {
                        Data_Asset = null;
                    }
                    _data_Asset_Id = value;
                }
            }
        }
        private int _data_Asset_Id;

        public virtual int State_Id
        {
            get { return _state_Id; }
            set
            {
                if (_state_Id != value)
                {
                    if (STATE != null && STATE.State_Id != value)
                    {
                        STATE = null;
                    }
                    _state_Id = value;
                }
            }
        }
        private int _state_Id;

        #endregion
        #region Navigation Properties

        public virtual Data_Asset Data_Asset
        {
            get { return _data_Asset; }
            set
            {
                if (!ReferenceEquals(_data_Asset, value))
                {
                    var previousValue = _data_Asset;
                    _data_Asset = value;
                    FixupData_Asset(previousValue);
                }
            }
        }
        private Data_Asset _data_Asset;

        public virtual ICollection<File_DateTime_Attribute> File_DateTime_Attribute
        {
            get
            {
                if (_file_DateTime_Attribute == null)
                {
                    var newCollection = new FixupCollection<File_DateTime_Attribute>();
                    newCollection.CollectionChanged += FixupFile_DateTime_Attribute;
                    _file_DateTime_Attribute = newCollection;
                }
                return _file_DateTime_Attribute;
            }
            set
            {
                if (!ReferenceEquals(_file_DateTime_Attribute, value))
                {
                    var previousValue = _file_DateTime_Attribute as FixupCollection<File_DateTime_Attribute>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupFile_DateTime_Attribute;
                    }
                    _file_DateTime_Attribute = value;
                    var newValue = value as FixupCollection<File_DateTime_Attribute>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupFile_DateTime_Attribute;
                    }
                }
            }
        }
        private ICollection<File_DateTime_Attribute> _file_DateTime_Attribute;

        public virtual ICollection<File_Int_Attribute> File_Int_Attribute
        {
            get
            {
                if (_file_Int_Attribute == null)
                {
                    var newCollection = new FixupCollection<File_Int_Attribute>();
                    newCollection.CollectionChanged += FixupFile_Int_Attribute;
                    _file_Int_Attribute = newCollection;
                }
                return _file_Int_Attribute;
            }
            set
            {
                if (!ReferenceEquals(_file_Int_Attribute, value))
                {
                    var previousValue = _file_Int_Attribute as FixupCollection<File_Int_Attribute>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupFile_Int_Attribute;
                    }
                    _file_Int_Attribute = value;
                    var newValue = value as FixupCollection<File_Int_Attribute>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupFile_Int_Attribute;
                    }
                }
            }
        }
        private ICollection<File_Int_Attribute> _file_Int_Attribute;

        public virtual ICollection<File_String_Attribute> File_String_Attribute
        {
            get
            {
                if (_file_String_Attribute == null)
                {
                    var newCollection = new FixupCollection<File_String_Attribute>();
                    newCollection.CollectionChanged += FixupFile_String_Attribute;
                    _file_String_Attribute = newCollection;
                }
                return _file_String_Attribute;
            }
            set
            {
                if (!ReferenceEquals(_file_String_Attribute, value))
                {
                    var previousValue = _file_String_Attribute as FixupCollection<File_String_Attribute>;
                    if (previousValue != null)
                    {
                        previousValue.CollectionChanged -= FixupFile_String_Attribute;
                    }
                    _file_String_Attribute = value;
                    var newValue = value as FixupCollection<File_String_Attribute>;
                    if (newValue != null)
                    {
                        newValue.CollectionChanged += FixupFile_String_Attribute;
                    }
                }
            }
        }
        private ICollection<File_String_Attribute> _file_String_Attribute;

        public virtual STATE STATE
        {
            get { return _sTATE; }
            set
            {
                if (!ReferenceEquals(_sTATE, value))
                {
                    var previousValue = _sTATE;
                    _sTATE = value;
                    FixupSTATE(previousValue);
                }
            }
        }
        private STATE _sTATE;

        #endregion
        #region Association Fixup

        private void FixupData_Asset(Data_Asset previousValue)
        {
            if (previousValue != null && previousValue.FILES.Contains(this))
            {
                previousValue.FILES.Remove(this);
            }

            if (Data_Asset != null)
            {
                if (!Data_Asset.FILES.Contains(this))
                {
                    Data_Asset.FILES.Add(this);
                }
                if (Data_Asset_Id != Data_Asset.Data_Asset_ID)
                {
                    Data_Asset_Id = Data_Asset.Data_Asset_ID;
                }
            }
        }

        private void FixupSTATE(STATE previousValue)
        {
            if (previousValue != null && previousValue.FILES.Contains(this))
            {
                previousValue.FILES.Remove(this);
            }

            if (STATE != null)
            {
                if (!STATE.FILES.Contains(this))
                {
                    STATE.FILES.Add(this);
                }
                if (State_Id != STATE.State_Id)
                {
                    State_Id = STATE.State_Id;
                }
            }
        }

        private void FixupFile_DateTime_Attribute(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (File_DateTime_Attribute item in e.NewItems)
                {
                    item.FILE = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (File_DateTime_Attribute item in e.OldItems)
                {
                    if (ReferenceEquals(item.FILE, this))
                    {
                        item.FILE = null;
                    }
                }
            }
        }

        private void FixupFile_Int_Attribute(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (File_Int_Attribute item in e.NewItems)
                {
                    item.FILE = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (File_Int_Attribute item in e.OldItems)
                {
                    if (ReferenceEquals(item.FILE, this))
                    {
                        item.FILE = null;
                    }
                }
            }
        }

        private void FixupFile_String_Attribute(object sender, NotifyCollectionChangedEventArgs e)
        {
            if (e.NewItems != null)
            {
                foreach (File_String_Attribute item in e.NewItems)
                {
                    item.FILE = this;
                }
            }

            if (e.OldItems != null)
            {
                foreach (File_String_Attribute item in e.OldItems)
                {
                    if (ReferenceEquals(item.FILE, this))
                    {
                        item.FILE = null;
                    }
                }
            }
        }

        #endregion
    }
}

Is there any logical reason for this behavior? Thanks.

Answer 1

Yes, there is a logical reason for this behaviour. But it's not your fault in my opinion but the EF 4.0 POCO T4 template's fault. The problem is the combination of those autogenerated Fixup... methods together with lazy loading.

The following happens:

• You set the FK property:

  pocofile.State_Id = Global.DeletedStateId;
  

• It calls the State_Id setter:

  if (_state_Id != value)
  {
      if (STATE != null && STATE.State_Id != value)
      {
          STATE = null;
      }
      _state_Id = value;
  }
  

• The first if condition is true unless you set the same FK value that the loaded entity has already (no change of FK). The second if condition will be true because lazy loading will load the STATE from the database in the expression STATE != null and because the STATE in the database cannot be null since your relationship is not nullable (State_Id is an int). STATE.State_Id cannot be equal to value if _state_Id is not equal to value, it would violate the FK constraint in the DB. So, STATE = null is executed. In other words the setter of the navigation property STATE is called:

  if (!ReferenceEquals(_sTATE, value))
  {
      var previousValue = _sTATE;
      _sTATE = value;
      FixupSTATE(previousValue);
  }
  

• The if condition is true again because _STATE is not null (it just has been loaded from the DB before) and value is null. So, FixupSTATE will be called with a parameter previousValue that is not null:

  if (previousValue != null && previousValue.FILES.Contains(this))
      //...
  

• Now, if lazy loading is enabled (and it is by default) accessing the previousValue.FILES collection will cause lazy loading to kick in and issue a database query the loads the whole previousValue.FILES collection from the database - which seem to contain 60000 entities in your case.

The solution for this problem is to disable lazy loading:

var context = GetContext(myTrans, false);
context.ContextOptions.LazyLoadingEnabled = false;

//...

Or, modify the T4 template so that it doesn't create the Fixup code anymore (might be difficult to get it right). Or, maybe there already is a modified T4 template for EF 4.0 out there you can use. Or, upgrade to EF >= 4.1 and DbContext because the POCO template for DbContext doesn't have this additional code. The generated POCO classes are much simpler then.