CODEWITHSUNDEEP
c#entity-frameworkasp.net-mvc-5extension-methods
Scuba Steve
Scuba Steve

Reputation: 1648

At what point does Entity Framework lambda extension method send the query to database?

I'm trying to figure out how to optimize some database queries, such that they're using the indexes that are built into our database, and pulling the data being requested quickly.

What I'd like to understand, is, at what point in the following code is the IQueryable translated into SQL, and sent to the database for retrieval. My attempt below is trying to leverage the existing indexes to narrow down the data-set, and then do additional more complex and less indexed operations on the smaller set of data.

My instinct, is that the call doesn't actually go to the database until I call ToListAsync() (thus making my approach below kinda pointless), but I'm not sure.

For context, the below code is a controller action, and is wrapped in some exception handling. Stripped all that out for simplicity's sake.

                var shift_Offers = db.Shift_Offers.Include(c => c.Callout)
                                                    .Where(x => x.Callout.shift_date_new >= today
                                                        && x.employee_id_fk == id
                                                        && x.offer_timestamp != null);

                //do the complex work on the data set once we've gotten the main dataset
                shift_Offers = shift_Offers.Where(x => ((x.Callout.employee_id_fk ?? -1) == id ||
                                                            (x.Callout.employee_id_fk ?? -1) == -1)
                                                        && (x.Callout.status.Contains(CalloutStatus.inprogress)
                                                            || x.Callout.status.Contains(CalloutStatus.inprogressWaitingNext)
                                                            || x.Callout.status.Contains(CalloutStatus.stopped)
                                                            || x.Callout.status.Contains(CalloutStatus.finishedSucceeded)
                                                            || x.Callout.status.Contains(CalloutStatus.finishedFailed)));

                //do work on the shift offer table last, once the data set is smallest
                shift_Offers = shift_Offers.Where(x => !x.offer_status.Contains(ShiftOfferStatus.NotYetOffered));

                Debug.WriteLine(shift_Offers.AsQueryable().ToString());
                List<Shift_Offer> shos = await shift_Offers.ToListAsync();
                return View(shos);

Upvotes: 1

Views: 60

Answers (1)

lisandro101
lisandro101

Reputation: 474

A query is executed against the database when:

  • It is enumerated by a foreach (C#) or For Each (Visual Basic) statement.
  • It is enumerated by a collection operation such as ToArray, ToDictionary, or ToList.
  • LINQ operators such as First or Any are specified in the outermost part of the query.
  • The following methods are called: the Load extension method on a DbSet, DbEntityEntry.Reload, and Database.ExecuteSqlCommand.

In your case is when you call to the ToListAsync() method. As a general rule as long as you work with the IQueryable objects the query won't be executed, once you try to cast it on something else the query will be made.

source: https://learn.microsoft.com/en-us/ef/ef6/querying/

Upvotes: 2

Related Questions