EF Core query with includes, subqueries, left joins, order by, top 1, database LIKE functions etc.

efCoreQuerySample.cs

/// <summary>
/// Select signing requests for the signing requests page grid.
/// </summary>
/// <param name="currentUserNameCaseIndependent">The user name of the person viewing the page.</param>
/// <param name="context">Db context to use.</param>
/// <param name="titleFilter">Document title filter. May include DB LIKE operators: %, _ and others.</param>
/// <param name="participantFilter">Participant filter. May include DB LIKE operators: %, _ and others. </param>
/// <param name="processStatusFilter">Process status filter.</param>
/// <returns>A query ready to be sorted, grouped and executed.</returns>
public virtual IQueryable<SigningRequestsPageGridDto> GetSigningRequestsPageGridDto(string currentUserNameCaseIndependent, SigningToolContext context, string? titleFilter, string? participantFilter, SigningRequestProcessStatus? processStatusFilter)
{
	var userNameLowerCase = currentUserNameCaseIndependent.ToLower();
	var relevantGroupNamesLowerCase = GetSigningGroups(currentUserNameCaseIndependent).Select(item => item.NameLowerCase).ToList();

	IQueryable<SigningRequest> query = context.SigningRequests
		   .Include(t => t.Title) // left join
		   .Include(t => t.Participants) // left join
		   .Include(t => t.Process); // left join
	query = query.Where(t => t.Discriminator == typeof(SigningRequest).Name);
	query = query.Where(t => t.Participants.Any(
		p => p.NameLowerCase == userNameLowerCase // requests with a particular participant
	|| relevantGroupNamesLowerCase.Contains(p.NameLowerCase) // requests containing a group to which the current user belongs (translates to IN database predicate)
	));

	if (!string.IsNullOrWhiteSpace(titleFilter))
	{
		// Remember - use DB functions for LIKE or else possibly suffer from performance problems.
		// Case sensitivity depends on the vendor implementation
		var titleFilterTmp = $"%{titleFilter}%";
		query = query.Where(item => item.Title != null && (EF.Functions.Like(item.Title.TitleSi, titleFilterTmp) || EF.Functions.Like(item.Title.TitleEn, titleFilterTmp)));
	}
	if (!string.IsNullOrWhiteSpace(participantFilter))
	{
		// Remember - use DB functions for LIKE or else possibly suffer from performance problems.
		// Case sensitivity depends on the vendor implementation
		var participantFilterTmp = $"%{participantFilter}%";
		var participantFilterLowerCase = $"%{participantFilter?.ToLower()}%";
		query = query.Where(item => item.Participants.Any(participant => EF.Functions.Like(participant.DisplayName, participantFilterTmp) || EF.Functions.Like(participant.NameLowerCase, participantFilterLowerCase) || EF.Functions.Like(participant.Email, participantFilterTmp)));
	}
	if (processStatusFilter != null)
		query = query.Where(item => item.Process.Status == processStatusFilter);

	var finalQuery = from requests in query

					 join prepared in (from participant in context.SigningRequestParticipants where participant.ParticipantRole == SigningRequestParticipantRole.Prepared select participant)
					 on requests.Id equals prepared.SigningRequestId into preparedGrouping
					 from prepared in preparedGrouping.DefaultIfEmpty() // left join

					 join latestLog in (from latestLogLine in context.SigningRequestProcessLogs
										join logLinesWithMaxWhenGroupedByProcessStateId in // inner join
										(
											from logs in context.SigningRequestProcessLogs
											group logs by logs.SigningRequestProcessStateId into logsGroup
											select new
											{
												When = logsGroup.Max(item => item.When),
												SigningRequestProcessStateId = logsGroup.Key
											}
										)
										on new { latestLogLine.SigningRequestProcessStateId, latestLogLine.When }
										equals new 
										{ 
											logLinesWithMaxWhenGroupedByProcessStateId.SigningRequestProcessStateId,                                      logLinesWithMaxWhenGroupedByProcessStateId.When
										}
										select latestLogLine
					 )
					 on requests.Process.Id equals latestLog.SigningRequestProcessStateId into latestLogGrouping
					 from latestLog in latestLogGrouping.DefaultIfEmpty() // left join

					 select new SigningRequestsPageGridDto
					 {
						 Id = requests.Id,
						 PreparedDisplayName = prepared.DisplayName,
						 PreparedNameLowerCase = prepared.NameLowerCase,
						 Deadline = requests.Deadline,
						 ProcessStatus = requests.Process.Status,
						 LatestLogWhen = latestLog.When,
						 LatestLogEventOrigin = latestLog.EventOrigin,
						 LatestLogProcessTrigger = latestLog.ProcessTrigger,
						 LatestLogByWhoUserNameLowerCase = latestLog.ByWhoUserNameLowerCase,
						 TitleSi = requests.Title!.TitleSi,
						 TitleEn = requests.Title!.TitleEn,
						 TemplateName = requests.Template!.TemplateName
					 };
	return finalQuery;
}

