Pfthb

Using .Net Core 2.1 and EF Core 2.1.4

I'm trying to grab all the nested records of an object if any of those records contains a name that I have in a list, and only objects that have matching nested records.

I have a query that is producing the right results, but it's performing a query for every single record in the database table.

I'm hoping to reduce it further, if possible.

My ViewModels (actual models are nearly identical):

EventViewModel

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace MyProject.Models.ViewModels

 public class EventViewModel
 
 private string _name;

 public Guid Id get; set; 

 [Required, StringLength(100)]
 public string Name
 
 get => _name?.Trim();
 set => _name = value?.Trim();
 

 [DataType(DataType.Date)]
 public DateTime Date get; set; 

 [Display(Name = "Programs")]
 public IEnumerable<EventProgramViewModel> EventProgramViewModels get; set; 

 public string AllProgramNames
 
 get
 
 string result = EventProgramViewModels.Aggregate(string.Empty,
 (current, program) => current + $"program?.ProgramViewModel?.Name, ");
 return result.TrimEnd(',', ' ');
 
 

 public EventViewModel()
 
 Id = Guid.NewGuid();
 Date = DateTime.Now;
 EventProgramViewModels = new List<EventProgramViewModel>();
 
 

EventProgramViewModel

using System;

namespace MyProject.Models.ViewModels

 public class EventProgramViewModel
 
 public Guid EventViewModelId get; set; 
 public EventViewModel EventViewModel get; set; 

 public Guid ProgramViewModelId get; set; 
 public ProgramViewModel ProgramViewModel get; set; 
 

ProgramViewModel

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace MyProject.Models.ViewModels

 public class ProgramViewModel
 
 private string _name;

 public Guid Id get; set; 

 [Required, StringLength(100)]
 [Display(Name = "Program Name")]
 public string Name
 
 get => _name?.Trim();
 set => _name = value?.Trim();
 
 

In my ApplicationDbContext.cs file I have this mapped like so:

modelBuilder.Entity<EventProgram>()
 .HasKey(eventProgram => new eventProgram.EventId, eventProgram.ProgramId);

Finally, here is my query:

var testQuery = context.Events
 .AsNoTracking()
 .Select(e => new EventViewModel
 
 Id = e.Id,
 Name = e.Name,
 Date = e.Date,
 EventProgramViewModels = e.EventPrograms.Select(eventProgram =>
 new EventProgramViewModel
 
 ProgramViewModel = new ProgramViewModel
 
 Name = eventProgram.Program.Name
 
 )
 )
 .OrderByDescending(eventViewModel => eventViewModel.Date)
 .ThenBy(eventViewModel => eventViewModel.Name)
 .Where(eventViewModel =>
 !search.ProgramsChosen.Any() || eventViewModel.EventProgramViewModels.Any(
 eventProgramViewModel =>
 search.ProgramsChosen.Contains(eventProgramViewModel.ProgramViewModel
 .Name)))
 .ToList()
 ;

foreach (var item in testQuery)

 // Mock usage to show where queries are generated
 _loggingServices.LogInformation(JsonConvert.SerializeObject(item));

This produces one EF query for the main Event properties:

SELECT [e].[Id], [e].[Name], [e].[Date]
FROM [Events] AS [e]
ORDER BY [e].[Date] DESC, [e].[Name]

And 1 for each result (in this case, in the above foreach loop)

SELECT [eventProgram.Program].[Name]
FROM [EventPrograms] AS [eventProgram]
INNER JOIN [Programs] AS [eventProgram.Program] ON [eventProgram].[ProgramId] = [eventProgram.Program].[Id]
WHERE @_outer_Id = [eventProgram].[EventId]

Is there any way I can improve this performance?

Update 1:

Based on TyCobb's comment I changed the query structure to:

var testQuery =
 context.Events
 .AsNoTracking()
 .OrderByDescending(@event => @event.Date)
 .ThenBy(@event => @event.Name)
 .Where(@event =>
 !search.ProgramsChosen.Any() || @event.EventPrograms.Any(
 eventProgramViewModel =>
 search.ProgramsChosen.Contains(eventProgramViewModel.Program
 .Name)))
 .Select(e => new EventViewModel
 
 Id = e.Id,
 Name = e.Name,
 Date = e.Date,
 EventProgramViewModels = e.EventPrograms.Select(eventProgram =>
 new EventProgramViewModel
 
 ProgramViewModel = new ProgramViewModel
 
 Name = eventProgram.Program.Name
 
 )
 )
 ;

