Use of using statement for Entity Framework DbContext in foreach-loop

Question

There is a parser that parses a text file which contains object definition. The object definitions in the text file have a placeholder handle key. The place holder handle needs to be replaced with actual value by looking up the handle value in DB. In my application I am making use of the Entity framework Core for working with the DB.

The parser returns one object at a time, and I am looking up the handle and other properties in the DB one at a time. This is how the code looks so far:

    IEnumerable<ObjectInfo> GetNextContent();

    IEnumerable<ObjectInfo> GetNextObjectInfo()
    {

        foreach (var item in parser.GetNextContent())
        {
            using (var dbContext = new ContentDbContext())
            {
                string key = item.Key;

                string id = dbContext.Contents.Find(key).ObjectId;
                item.Id = id;
                // Assign other fields...

                yield return item;
            }
        }
    }

The question that I have is that in the code above, the 'using' block is within the foreach loop. Is this a right thing to do? The other thought is that I can take the 'using' block outside of the foreach-loop but then I am not sure how would that play out with the iterator in the code.

You can do it, the question is whether you should. You are always better to do things in batches when you can — TheGeneral
– TheGeneral, Commented Feb 18, 2020 at 4:12
Right, this is precisely my question. I am concerned am I causing too many dispose() calls when using the DbContext within the loop. — tyrion
– tyrion, Commented Feb 18, 2020 at 4:17
Putting the context outside for an extended period of time can create its own set of issues. My point was mainly, that you are better off to get a list of Ids, and query the database in one go or in batches (if possible), or if the list isn't too large, load them all into memory. it all really depends on information you haven't supplied — TheGeneral
– TheGeneral, Commented Feb 18, 2020 at 4:20
@Michael Randall The same my idea. Check my answer to be more details. — Nguyễn Văn Phong
– Nguyễn Văn Phong, Commented Feb 18, 2020 at 4:25
@MichaelRandall Yes, that makes sense. I can perform this in batch as well, thanks for the pointer. As for number of times, the loop might run, the count can vary from 10K to 20K.Doing this now within the for-each loop looks bad idea. — tyrion
– tyrion, Commented Feb 18, 2020 at 4:33

Ramil Aliyev 007 · Accepted Answer · 2020-02-18 05:46:05Z

3

You should move ContentDbContext into outside for better performance.

This is simply because You just need one context per request.

One DbContext per web request... why?

using (var dbContext = new ContentDbContext())
 {
    foreach (var item in parser.GetNextContent())
     {
            string key = item.Key;

            string id = dbContext.Contents.Find(key).ObjectId;
            item.Id = id;
            // Assign other fields...

            yield return item;
        }
    }

Updated

You might also join then make sure that fetch all data at a time

// You need to fetch all `item.Key` from `parser.GetNextContent()` to get all data in `dbContext.Contents`
var keys = parser.GetNextContent().Select(p => p.Key).ToArray();

var result = (from content in dbContext.Contents 
              join key in keys on content.Id equals key 
              select new 
{
  Id = content.ObjectId,
  //....
}

If you are use C# 8, using statement may be as below:

 using var dbContext = new ContentDbContext();

 foreach (var item in parser.GetNextContent())
 {
        string key = item.Key;

        string id = dbContext.Contents.Find(key).ObjectId;
        item.Id = id;
        // Assign other fields...

        yield return item;
  }

edited Feb 18, 2020 at 5:46

Ramil Aliyev 007

5,6102 gold badges38 silver badges59 bronze badges

answered Feb 18, 2020 at 4:12

Nguyễn Văn Phong

14.2k19 gold badges47 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

tyrion Over a year ago

This LINQ query looks very interesting. Based on comments from @MichaelRandall I realized I would need to batch the items coming from parser and do a single lookup in DB. Does the above query doesn't need that anymore?

TheGeneral Over a year ago

Note in the last example parser.GetNextContent() will need to be enumerated first. ToList() as it will likely throw an error because it cant be converted to sql

Fabio Over a year ago

because You just need one context per request. - context is just a c# class as others, instantiating it multiple times will not cause more problem than instantiating some other disposable instance.

Nguyễn Văn Phong Over a year ago

@Michael Randall I've just updated my answer, please take a look at

tyrion Over a year ago

@Phong Thanks for the updated answer. I will make use of the 2nd solution.

|

Collectives™ on Stack Overflow

Use of using statement for Entity Framework DbContext in foreach-loop

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related