title: Entity framework performance join vs navigation property
body: <p>I am trying to understand why is join in my case faster than statement which use navigation property. I have two queries.</p>
  
  <p>First with navigation property :</p>
  
  <pre><code>          var result = (from users in context.MetricBloodPreasure
                  orderby users.User.LastName, users.User.FirstName
                  select new
                  {
                      UserName = users.User.LastName + ", " + users.User.FirstName,
                      Date = users.DateOfValue,
                  }).ToList();
  </code></pre>
  
  <p>Generatet sql : </p>
  
  <pre><code>SELECT 
      [Project1].[C1] AS [C1], 
      [Project1].[C2] AS [C2], 
      [Project1].[DateOfValue] AS [DateOfValue]
      FROM ( SELECT 
          [Extent1].[DateOfValue] AS [DateOfValue], 
          [Extent2].[FirstName] AS [FirstName], 
          [Extent2].[LastName] AS [LastName], 
          1 AS [C1], 
          CASE WHEN ([Extent2].[LastName] IS NULL) THEN N'' ELSE [Extent2].[LastName] END + N', ' + CASE WHEN ([Extent2].[FirstName] IS NULL) THEN N'' ELSE [Extent2].[FirstName] END AS [C2]
          FROM  [dbo].[MetricBloodPreasure] AS [Extent1]
          INNER JOIN [dbo].[User] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[Id]
      )  AS [Project1]
      ORDER BY [Project1].[LastName] ASC, [Project1].[FirstName] ASC
  </code></pre>
  
  <p>Second with join:</p>
  
  <pre><code>var result1 = (from u in context.User
                  orderby u.LastName, u.FirstName
                  join us in context.MetricBloodPreasure
                      on u.Id equals us.UserId into users
                  from s in users
                  select new
                  {
                      UserName = s.User.LastName + ", " + s.User.FirstName,
                      Date = s.DateOfValue,
                  }).ToList();
  </code></pre>
  
  <p>Generated sql:</p>
  
  <pre><code>SELECT 
      1 AS [C1], 
      CASE WHEN ([Extent1].[LastName] IS NULL) THEN N'' ELSE [Extent1].[LastName] END + N', ' + CASE WHEN ([Extent1].[FirstName] IS NULL) THEN N'' ELSE [Extent1].[FirstName] END AS [C2], 
      [Extent2].[DateOfValue] AS [DateOfValue]
      FROM  [dbo].[User] AS [Extent1]
      INNER JOIN [dbo].[MetricBloodPreasure] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[UserId]) AND ([Extent2].[UserId] = [Extent1].[Id])
  </code></pre>
  
  <p>Before running first query, call <code>var user = context.User.FirstOrDefault();</code> because I think open connection to database take some time. </p>
  
  <p>Results :
  Navigation property query : 00:00:00.6719646
  Join query : 00:00:00.4941169</p>
  
  <p>Looking at results it seems that Linq queries that use joins instead of navigation properties are faster. Is that true or I'm doing something wrong ?</p>