Which is better, as it only produces a query for each valid record, where before it would do every record regardless.

This now produces this one query:

SELECT [event].[Id], [event].[Name], [event].[Date]
FROM [Events] AS [event]
WHERE EXISTS (
 SELECT 1
 FROM [EventPrograms] AS [eventProgramViewModel]
 INNER JOIN [Programs] AS [eventProgramViewModel.Program] ON [eventProgramViewModel].[ProgramId] = [eventProgramViewModel.Program].[Id]
 WHERE [eventProgramViewModel.Program].[Name] IN (N'A SEARCHED PROGRAM NAME') AND ([event].[Id] = [eventProgramViewModel].[EventId]))
ORDER BY [event].[Date] DESC, [event].[Name]

And one for each valid record:

SELECT [eventProgram.Program].[Name]
FROM [EventPrograms] AS [eventProgram]
INNER JOIN [Programs] AS [eventProgram.Program] ON [eventProgram].[ProgramId] = [eventProgram.Program].[Id]
WHERE @_outer_Id = [eventProgram].[EventId]

There are two issues.

The first (which you solved with the Update 1) is caused by the current EF Core query translation defect which causes client evaluation of the Where clause (the OrderBy is ok). So moving the filtering before the projection is the current way to workaround it.

The second is the so called N + 1 subquery issue. EF Core 2.1 contains Optimization of correlated subqueries, which works in your case, but as mentioned in the documentation, you are expected to opt-in for it by adding ToList (or ToArray):

We have improved our query translation to avoid executing "N + 1" SQL queries in many common scenarios in which the usage of a navigation property in the projection leads to joining data from the root query with data from a correlated subquery. The optimization requires buffering the results from the subquery, and we require that you modify the query to opt-in the new behavior.

and then

By including ToList() in the right place, you indicate that buffering is appropriate for the Orders, which enable the optimization

So the final query should be something like this:

var testQuery = context.Events
 //.AsNoTracking() <-- No need when using projection
 .OrderByDescending(@event => @event.Date)
 .ThenBy(@event => @event.Name)
 .Where(@event =>
 !search.ProgramsChosen.Any() || @event.EventPrograms.Any(
 eventProgram =>
 search.ProgramsChosen.Contains(eventProgram.Program
 .Name)))
 .Select(e => new EventViewModel
 
 Id = e.Id,
 Name = e.Name,
 Date = e.Date,
 EventProgramViewModels = e.EventPrograms.Select(eventProgram =>
 new EventProgramViewModel
 
 ProgramViewModel = new ProgramViewModel
 
 Name = eventProgram.Program.Name
 
 ).ToList() // <-- 
 )
 .ToList()
 ;

which will result in 2 SQL queries:

SELECT [event].[Id], [event].[Name], [event].[Date]
FROM [Events] AS [event]
WHERE EXISTS (
 SELECT 1
 FROM [EventPrograms] AS [eventProgramViewModel]
 INNER JOIN [Programs] AS [eventProgramViewModel.Program] ON [eventProgramViewModel].[ProgramId] = [eventProgramViewModel.Program].[Id]
 WHERE [eventProgramViewModel.Program].[Name] IN (N'P2', N'P4', N'P7') AND ([event].[Id] = [eventProgramViewModel].[EventId]))
ORDER BY [event].[Date] DESC, [event].[Name], [event].[Id]

and

SELECT [t].[Date], [t].[Name], [t].[Id], [eventProgram.Program].[Name] AS [Name0], [event.EventPrograms].[EventId]
FROM [EventPrograms] AS [event.EventPrograms]
INNER JOIN [Programs] AS [eventProgram.Program] ON [event.EventPrograms].[ProgramId] = [eventProgram.Program].[Id]
INNER JOIN (
 SELECT [event0].[Date], [event0].[Name], [event0].[Id]
 FROM [Events] AS [event0]
 WHERE EXISTS (
 SELECT 1
 FROM [EventPrograms] AS [eventProgramViewModel0]
 INNER JOIN [Programs] AS [eventProgramViewModel.Program0] ON [eventProgramViewModel0].[ProgramId] = [eventProgramViewModel.Program0].[Id]
 WHERE [eventProgramViewModel.Program0].[Name] IN (N'P2', N'P4', N'P7') AND ([event0].[Id] = [eventProgramViewModel0].[EventId]))
) AS [t] ON [event.EventPrograms].[EventId] = [t].[Id]
ORDER BY [t].[Date] DESC, [t].[Name], [t].[Id]