title: Load only some elements of a nested collection efficiently with LINQ
body: <p>I have the following LINQ query (using EF Core 6 and MS SQL Server):</p>
  <pre><code>var resultSet = dbContext.Systems
              .Include(system =&gt; system.Project)
              .Include(system =&gt; system.Template.Type)
              .Select(system =&gt; new
              {
                  System = system,
                  TemplateText = system.Template.TemplateTexts.FirstOrDefault(templateText =&gt; templateText.Language == locale.LanguageIdentifier),
                  TypeText = system.Template.Type.TypeTexts.FirstOrDefault(typeText =&gt; typeText.Language == locale.LanguageIdentifier)
              })
              .FirstOrDefault(x =&gt; x.System.Id == request.Id);
  </code></pre>
  <p>The requirement is to retrieve the system matching the requested ID and load its project, template and template's type info. The template has multiple TemplateTexts (one for each translated language) but I only want to load the one matching the requested locale, same deal with the TypeTexts elements of the template's type.</p>
  <p>The LINQ query above does that in one query and it gets converted to the following SQL query (I edited the SELECT statements to use * instead of the long list of columns generated):</p>
  <pre><code>SELECT [t1].*, [t2].*, [t5].*
  FROM (
      SELECT TOP(1) [p].*, [t].*, [t0].*
      FROM [ParkerSystems] AS [p]
      LEFT JOIN [Templates] AS [t] ON [p].[TemplateId] = [t].[Id]
      LEFT JOIN [Types] AS [t0] ON [t].[TypeId] = [t0].[Id]
      LEFT JOIN [Projects] AS [p0] ON [p].[Project_ProjectId] = [p0].[ProjectId]
      WHERE [p].[SystemId] = @__request_Id_1
  ) AS [t1]
  LEFT JOIN (
      SELECT [t3].*
      FROM (
          SELECT [t4].*, ROW_NUMBER() OVER(PARTITION BY [t4].[ReferenceId] ORDER BY [t4].[Id]) AS [row]
          FROM [TemplateTexts] AS [t4]
          WHERE [t4].[Language] = @__locale_LanguageIdentifier_0
      ) AS [t3]
      WHERE [t3].[row] &lt;= 1
  ) AS [t2] ON [t1].[Id] = [t2].[ReferenceId]
  LEFT JOIN (
      SELECT [t6].*
      FROM (
          SELECT [t7].*, ROW_NUMBER() OVER(PARTITION BY [t7].[ReferenceId] ORDER BY [t7].[Id]) AS [row]
          FROM [TypeTexts] AS [t7]
          WHERE [t7].[Language] = @__locale_LanguageIdentifier_0
      ) AS [t6]
      WHERE [t6].[row] &lt;= 1
  ) AS [t5] ON [t1].[Id0] = [t5].[ReferenceId]
  </code></pre>
  <p>which is not bad, it's not a super complicated query, but I feel like my requirement can be solved with a much simpler SQL query:</p>
  <pre><code>SELECT * 
  FROM [Systems] AS [p]
  JOIN [Templates] AS [t] ON [p].[TemplateId] = [t].[Id]
  JOIN [TemplateTexts] AS [tt] ON [p].[TemplateId] = [tt].[ReferenceId]
  JOIN [Types] AS [ty] ON [t].[TypeId] = [ty].[Id]
  JOIN [TemplateTexts] AS [tyt] ON [ty].[Id] = [tyt].[ReferenceId]
  WHERE [p].[SystemId] = @systemId and tt.[Language] = 2 and tyt.[Language] = 2
  </code></pre>
  <p>My question is: is there a different/simpler LINQ expression (either in Method syntax or Query syntax) that produces the same result (get all info in one go) because ideally I'd like to not have to have an anonymous object where the filtered sub-collections are aggregated. For even more brownie points, it'd be great if the generated SQL would be simpler/closer to what I think would be a simple query.</p>