Entity Framework dispose context after Load Method

Question

question

IDGO-6443 asked May 5, '22 IDGO-6443 commented May 7, '22

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-general dotnet-csharp dotnet-entity-framework

· 6

5 |1600 characters needed characters left characters exceeded

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

IDGO-6443 · May 05 at 02:29 PM

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

0 ·

AgaveJoe · May 05 at 02:37 PM

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 ·

IDGO-6443 AgaveJoe · May 05 at 03:02 PM

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 ·

AgaveJoe IDGO-6443 · May 05 at 03:13 PM

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 ·

IDGO-6443 · May 07 at 10:27 AM

 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 ·

IDGO-6443 IDGO-6443 · May 07 at 07:01 PM

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 ·

Answer 1 · 2022-05-07T11:56:31.287Z

AgaveJoe answered May 7, '22 IDGO-6443 commented May 7, '22

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

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

IDGO-6443 · May 07 at 06:58 PM

 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 ·

IDGO-6443 IDGO-6443 · May 07 at 07:10 PM

Please find the screenshot of the Table

0 ·

untitled.png (266.2 KiB)

IDGO-6443 IDGO-6443 · May 07 at 08:37 PM

                    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 ·

question