WalkingKindle | Alex
WalkingKindle | Alex
CC#
Created by WalkingKindle | Alex on 9/19/2024 in #help
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:
9 replies