Translating linq query into OUTER APPLY or CROSS APPLY
Hi, consider the following query
Generates the followinng LEFT JOIN:
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();
5 Replies
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))
...
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.
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?
the architecture is causing the bad query 🙂
How so?
How do i fit 2 of them in one query with IsIncoming being different ?