title: Linq: Join vs Include/ThenInclude in Entity Framework Core
body: <p>I am working on a project with SQL Server and EF Core v3.</p>
  
  <p>I have 4 tables related to each other. Here are my tables schemes:</p>
  
  <p><a href="https://i.stack.imgur.com/IO205.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IO205.jpg" alt="enter image description here"></a></p>
  
  <p>I wrote 2 Linq queries against those tables - one of them using join like this:</p>
  
  <pre><code>var result = (from emailTemplate in _context.EmailTemplates.Include(et =&gt; et.EmailTemplateContents)
                join priorityLookup in _context.Lookups on new { GroupKey = "PRIORITIES", DetailKey = emailTemplate.Priority, emailTemplate.CompanyId } equals new { priorityLookup.GroupKey, priorityLookup.DetailKey, priorityLookup.CompanyId }
                join statusLookup in _context.Lookups on new { GroupKey = "STATUSES", DetailKey = emailTemplate.StatusCode, emailTemplate.CompanyId } equals new { statusLookup.GroupKey, statusLookup.DetailKey, statusLookup.CompanyId }
                join priorityLookupLabel in _context.LookupLabels on new { Locale = 1033, priorityLookup.DetailKey, priorityLookup.CompanyId } equals new { priorityLookupLabel.Locale, priorityLookupLabel.DetailKey, priorityLookupLabel.CompanyId }
                join statusLookupLabel in _context.LookupLabels on new { Locale = 1033, statusLookup.DetailKey, statusLookup.CompanyId } equals new { statusLookupLabel.Locale, statusLookupLabel.DetailKey, statusLookupLabel.CompanyId }
                where emailTemplate.CompanyId == 3
                select new EmailTemplateModel
                        {
                            Code = emailTemplate.Code,
                            TemplateName = emailTemplate.TemplateName,
                            FromEmail = emailTemplate.FromEmail,
                            BccEmail = emailTemplate.BccEmail,
                            CcEmail = emailTemplate.CcEmail,
                            PriorityCode = emailTemplate.Priority,
                            Priority = priorityLookupLabel.Label,
                            Subject = emailTemplate.EmailTemplateContents.Subject,
                            Body = HttpUtility.HtmlDecode(emailTemplate.EmailTemplateContents.Body),
                            StatusCode = emailTemplate.StatusCode,
                            Status = statusLookupLabel.Label,
                            ToEmail = emailTemplate.ToEmail,
                            TriggerSqlCommand = emailTemplate.TriggerSqlCommand,
                            TriggerType = emailTemplate.TriggerType,
                            ModifDate = emailTemplate.ModifDate
                        }).ToList();
  </code></pre>
  
  <p>and one of them using <code>.Include</code> like so :</p>
  
  <pre><code>var results = _context.EmailTemplates
                        .Where(e =&gt; e.CompanyId == 3)
                        .Include(e =&gt; e.EmailTemplateContents)
                        .Include(e =&gt; e.Lookups)
                          .ThenInclude(g =&gt; g.LookupLabels)
                        .Include(e =&gt; e.LookupsNavigation)
                          .ThenInclude(g =&gt; g.LookupLabels)
                        .Select(e =&gt; new EmailTemplateModel
                                     {
                                         Code = e.Code,
                                         TemplateName = e.TemplateName,
                                         FromEmail = e.FromEmail,
                                         BccEmail = e.BccEmail,
                                         CcEmail = e.CcEmail,
                                         PriorityCode = e.Priority,
                                         PriorityLabel = e.Lookups.LookupLabels.FirstOrDefault(l =&gt; l.Locale == 1033),
                                         Subject = e.EmailTemplateContents.Subject,
                                         Body = HttpUtility.HtmlDecode(e.EmailTemplateContents.Body),
                                         StatusCode = e.StatusCode,
                                         StatusLabel = e.LookupsNavigation.LookupLabels.FirstOrDefault(l =&gt; l.Locale == 1033),
                                         ToEmail = e.ToEmail,
                                         TriggerSqlCommand = e.TriggerSqlCommand,
                                         TriggerType = e.TriggerType,
                                         ModifDate = e.ModifDate
                                     }).ToList();
  </code></pre>
  
  <p>I tried to understand if there is any performance difference between these two type, so I checked the query generated by EF using profiler.</p>
  
  <p>The SQL Script generated from the Join statement is : </p>
  
  <pre><code>SELECT 
      [e].[Code], [e].[TemplateName], [e].[FromEmail], [e].[BccEmail], 
      [e].[CcEmail], [e].[Priority], [l1].[Label], 
      [e0].[Subject], [e0].[Body], [e].[StatusCode], [l2].[Label], 
      [e].[ToEmail], [e].[TriggerSqlCommand], [e].[TriggerType], 
      [e].[ModifDate]
  FROM [EmailTemplates] AS [e]
  INNER JOIN [Lookups] AS [l] ON (('PRIORITIES' = [l].[GroupKey]) AND ([e].[Priority] = [l].[DetailKey])) AND ([e].[CompanyId] = [l].[CompanyId])
  INNER JOIN [Lookups] AS [l0] ON (('STATUSES' = [l0].[GroupKey]) AND ([e].[StatusCode] = [l0].[DetailKey])) AND ([e].[CompanyId] = [l0].[CompanyId])
  INNER JOIN [LookupLabels] AS [l1] ON ((1033 = [l1].[Locale]) AND ([l].[DetailKey] = [l1].[DetailKey])) AND ([l].[CompanyId] = [l1].[CompanyId])
  INNER JOIN [LookupLabels] AS [l2] ON ((1033 = [l2].[Locale]) AND ([l0].[DetailKey] = [l2].[DetailKey])) AND ([l0].[CompanyId] = [l2].[CompanyId])
  LEFT JOIN [EmailTemplateContents] AS [e0] ON ([e].[CompanyId] = [e0].[CompanyId]) AND ([e].[Code] = [e0].[EmailTemplateCode])
  WHERE [e].[CompanyId] = 3
  </code></pre>
  
  <p>The SQL script generated from the <code>.Include</code> statement is : </p>
  
  <pre><code>SELECT 
      [e].[Code], [e].[TemplateName], [e].[FromEmail], [e].[BccEmail], 
      [e].[CcEmail], [e].[Priority], [t0].[DetailKey], [t0].[CompanyId], 
      [t0].[Locale], [t0].[Label], [t0].[OrderNo], [e0].[Subject], [e0].[Body], 
      [e].[StatusCode], [t2].[DetailKey], [t2].[CompanyId], [t2].[Locale], 
      [t2].[Label], [t2].[OrderNo], [e].[ToEmail], [e].[TriggerSqlCommand], 
      [e].[TriggerType], [e].[ModifDate]
  FROM [EmailTemplates] AS [e]
  INNER JOIN [Lookups] AS [l] ON ([e].[Priority] = [l].[DetailKey]) AND ([e].[CompanyId] = [l].[CompanyId])
  LEFT JOIN [EmailTemplateContents] AS [e0] ON ([e].[CompanyId] = [e0].[CompanyId]) AND ([e].[Code] = [e0].[EmailTemplateCode])
  INNER JOIN [Lookups] AS [l0] ON ([e].[StatusCode] = [l0].[DetailKey]) AND ([e].[CompanyId] = [l0].[CompanyId])
  LEFT JOIN (
      SELECT [t].[DetailKey], [t].[CompanyId], [t].[Locale], [t].[Label], [t].[OrderNo]
      FROM (
          SELECT [l1].[DetailKey], [l1].[CompanyId], [l1].[Locale], [l1].[Label], [l1].[OrderNo], ROW_NUMBER() OVER(PARTITION BY [l1].[DetailKey], [l1].[CompanyId] ORDER BY [l1].[DetailKey], [l1].[CompanyId], [l1].[Locale]) AS [row]
          FROM [LookupLabels] AS [l1]
          WHERE [l1].[Locale] = 1033
      ) AS [t]
      WHERE [t].[row] &lt;= 1
  ) AS [t0] ON ([l].[DetailKey] = [t0].[DetailKey]) AND ([l].[CompanyId] = [t0].[CompanyId])
  LEFT JOIN (
      SELECT [t1].[DetailKey], [t1].[CompanyId], [t1].[Locale], [t1].[Label], [t1].[OrderNo]
      FROM (
          SELECT [l2].[DetailKey], [l2].[CompanyId], [l2].[Locale], [l2].[Label], [l2].[OrderNo], ROW_NUMBER() OVER(PARTITION BY [l2].[DetailKey], [l2].[CompanyId] ORDER BY [l2].[DetailKey], [l2].[CompanyId], [l2].[Locale]) AS [row]
          FROM [LookupLabels] AS [l2]
          WHERE [l2].[Locale] = 1033
      ) AS [t1]
      WHERE [t1].[row] &lt;= 1
  ) AS [t2] ON ([l0].[DetailKey] = [t2].[DetailKey]) AND ([l0].[CompanyId] = [t2].[CompanyId])
  WHERE [e].[CompanyId] = 3
  </code></pre>
  
  <p>I compared the actual execution plans for both to see what the difference between these two is.</p>
  
  <p>Here is the join execution plan: </p>
  
  <p><a href="https://i.stack.imgur.com/pYCiW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pYCiW.png" alt="Join Actual Execution Plan"></a></p>
  
  <p>and here is the include execution plan: </p>
  
  <p><a href="https://i.stack.imgur.com/lpmhu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lpmhu.png" alt="Include Actual Execution Plan"></a></p>
  
  <p>The cost of both queries are the same 50%.</p>
  
  <p>Now I have couple of questions :</p>
  
  <ol>
  <li>Based on the query cost(50%) should I consider these two equal performance-wise ?</li>
  <li>Is there any suggestion for using include or join to make one of them faster or with fewer cost?</li>
  <li>What are the pros and cons of using Join (Syntax/maintenance)?</li>
  <li>What are the pros and cons of using Include (Syntax/maintenance)?</li>
  <li>Which one should I use if the table has a few records or if it has lots of records? </li>
  </ol>