/*
Produces the following SQLite SQL optimized for the current user context:

SELECT "s"."Id", "t"."DisplayName" AS "PreparedDisplayName", "t"."NameLowerCase" AS "PreparedNameLowerCase", "s"."Deadline", "s1"."Status" AS "ProcessStatus", "t1"."When" AS "LatestLogWhen", "t1"."EventOrigin" AS "LatestLogEventOrigin", "t1"."ProcessTrigger" AS "LatestLogProcessTrigger", "t1"."ByWhoUserNameLowerCase" AS "LatestLogByWhoUserNameLowerCase", "s4"."TitleSi", "s4"."TitleEn", "t2"."TemplateName"
FROM "SigningRequests" AS "s"
LEFT JOIN (
    SELECT "s0"."DisplayName", "s0"."NameLowerCase", "s0"."SigningRequestId"
    FROM "SigningRequestParticipants" AS "s0"
    WHERE "s0"."ParticipantRole" = 1
) AS "t" ON "s"."Id" = "t"."SigningRequestId"
LEFT JOIN "SigningRequestProcessState" AS "s1" ON "s"."Id" = "s1"."SigningRequestId"
LEFT JOIN (
    SELECT "s2"."ByWhoUserNameLowerCase", "s2"."EventOrigin", "s2"."ProcessTrigger", "s2"."SigningRequestProcessStateId", "s2"."When"
    FROM "SigningRequestProcessLogs" AS "s2"
    INNER JOIN (
        SELECT MAX("s3"."When") AS "c", "s3"."SigningRequestProcessStateId"
        FROM "SigningRequestProcessLogs" AS "s3"
        GROUP BY "s3"."SigningRequestProcessStateId"
    ) AS "t0" ON ("s2"."SigningRequestProcessStateId" = "t0"."SigningRequestProcessStateId") AND ("s2"."When" = "t0"."c")
) AS "t1" ON "s1"."Id" = "t1"."SigningRequestProcessStateId"
LEFT JOIN "SigningRequestTitles" AS "s4" ON "s"."Id" = "s4"."SigningRequestId"
LEFT JOIN (
    SELECT "s5"."Id", "s5"."TemplateName"
    FROM "SigningRequests" AS "s5"
    WHERE "s5"."Discriminator" = 'SigningRequestTemplate'
) AS "t2" ON "s"."TemplateId" = "t2"."Id"
WHERE ("s"."Discriminator" = @__Name_0) AND EXISTS (
    SELECT 1
    FROM "SigningRequestParticipants" AS "s6"
    WHERE ("s"."Id" = "s6"."SigningRequestId") AND (("s6"."NameLowerCase" = @__userNameLowerCase_1) OR ("s6"."NameLowerCase" = 'oe097')))
*/