WalkingKindle | Alex
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();
9 replies