none
Time Out on Delete - System.OutOfMemoryException RRS feed

  • Question

  • Hello,

    I have a table with 8000 records. I am using EF and deleting an item as follows:

    IRepository<Job> rep = new Repository<Job>(_context);
    rep.Delete(j => j.Id == 50);
    _context.Save();
    

    The Delete method on the repository is the following:

        public void Delete(Func<T, Boolean> predicate) {
          IEnumerable<T> objects = from o in _set.Where<T>(predicate) select o;
          foreach (T o in objects)
            _set.DeleteObject(o);
        } // Delete
    

    I get the error "System.OutOfMemoryException"  ...

    When I debug I get the message "Items = Function evaluation timed out." on getting the objects.

    This problem started when the number of records started to increase.

    I am able to delete the item without any problem using TSQL in SSMS.

    What am I doing wrong?

    Thank You,

    Miguel

    Tuesday, November 8, 2011 2:18 PM

Answers

  • ah ok.. i didnt get that ID = 50 is a unique index.. thought you are deleting more than one objects.. So i dont understand the issue as well.

    if you are deleting by primary key you should add following delete(int id) method:

     

            public virtual void Delete(long id)
            {
                var entity = _dbset.Find(id);
                if (entity != null)
                {
                    _dbset.Remove(entity);
                }
            }
    

     


    I am not seeing why your way shouldn't work, but i ll show you my implementation of your method:

     

            public void Delete(Expression<Func<TEntity, bool>> predicate)
            {
                IEnumerable<TEntity> objects = _dbset.Where(predicate).AsEnumerable();
                foreach (TEntity obj in objects)
                {
                    _dbset.Remove(obj);
                }
            }
    
    

     


    Hope that helps. For understanding .. in my generic repository i define the DbSet like this:

    _dbset = DataContext.Set<TEntity>();

    So i dont need to use Where<T> as you did here.

     

    Greets

    Holger

     

     

    • Marked as answer by MDMoura Wednesday, November 9, 2011 10:26 PM
    Tuesday, November 8, 2011 2:52 PM
  • I think I found the solution.

    And it seems a lot of generic repositories I find online have this problem.

    If you use the following the search will be evaluated in memory:

        public T First(Func<T, Boolean> predicate) {
          return _set.FirstOrDefault<T>(predicate);
        } // First
    


    That is not a problem if the records are few and without filestream.

    It becomes a problem on all the other cases. So here is something that seems to work:

    Note: when I say work I mean the query is done on the database

        public T First(<strong>Expression</strong><Func<T, Boolean>> predicate) {
          return _set.FirstOrDefault<T>(predicate);
        } // First
    

    So all methods should be replaced this way.

    Let me know what do you think.

    Thank You,

    Miguel

    • Marked as answer by MDMoura Wednesday, November 9, 2011 6:11 PM
    Wednesday, November 9, 2011 5:09 PM

