Query running multiple times and is weirdly made

Question

I am using Application Insights on my C# MVC web application. Going into End-to-end transaction details for a long running query.. I am getting that the following query runs over 10 times in a row on a slow page.

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[paid] AS [paid]
    FROM (SELECT 
    [TableXX].[ID] AS [ID], 
    [TableXX].[paid] AS [paid]
    FROM [dbo].[TableXX] AS [TableXX]) AS [Extent1]
    WHERE [Extent1].[ID] = @EntityKeyValue1

I'm not sure how to find it in my application. But also, I don't know why my TableXX is being subqueried with no where clause for the two fields if that makes sense.

Has anyone run into something like this? Could this be due to incorrectly using .Include, or just how EF structures certain type of Linq code smells?

Could this be it?

db.TableParent.Where(li => li.itemID == 16 && li.TableChild.TableXX != null && li.TableChild.companyId == company.id).ToList();

UPDATE:

So I used Database.Log and breakpoints to run my website locally and find the query that is causing the issue.

I create the following and pass it to a function that runs the second query below. Why is the ToList on the first one still letting the second one load them one by one?

List<TableParentItem> currentTableParentItems = db.TableParent.Where(i => i.id == givenId).Include(i => i.TableChild).Include(i => i.TableXX).GroupBy(i => new { i.id1, i.id2}).SelectMany(location => location.OrderByDescending(i => i.order).Take(1)).OrderBy(i => i.StartDate).ThenByDescending(i => i.id2).ToList();

..

result = currentTableParentItems.Where(i => i.Remaining > 0 && i.dueDate <= DateTime.Now).ToList();

Where i.Remaining is a get { return TableXX.remaining; }

.. I'm also confused because when I set a breakpoint before and after the currentTableParentItems var above.. I get multiple queries: 1 into TableXX, 1 into TableParent, and 2 into TableChild... and then 5th one for a random table not referenced on that line..

Answer 1

When debugging EF queries it helps to look at the whole execution as a whole, and run in conjunction with an application debugger. Repeated queries appearing like that are trademark of lazy load calls. With MVC applications this is commonly triggered by the serializer where entities are being sent to the client. (Rather than projected view models)

Without seeing the real code and what queries are being run, using the following example:

db.TableParent.Where(li => li.itemID == 16 && li.TableChild.TableXX != null && li.TableChild.companyId == company.id).ToList();

If the MVC controller in question is returning this "Parents" collection back to the view, the serializer is going to iterate over each parent, see the TableChild reference (along with any other references for children, grandchildren, etc. etc.) and when it touches those properties, if the DbContext has not already resolved them, the references will be lazy loaded one at a time.

The simple solution in this case would be to add an .Include(x => x.TableChild) to the query before the .Where() clause. Long term it would be beneficial to look at sending view models rather than entities to avoid unexpected surprises like this.

Update: GroupBy does not pass through Include expressions in EF. You either need to perform the GroupBy in memory (expensive) or use the GroupBy to identify the IDs of the parents, then load the parents with their eager-loaded relationships. Alternatively if you can project your ViewModels into the query expression, you should be able to compose the view models without eager loading the relationships.

Example 1: Get IDs, then eager load:

List<int> currentTableParentItemIds = db.TableParent
    .Where(i => i.id == givenId)
    .GroupBy(i => new { i.id1, i.id2})
    .SelectMany(location => location
        .OrderByDescending(i => i.order)
        .Take(1))
    .Select(i => i.id) // Assuming this gives you the ID of the records you want.
    .ToList();

List<TableParentItem> currentTableParentItems = db.TableParent
    .Include(i => i.TableChild)
    .Include(i => i.TableXX)
    .Where(i => currentParentItemIds.Contains(i.id))
    .OrderBy(i => i.StartDate)
    .ThenByDescending(i => i.id2)
    .ToList();

The first query performs the group by to find the appropriate row IDs. The second does our full eager load and sorting to get the entities and their relatives.

Example 2: If this loading is to populate a view model then you would be better off removing the eager load Includes and project your view models directly in the query expression using Select or Automapper's ProjectTo. This may be a bit of a re-factor if you use factory methods or such to populate the view models since those won't project through EF to SQL.

List<ParentItemViewModel> currentTableParentItems = db.TableParent
    .Where(i => i.id == givenId)
    .GroupBy(i => new { i.id1, i.id2})
    .SelectMany(location => location
        .OrderByDescending(i => i.order)
        .Take(1))
    .Select(i => new ParentItemViewModel
    {
       id = i.Id,
       ChildName = i.TableChild.Name, // etc. Use whatever related tables needed. No need to eager load.
       // ...
    }).ToList();

The advantage of this approach is that it generates the most optimal SQL. Instead of passing back all parent columns, and all related object columns, the SQL generated by this expression will only return back what is needed to populate the view model. There is no risk of lazy load hits either so long as you don't embed any entities within the view model. (I.e. Child = i.TableChild = bad)

If the code to return the entities is isolated in something like a repository and returns something like IEnumerable<ParentTable> then to adopt optimizations like example 2 can be facilitated by adopting a return type of IQueryable<ParentTable> to allow consumers to refine the EF queries to project their specific needs. Otherwise, option #1 should get you there.