WalkingKindle | Alex
WalkingKindle | Alex
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
Translating linq query into OUTER APPLY or CROSS APPLY
How do i fit 2 of them in one query with IsIncoming being different ?
9 replies
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
Translating linq query into OUTER APPLY or CROSS APPLY
How so?
9 replies
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
Translating linq query into OUTER APPLY or CROSS APPLY
Is there a way to change the linq method to achieve generating outer apply or cross apply?
9 replies
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
Translating linq query into OUTER APPLY or CROSS APPLY
The architecture is not my primary concern here. What I'm trying to achieve is something else
9 replies
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
Translating linq query into OUTER APPLY or CROSS APPLY
Due to performance issue I would like it to generate a CROSS APPLY or OUTER APPLY sql transaction
9 replies
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
Translating linq query into OUTER APPLY or CROSS APPLY
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))
...
9 replies