All replies

  • Hi,

    i think the problem is, that you are trying to load a huge number of records into memory. This is done when you delete objects in the way that you have described here.

    For this scenario I suggest to execute an pure sql command. Because there is no need to load all that entities into memory. And its much much faster too..

    Create a custom DeleteJob(int id) method in your repository and do something like this...

    DataContext.Database.ExecuteSqlCommand("DELETE FROM JOB WHERE Id={0}", 50);

    HTH

    Holger

    Tuesday, November 8, 2011 2:28 PM
  • But when I use:

    IEnumerable<T> objects = from o in _set.Where<T>(predicate) select o;

    But here I am selecting the items that satisfy a certain criteria.

    When I use ID = 50 I would get only one item.

    Then I delete that item. Wouldn't this be the same?

     

     

    Tuesday, November 8, 2011 2:32 PM
  • ah ok.. i didnt get that ID = 50 is a unique index.. thought you are deleting more than one objects.. So i dont understand the issue as well.

    if you are deleting by primary key you should add following delete(int id) method:

     

            public virtual void Delete(long id)
            {
                var entity = _dbset.Find(id);
                if (entity != null)
                {
                    _dbset.Remove(entity);
                }
            }
    

     


    I am not seeing why your way shouldn't work, but i ll show you my implementation of your method:

     

            public void Delete(Expression<Func<TEntity, bool>> predicate)
            {
                IEnumerable<TEntity> objects = _dbset.Where(predicate).AsEnumerable();
                foreach (TEntity obj in objects)
                {
                    _dbset.Remove(obj);
                }
            }
    
    

     


    Hope that helps. For understanding .. in my generic repository i define the DbSet like this:

    _dbset = DataContext.Set<TEntity>();

    So i dont need to use Where<T> as you did here.

     

    Greets

    Holger

     

     

    • Marked as answer by MDMoura Wednesday, November 9, 2011 10:26 PM
    Tuesday, November 8, 2011 2:52 PM
  • I am not sure what is going on ... I keep having the time out problem.

    I have the following generic repository:

      public class Repository<T> : IRepository<T> where T : class {
    
        private Context _context;
        private IObjectSet<T> _set;
    
        public Repository(IContext context) {
    
          _context = (Context)context;      
          _set = _context.CreateObjectSet<T>();
    
        } // Repository
    
        public T FirstOrDefault(Func<T, Boolean> predicate) {
    
          var a = _context.Jobs.FirstOrDefault(j => j.Id == 51);
    
          var b = _set.FirstOrDefault<T>(predicate);
          
          return _set.FirstOrDefault<T>(predicate);
    
        }
      }
    

     


    Then I tried the following:

     

    IContext context = new Context();
    IRepository<Job> rep = new Repository<Job>(context);
    Job job = rep.FirstOrDefault(j => j.Id == 51);
    
    

    I added breakpoints inside the FirstOrDefault method in the generic repository.

    "a" works fine and I get the correct job. "b" gives a timeout.

    Any idea what might be going on?

     



     


    • Edited by MDMoura Tuesday, November 8, 2011 4:01 PM
    Tuesday, November 8, 2011 3:59 PM
  • Well.. strange behaviour :)

    Could you try to use

     _set = _context.Set<T>();

    instead of

    _set = _context.CreateObjectSet<T>();

    and see if the timeout still happens?

    --

    Holger



    Tuesday, November 8, 2011 4:08 PM
  • Well.. strange behaviour :)

    Could you try to use

     _set = _context.Set<T>();

    instead of

    _set = _context.CreateObjectSet<T>();

    and see if the timeout still happens?

    I did try it after your previous post.

    But context does not have a set option ...

    Isn't "_set = _context.Set<T>();" just for DBContext?

    But in this project, context is an ObjectContext from an EDMX file.

    Correct me if I am wrong ...

    Tuesday, November 8, 2011 4:20 PM
  • sorry ditnt thought about that. you are right.. This only works with DbContext..

    Maybe you can upload a demo program. Or someone else has an idea. I would be interested too in knowing why this happens..

    Have a nice day..

    Holger

    Tuesday, November 8, 2011 8:31 PM
  • This is really strange ... I did just the following:

     

      Context context = new Context();
    
      Func<Job, Boolean> predicat = (j => j.Id == 2);
    
      var q1 = context.Jobs.FirstOrDefault(j => j.Id == 2);
    
      var q2 = context.Jobs.FirstOrDefault(predicat);
    

     


    In a table with 800 records and no filestream column both "q1" and "q2" work fine.

    In a table with 4000 records and filestream column "q1" works fine and "q2" I get:

    Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

     

    Note: I am not even using _context.CreateObjectSet<T>();

              I am using only the 4 code lines I just posted.

    Go figure ... Any ideas?

    Thank You,

    Miguel

    Tuesday, November 8, 2011 11:55 PM
  • Hello,

    I have been doing more tests. Here it goes:

          Context context = new Context();
    
          Func<Job, Boolean> predicate = j => j.Id == 55;
    
          var q0 = from j in context.Jobs
                   where j.Id == 55
                   select j;
    
          var q1 = context.Jobs.Where(j => j.Id == 55);
    
          var q2 = context.Jobs.Where(j => j.Id == 55).FirstOrDefault();
    
          var q3 = context.Jobs.FirstOrDefault(j => j.Id == 55);
    
          var q4 = context.Jobs.Where(predicate);
    
          var q5 = context.Jobs.FirstOrDefault(predicate);
    


    Q0:  It works fine. I get the correct job.

    Q1:  It works fine. I get the correct job. I analyzed the generated SQL code and I got:

    SELECT
      [Extent1].[Id] AS [Id],
      [Extent1].[Client] AS [Client],
      [Extent1].[Key] AS [Key],
      [Extent1].[Plan] AS [Plan],
      [Extent1].[ProjectId] AS [ProjectId],
    FROM [dbo].[Jobs] AS [Extent1]
    WHERE 55 = [Extent1].[Id]
    

            If I expand the Results View when debugging I get only one item. The one with Id = 55.


    Q2:  It works fine. I get the correct job.

    Q3:  It works fine. I get the correct job.

    Q4:  I get a "System.Linq.Enumerable.WhereEnumerableIterator<Job>

            When debugging if I expand the Results View I get a Time Out.

    Q5: I get a time out ...

     

    Note 1: Context is just the class generated by the .edmx file.

                 I am not using repositories or anything else.

    Note 2: If I use a table with a few records all Queries work fine.

     

    Does anyone has any idea what might be wrong or what other tests should I do?

    This is a real problem ...

     

    Is there any other way to use generics without a predicate?

    Thank You,

    Miguel

     

    Wednesday, November 9, 2011 2:25 PM
  • Two extra tests:

          var c1 = context.Jobs.Count(j => j.Id == 55);
    
          var c2 = context.Jobs.Count(predicate);
    

    C1: Return 1 as expected

    C2: Time out (System.OutOfMemoryException) ... Same problem again when using a predicate.

     

    For me it seems the predicate is somehow enumerating everything ...

    When the SQL table is big (with many records and filestream) somehow the problem comes up ...

     

    Anyone?

     

    Wednesday, November 9, 2011 2:30 PM
  • When I get the Out of Memory message the error is:

    ----------------System.OutOfMemoryException was unhandled by user code
      Message=Exception of type 'System.OutOfMemoryException' was thrown.
      Source=System.Data
      StackTrace:
           at System.Data.SqlTypes.SqlBinary.get_Value()
           at System.Data.SqlClient.SqlBuffer.get_ByteArray()
           at System.Data.SqlClient.SqlBuffer.get_Value()
           at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
           at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
           at lambda_method(Closure , Shaper )
           at System.Data.Common.Internal.Materialization.Shaper.HandleEntityAppendOnly[TEntity](Func`2 constructEntityDelegate, EntityKey entityKey, EntitySet entitySet)
           at lambda_method(Closure , Shaper )
           at System.Data.Common.Internal.Materialization.Coordinator`1.ReadNextElement(Shaper shaper)
           at System.Data.Common.Internal.Materialization.Shaper`1.SimpleEnumerator.MoveNext()
           at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source, Func`2 predicate)
           at Predicate() in Testing.cs:line 283
      InnerException:

    ----------------

     

    It seems the problem is with Byte Array.

    A lot of records in a table that contains FileStream columns.

     

    What is strange is why this happens when using the Predicate and not happens when not using it.

    Any idea how to overcome this?

    thank you,

    Miguel

     

    Wednesday, November 9, 2011 2:37 PM
  • I think I found the solution.

    And it seems a lot of generic repositories I find online have this problem.

    If you use the following the search will be evaluated in memory:

        public T First(Func<T, Boolean> predicate) {
          return _set.FirstOrDefault<T>(predicate);
        } // First
    


    That is not a problem if the records are few and without filestream.

    It becomes a problem on all the other cases. So here is something that seems to work:

    Note: when I say work I mean the query is done on the database

        public T First(<strong>Expression</strong><Func<T, Boolean>> predicate) {
          return _set.FirstOrDefault<T>(predicate);
        } // First
    

    So all methods should be replaced this way.

    Let me know what do you think.

    Thank You,

    Miguel

    • Marked as answer by MDMoura Wednesday, November 9, 2011 6:11 PM
    Wednesday, November 9, 2011 5:09 PM
  • Uhm... isnt it that what I posted in my second answer? ;(

     

    public void Delete(Expression<Func<TEntity, bool>> predicate)

    But anyways.. Good you found it by yourself now.

    cheers

    Holger

    Wednesday, November 9, 2011 10:24 PM
  • Yes, it is!

    I was focused comparing the content of the method and dbSet, eg:

                IEnumerable<TEntity> objects = _dbset.Where(predicate).AsEnumerable();
                foreach (TEntity obj in objects)
                {
                    _dbset.Remove(obj);
                }
    


    that I didn't notice you had a different parameter. Sorry!

    I am sure in the future I will remember this post. :-)

    Just marked your post as an answer!

    Thank You,

    Miguel

    Wednesday, November 9, 2011 10:31 PM