title: How do I create a T-SQL Cross-Apply with Entity Framework Core?
body: <p>I want to create a query with Entity Framework Core which has multiple INNER JOINs and one CROSS APPLY. </p>
  
  <p>I can create the INNER JOINs just fine but can't seem to make the CROSS APPLY work. </p>
  
  <ul>
  <li>Did some Google searches bus nothing really useful came up</li>
  <li>Tried different thing with the Entity Framework linq statements but so far to no avail. </li>
  </ul>
  
  <p>This is the EF query I got so far:</p>
  
  <pre class="lang-cs prettyprint-override"><code>comCommunicationContext
      .Communication
      .AsNoTracking()
      .Where(c =&gt; c.Label.InternalName == labelName &amp;&amp; 
                  c.Businesstransaction.DocumentType.DocumentTypeName == documentType &amp;&amp; 
                  c.Businesstransaction.SourceSystem.SourceSystemName == sourceSystem)
      .Join(comCommunicationContext.CommunicationOutputChannel,  communicationEntity =&gt; communicationEntity.CommunicationId, communicationOutputChannelEntity =&gt; communicationOutputChannelEntity.CommunicationId, (communicationEntity, communicationOutputChannelEntity) =&gt; new {Communication = communicationEntity, CommunicationOutputChannel = communicationOutputChannelEntity})
      .Where(y =&gt; y.CommunicationOutputChannel.OutputChannel == outputChannel)
      .Join(comCommunicationContext.CommunicationStatusOutputChannel.
  Where(x =&gt; x.Status=="to process"), communicationOutputChannelEntity =&gt; communicationOutputChannelEntity.CommunicationOutputChannel.CommunicationOutputChannelId, communicationStatusOutputChannelEntity =&gt; communicationStatusOutputChannelEntity.CommunicationOutputChannelId, (outer, inner) =&gt; new Communication() { CommunicationDataEnriched = outer.Communication.CommunicationDataEnriched })
  </code></pre>
  
  <p>Which produces the following T-SQL query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT [c].[CommunicationDataEnriched]
  FROM [Communications] AS [c]
  INNER JOIN [Businesstransactions] AS [c.Businesstransaction] ON [c].[BusinessTransactionId] = [c.Businesstransaction].[BusinessTransactionId]
  INNER JOIN [DocumentTypes] AS [c.Businesstransaction.DocumentType] ON [c.Businesstransaction].[DocumentTypeId] = [c.Businesstransaction.DocumentType].[DocumentTypeId]
  INNER JOIN [SourceSystems] AS [c.Businesstransaction.SourceSystem] ON [c.Businesstransaction].[SourceSystemId] = [c.Businesstransaction.SourceSystem].[SourceSystemId]
  INNER JOIN [Labels] AS [c.Label] ON [c].[LabelId] = [c.Label].[LabelId]
  INNER JOIN [CommunicationOutputChannels] AS [CommunicationOutputChannels] ON [c].[CommunicationId] = [CommunicationOutputChannels].[CommunicationId]
  INNER JOIN (
      SELECT [x].*
      FROM [CommunicationStatusOutputChannels] AS [x]
      WHERE [x].[Status] = 'to process'
  ) AS [t] ON [CommunicationOutputChannels].[CommunicationOutputChannelId] = [t].[CommunicationOutputChannelId]
  WHERE ((([c.Label].[InternalName] = 'labelname') AND ([c.Businesstransaction.DocumentType].[DocumentTypeName] = 'documenttype')) AND ([c.Businesstransaction.SourceSystem].[SourceSystemName] = 'sourcessysem')) AND ([CommunicationOutputChannels].[OutputChannel] = 'email')
  </code></pre>
  
  <p>What I would like to send to the database is this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT [c].[CommunicationDataEnriched]
  FROM [Communications] AS [c]
  INNER JOIN [Businesstransactions] AS [c.Businesstransaction] ON [c].[BusinessTransactionId] = [c.Businesstransaction].[BusinessTransactionId]
  INNER JOIN [DocumentTypes] AS [c.Businesstransaction.DocumentType] ON [c.Businesstransaction].[DocumentTypeId] = [c.Businesstransaction.DocumentType].[DocumentTypeId]
  INNER JOIN [SourceSystems] AS [c.Businesstransaction.SourceSystem] ON [c.Businesstransaction].[SourceSystemId] = [c.Businesstransaction.SourceSystem].[SourceSystemId]
  INNER JOIN [Labels] AS [c.Label] ON [c].[LabelId] = [c.Label].[LabelId]
  INNER JOIN [CommunicationOutputChannels] AS [CommunicationOutputChannels] ON [c].[CommunicationId] = [CommunicationOutputChannels].[CommunicationId]
  CROSS APPLY (
      SELECT TOP 1 [x].*
      FROM [CommunicationStatusOutputChannels] AS [x]
      WHERE [x].[Status] = 'to process'
      AND [x].[CommunicationOutputChannelId] = [CommunicationOutputChannels].[CommunicationOutputChannelId]
      ORDER BY [x].createdOn DESC
  ) [t]
  WHERE ((([c.Label].[InternalName] = 'labelname') AND ([c.Businesstransaction.DocumentType].[DocumentTypeName] = 'documenttype')) AND ([c.Businesstransaction.SourceSystem].[SourceSystemName] = 'sourcessysem')) AND ([CommunicationOutputChannels].[OutputChannel] = 'email')
  </code></pre>
  
  <p>The object <code>CommunicationOutputChannel</code> has a navigation property <code>CommunicationStatusOutputChannels</code> of type <code>ICollection&lt;CommunicationStatusOutputChannel&gt;</code> (there's a one-to-many relationn between <code>CommunicationOutputChannel</code> and <code>CommunicationStatusOutputChannels</code>  entities).</p>
  
  <p>So the last <code>INNER JOIN</code> between tables <code>CommunicationOutputChannels</code> and <code>CommunicationStatusOutputChannels</code> should be a <code>CROSS APPLY</code>.</p>