Translating linq query into OUTER APPLY or CROSS APPLY

Hi, consider the following query
c#
 return await context.TextMessages.IgnoreQueryFilters().Where(x => x.TenantId == tenantId && x.CreationDate >= startDate && x.CreationDate <= endDate && !x.IsIncoming)
     .Select(x => new TextMessagesForLaunchLeads
     {
         MessageSentByProspect = new TextMessageForLaunchLeadsInfo
         {
             CreationDate = x.CreationDate,
             BatchId = x.BatchId,
             MessageContent = x.Content,
             ProspectId = x.ProspectId
         },
         MessageReply = context.TextMessages.IgnoreQueryFilters().Where(y => y.IsIncoming && y.TenantId == tenantId && y.ProspectId == x.ProspectId)
         .Select(x => new TextMessageForLaunchLeadsInfo
         {
             CreationDate = x.CreationDate,
             BatchId = x.BatchId,
             MessageContent = x.Content,
             ProspectId = x.ProspectId
         }).OrderBy(x => x.CreationDate).FirstOrDefault(),
         ProspectInfoForLaunchLeadsDto = context.ProspectAdditions.IgnoreQueryFilters().Where(z => z.ProspectId == x.ProspectId && z.TenantId == tenantId)
         .Select(m => new ProspectInfoForLaunchLeadsDto
         {
             LeadStatus = m.LeadStatus,
             PushedToCrm = m.PushedToCRM,
             ProspectId = x.ProspectId,
             CampaignId = m.CampaignId,
             CampaignName = context.Campaigns.Where(p => p.Id == m.CampaignId).Select(o => o.Title).FirstOrDefault(),
             DatePushedToCRM = m.PushedToCRM == true ? context.Activities.IgnoreQueryFilters().Where(l => x.ProspectId == l.ProspectId && l.Detail == "Pushed to CRM").Select(u => u.CreationTime).FirstOrDefault() : null
         }).FirstOrDefault()
     }).Where(x => x.MessageReply.MessageContent != null).ToListAsync();
c#
 return await context.TextMessages.IgnoreQueryFilters().Where(x => x.TenantId == tenantId && x.CreationDate >= startDate && x.CreationDate <= endDate && !x.IsIncoming)
     .Select(x => new TextMessagesForLaunchLeads
     {
         MessageSentByProspect = new TextMessageForLaunchLeadsInfo
         {
             CreationDate = x.CreationDate,
             BatchId = x.BatchId,
             MessageContent = x.Content,
             ProspectId = x.ProspectId
         },
         MessageReply = context.TextMessages.IgnoreQueryFilters().Where(y => y.IsIncoming && y.TenantId == tenantId && y.ProspectId == x.ProspectId)
         .Select(x => new TextMessageForLaunchLeadsInfo
         {
             CreationDate = x.CreationDate,
             BatchId = x.BatchId,
             MessageContent = x.Content,
             ProspectId = x.ProspectId
         }).OrderBy(x => x.CreationDate).FirstOrDefault(),
         ProspectInfoForLaunchLeadsDto = context.ProspectAdditions.IgnoreQueryFilters().Where(z => z.ProspectId == x.ProspectId && z.TenantId == tenantId)
         .Select(m => new ProspectInfoForLaunchLeadsDto
         {
             LeadStatus = m.LeadStatus,
             PushedToCrm = m.PushedToCRM,
             ProspectId = x.ProspectId,
             CampaignId = m.CampaignId,
             CampaignName = context.Campaigns.Where(p => p.Id == m.CampaignId).Select(o => o.Title).FirstOrDefault(),
             DatePushedToCRM = m.PushedToCRM == true ? context.Activities.IgnoreQueryFilters().Where(l => x.ProspectId == l.ProspectId && l.Detail == "Pushed to CRM").Select(u => u.CreationTime).FirstOrDefault() : null
         }).FirstOrDefault()
     }).Where(x => x.MessageReply.MessageContent != null).ToListAsync();
