question

IDGO-6443 avatar image
0 Votes"
IDGO-6443 asked IDGO-6443 commented

Entity Framework dispose context after Load Method

Entity Framework dispose context after Load Method

 this.Context.Recognitions.Where(rec => reportableResultIds.Contains(rec.ReportableResultId)).Load();

I want to Dispose the Context which is Eagerly Loaded on Method End and
During the Begging of the Method need to set the context again.

  public ReportableResultsRepository(
             IMemberUpdater memberUpdater,
             IReportableResultQueryGenerator queryGenerator,
             IDbContextPool contextPool,
             IConfigurationProvider configurationProvider)
         {
             this.memberUpdater = memberUpdater;
             this.queryGenerator = queryGenerator;
             this.contextPool = contextPool;
             this.configurationProvider = configurationProvider;
         }
    
    private IResultRepositoryDbContext Context
         {
             get
             {
                 return this.contextPool.CurrentContext;
             }
         }

   unityContainer.RegisterType<IDbContextPool, DbContextPool>(new HierarchicalLifetimeManager());







sql-server-generaldotnet-csharpdotnet-entity-framework
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

or Unload is also Fine...this.Context.Recognitions.Where(rec => reportableResultIds.Contains(rec.ReportableResultId)).unLoad();

0 Votes 0 ·

The code pattern looks like you are using a singleton which has an application scope. I'm not a Unity expert but the docs seems to confirm a Singleton pattern which is scoped to the application.

Typically scope is handled by the IoC container. I think you need to read the Unity docs and pick a different scope option.

Otherwise, the feature you've described sounds like a using statement if you can't configure Unity to do what you want. At this point, there is just not enough information to understand the general design. But, it does seem very odd that you would load records in memory then dispose the context which would also dispose the records in memory.

0 Votes 0 ·

Yes...I am in a For-loop so every time reportableResultIds changes...Where in I need to unload or dispose the this.Context.Recognitions in Memory.
The problem now it not unloading or Disposing and Memory is going to max.

0 Votes 0 ·

I recommend committing the source code to GitHub so the community members can see what you're doing. What's you've described so far does not make a lot of sense but I could be missing something. Also, describing the general design or the problem this design solves will help understand the intent.

0 Votes 0 ·
 this.Context.Recognitions.Where(rec => reportableResultIds.Contains(rec.ReportableResultId)).Load();


Need to optimize this line of code...Recognitions table has 6500000 Records...

reportableResultIds has 500 Records

0 Votes 0 ·

this.Context.Recognitions.Where(rec => reportableResultIds.Contains(rec.ReportableResultId)).Load();


Please find the SQL Profiler which is causing Memory Issue ...Sure it will be helpful, and let me know what's the fix for this Memory Issue.

https://1drv.ms/u/s!Ag8QU6ar3yRugYxKImSjIXtdWOkhkw?e=MQ7pax

0 Votes 0 ·

1 Answer

AgaveJoe avatar image
0 Votes"
AgaveJoe answered IDGO-6443 commented

Need to optimize this line of code...Recognitions table has 6500000 Records...

I suspect dropping the current design and moving to a standard load process is the best approach. Unfortunately, you continue to post questions as if the community can see your source code and we understand the design intent.

The Contains method generates an IN clause which is ok for small lists. For large lists a join with proper indexes is far more performant.

If the list of reportableResultIds are coming from an external source then load the reportableResultIds in to a table.

If the reportableResultIds are coming from the database that contains the Recognitions table then use standard SQL constructs not LINQ. Keep in mind, if the reportableResultIds are coming from the database that contains the Recognitions table that means the reportableResultIds are copied from SQL to the client application and back to SQL with an IN clause. Plus the resulting SQL generated by LINQ is not optimal. This design wastes bandwidth and clock cycles.

Basically, I'm recommending that you design a SQL solution rather than LINQ.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

 this.Context.Recognitions.Where(rec => reportableResultIds.Contains(rec.ReportableResultId)).Load();


Please find the SQL Profiler which is causing Memory Issue ...Sure it will be helpfull, and let me know whats the alternative for this Where Clause.

https://1drv.ms/u/s!Ag8QU6ar3yRugYxKImSjIXtdWOkhkw?e=MQ7pax

0 Votes 0 ·

199934-untitled.png



Please find the screenshot of the Table

0 Votes 0 ·
untitled.png (266.2 KiB)
                    var mm = this.Context.Recognitions.Where(rec => reportableResultIds.Contains(rec.ReportableResultId)).Select(x => new  {x.ReportableResultId,x.Id,x.RecognitionCode,x.ReportableResult}).ToList().AsQueryable();
                      
                    mm.Load();

Above is Working ,Memory got Reduced after using Above Code.....but ReportableResult table is not Loaded.

   [Table("Recognitions")]
     public class Recognition : WithAuditBase, IDomainObject
     {
         [Key]
         [Column("RecognitionId")]
         public Guid Id { get; set; }
    
         public string RecognitionCode { get; set; }
    
         [ForeignKey("ReportableResultId")]
         public virtual ReportableResult ReportableResult { get; set; }
    
         public Guid ReportableResultId { get; set; }
     }


public virtual ReportableResult ReportableResult { get; set; } this one is not loaded.




0 Votes 0 ·