CODEWITHSUNDEEP
c#.netentity-framework-6
DAustin
DAustin

Reputation: 471

Does IQueryable re-query the database on every access of the object?

I had a bug in my code and eventually managed to track it down to my usage of an IQueryable statement, however the implication of what happened leaves me with a few questions I was hoping someone with more knowledge on Entity Framework could shed some light on so I can improve going forward.

(N.B. This code was originally in a single foreach loop, but I was getting a bug there too, though I'm not sure if its the same one)

IQueryable<SystemOrder> Orders = _ctx.SystemOrder.Where(x => x.ordStatus == 1 && x.rctStatus == 0).OrderBy(x => x.Id);

            Console.WriteLine(Orders.Count());

            AddressLabel l;

            foreach (SystemOrder Order in Orders)
            {
                Order.rctStatus = 1;
            }
            _ctx.SaveChanges();

            foreach (SystemOrder Order in Orders)
            {
                l = new AddressLabel(Order);
                SendOrderConfirmationEmail(Order, l);
                Order.rctStatus = 2;
            }
            _ctx.SaveChanges();

            foreach (SystemOrder Order in Orders)
            {
                l = new AddressLabel(Order);
                CreateOrderFiles(Order, l);
                Order.rctStatus = 3;
            }
            _ctx.SaveChanges();

            foreach (SystemOrder Order in Orders)
            {
                SystemOrder result = PushToTill(Order);
                Order.ordStatus = result.ordStatus;
            }
            _ctx.SaveChanges();

So the issue was that the results only worked in the first foreach loop, and all subsequent loops were ignored as the "Orders" IQueryable returned no results. I was confused until it dawned on me that it might be running the query each time orders was accessed, as it was not in-memory. Pushing the results to a list and working on that avoided the issue.

So if this is the case I have a few questions:

  1. Is my assumption actually correct in that each foreach is requerying the database?
  2. Would this not happen if the code was contained within a single foreach?
  3. In situations like this, what would be the best approach? Using memory or multiple queries?

The reason that I needed separate foreach loops is so I could call SaveChanges() on each status update as you can't call it within the loop. Inefficient but I wasn't sure of the best approach under these circumstances.

Thanks :)

Upvotes: 0

Views: 567

Answers (2)

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

1 - Yes

2 - Yes, single foreach should work

3 - Not clear what is the situation, I think that using single foreach is a good solution

Upvotes: 3

Brian
Brian

Reputation: 25834

In situations like this, what would be the best approach? Using memory or multiple queries?

Use whichever approach results in the most readable code. If having multiple foreach loops makes your code more readable, replace 

IQueryable<SystemOrder> Orders = _ctx.SystemOrder
    .Where(x => x.ordStatus == 1 && x.rctStatus == 0)
    .OrderBy(x => x.Id);

With:

List<SystemOrder> Orders = _ctx.SystemOrder
    .Where(x => x.ordStatus == 1 && x.rctStatus == 0)
    .OrderBy(x => x.Id)
    .ToList();

Calling ToList() hits the database (i.e., runs the query once). Enumerating a List<T> will not hit the database. Contrary to many people's intuition, materializing a query will not break _ctx.SaveChanges(). In the context of change-tracking, materializing a query via ToList() is not especially different from materializing it via foreach.

Upvotes: 2

Related Questions