Generates the followinng LEFT JOIN:
5 Replies
WalkingKindle | Alex
SELECT [t].[CreationDate], [t].[BatchId], [t].[Content], [t].[ProspectId], [t1].[CreationDate], [t1].[BatchId], [t1].[MessageContent], [t1].[ProspectId], [t1].[c], [t4].[LeadStatus], [t4].[PushedToCrm], [t4].[ProspectId], [t4].[CampaignId], [t4].[CampaignName], [t4].[DatePushedToCRM], [t4].[c]
FROM [TextMessages] AS [t]
LEFT JOIN (
    SELECT [t2].[CreationDate], [t2].[BatchId], [t2].[MessageContent], [t2].[ProspectId], [t2].[c]
    FROM (
        SELECT [t3].[CreationDate], [t3].[BatchId], [t3].[Content] AS [MessageContent], [t3].[ProspectId], 1 AS [c], ROW_NUMBER() OVER(PARTITION BY [t3].[ProspectId] ORDER BY [t3].[CreationDate]) AS [row]
        FROM [TextMessages] AS [t3]
        WHERE [t3].[IsIncoming] = CAST(1 AS bit) AND [t3].[TenantId] = @__tenantId_0
    ) AS [t2]
    WHERE [t2].[row] <= 1
) AS [t1] ON [t].[ProspectId] = [t1].[ProspectId]
OUTER APPLY (
    SELECT TOP(1) [p].[LeadStatus], [p].[PushedToCRM] AS [PushedToCrm], [t].[ProspectId], [p].[CampaignId], (
        SELECT TOP(1) [c].[Title]
        FROM [Campaigns] AS [c]
        WHERE [c].[Id] = [p].[CampaignId]) AS [CampaignName], CASE
        WHEN [p].[PushedToCRM] = CAST(1 AS bit) THEN COALESCE((
            SELECT TOP(1) [a].[CreationTime]
            FROM [Activities] AS [a]
            WHERE [t].[ProspectId] = [a].[ProspectId] AND [a].[Detail] = N'Pushed to CRM'), '0001-01-01T00:00:00.0000000Z')
        ELSE NULL
    END AS [DatePushedToCRM], 1 AS [c]
    FROM [ProspectAdditions] AS [p]
    WHERE [p].[ProspectId] = [t].[ProspectId] AND [p].[TenantId] = @__tenantId_0
) AS [t4]
WHERE [t].[TenantId] = @__tenantId_0 AND [t].[CreationDate] >= @__startDate_1 AND [t].[CreationDate] <= @__endDate_2 AND [t].[IsIncoming] = CAST(0 AS bit) AND (
    SELECT TOP(1) [t0].[Content]
    FROM [TextMessages] AS [t0]
    WHERE [t0].[IsIncoming] = CAST(1 AS bit) AND [t0].[TenantId] = @__tenantId_0 AND ([t0].[ProspectId] = [t].[ProspectId] OR ([t0].[ProspectId] IS NULL AND [t].[ProspectId] IS NULL))
...
SELECT [t].[CreationDate], [t].[BatchId], [t].[Content], [t].[ProspectId], [t1].[CreationDate], [t1].[BatchId], [t1].[MessageContent], [t1].[ProspectId], [t1].[c], [t4].[LeadStatus], [t4].[PushedToCrm], [t4].[ProspectId], [t4].[CampaignId], [t4].[CampaignName], [t4].[DatePushedToCRM], [t4].[c]
FROM [TextMessages] AS [t]
LEFT JOIN (
    SELECT [t2].[CreationDate], [t2].[BatchId], [t2].[MessageContent], [t2].[ProspectId], [t2].[c]
    FROM (
        SELECT [t3].[CreationDate], [t3].[BatchId], [t3].[Content] AS [MessageContent], [t3].[ProspectId], 1 AS [c], ROW_NUMBER() OVER(PARTITION BY [t3].[ProspectId] ORDER BY [t3].[CreationDate]) AS [row]
        FROM [TextMessages] AS [t3]
        WHERE [t3].[IsIncoming] = CAST(1 AS bit) AND [t3].[TenantId] = @__tenantId_0
    ) AS [t2]
    WHERE [t2].[row] <= 1
) AS [t1] ON [t].[ProspectId] = [t1].[ProspectId]
OUTER APPLY (
    SELECT TOP(1) [p].[LeadStatus], [p].[PushedToCRM] AS [PushedToCrm], [t].[ProspectId], [p].[CampaignId], (
        SELECT TOP(1) [c].[Title]
        FROM [Campaigns] AS [c]
        WHERE [c].[Id] = [p].[CampaignId]) AS [CampaignName], CASE
        WHEN [p].[PushedToCRM] = CAST(1 AS bit) THEN COALESCE((
            SELECT TOP(1) [a].[CreationTime]
            FROM [Activities] AS [a]
            WHERE [t].[ProspectId] = [a].[ProspectId] AND [a].[Detail] = N'Pushed to CRM'), '0001-01-01T00:00:00.0000000Z')
        ELSE NULL
    END AS [DatePushedToCRM], 1 AS [c]
    FROM [ProspectAdditions] AS [p]
    WHERE [p].[ProspectId] = [t].[ProspectId] AND [p].[TenantId] = @__tenantId_0
) AS [t4]
WHERE [t].[TenantId] = @__tenantId_0 AND [t].[CreationDate] >= @__startDate_1 AND [t].[CreationDate] <= @__endDate_2 AND [t].[IsIncoming] = CAST(0 AS bit) AND (
    SELECT TOP(1) [t0].[Content]
    FROM [TextMessages] AS [t0]
    WHERE [t0].[IsIncoming] = CAST(1 AS bit) AND [t0].[TenantId] = @__tenantId_0 AND ([t0].[ProspectId] = [t].[ProspectId] OR ([t0].[ProspectId] IS NULL AND [t].[ProspectId] IS NULL))
...
Due to performance issue I would like it to generate a CROSS APPLY or OUTER APPLY sql transaction
Patrick
Patrick2mo ago
I don't think this is architectured right. You're performing sub queries that are unrelated in one main query. The text messages should all be one query. Replies being stitched together is a frontend concern. The prospect info doesn't even seem to be related.
WalkingKindle | Alex
The architecture is not my primary concern here. What I'm trying to achieve is something else Is there a way to change the linq method to achieve generating outer apply or cross apply?
Patrick
Patrick2mo ago
the architecture is causing the bad query 🙂
WalkingKindle | Alex
How so? How do i fit 2 of them in one query with IsIncoming being different ?
Want results from more Discord servers?
Add your server