title: Entity Framework join optimization
body: <p>I have recently started using Entity Framework and code first migration. My application is now "live" and I am starting to see that things are getting slow. My database have around 30.000 rows in the table that are used the most. </p>
  
  <p>This is a method that I return a lot as I have to tables, one with the message data, and one with each recipient:</p>
  
  <pre><code>IQueryable&lt;CompleteMessageModel&gt; completeMessageModels = 
      from msg in db.NewMessageItems
      join mr in db.MessageRecipients on msg.MessageId equals mr.MessageId
      select
      new CompleteMessageModel()
      {
            MessageId = msg.MessageId,
            RecipientMessageId = mr.MessageRecipientId,
            Title = msg.Title,
            Message = msg.Message,
            Recipients = msg.Recipients,
            AuthorUserId = msg.AuthorId,
            RecipientUserId = mr.RecipientId,
            StatusCode = mr.StatusCode,
            Timestamp = msg.Timestamp,
            IsRead = mr.ReadTimestamp &gt; 0,
            ReadTimestamp = mr.ReadTimestamp,
            GeoTag = msg.GeoTag
      };
  </code></pre>
  
  <p>Then I use this IQueryable to ask for messages above a certain timestamp and similar actions. </p>
  
  <p><strong>My question is: Can this query be optimized further?</strong> </p>
  
  <p>This is the execution plan of the most used query:</p>
  
  <pre><code>SELECT TOP (90) 
  [Project1].[MessageId] AS [MessageId], 
  [Project1].[MessageRecipientId] AS [MessageRecipientId], 
  [Project1].[Title] AS [Title], 
  [Project1].[Message] AS [Message], 
  [Project1].[Recipients] AS [Recipients], 
  [Project1].[AuthorId] AS [AuthorId], 
  [Project1].[RecipientId] AS [RecipientId], 
  [Project1].[StatusCode] AS [StatusCode], 
  [Project1].[Timestamp] AS [Timestamp], 
  [Project1].[C1] AS [C1], 
  [Project1].[ReadTimestamp] AS [ReadTimestamp], 
  [Project1].[GeoTag] AS [GeoTag]
  FROM ( SELECT 
      [Extent1].[MessageId] AS [MessageId], 
      [Extent1].[Message] AS [Message], 
      [Extent1].[Title] AS [Title], 
      [Extent1].[AuthorId] AS [AuthorId], 
      [Extent1].[Timestamp] AS [Timestamp], 
      [Extent1].[Recipients] AS [Recipients], 
      [Extent1].[GeoTag] AS [GeoTag], 
      [Extent2].[MessageRecipientId] AS [MessageRecipientId], 
      [Extent2].[RecipientId] AS [RecipientId], 
      [Extent2].[ReadTimestamp] AS [ReadTimestamp], 
      [Extent2].[StatusCode] AS [StatusCode], 
      CASE WHEN ([Extent2].[ReadTimestamp] &gt; 0) THEN cast(1 as bit) WHEN ( NOT ([Extent2].[ReadTimestamp] &gt; 0)) THEN cast(0 as bit) END AS [C1]
      FROM  [dbo].[NewMessageModels] AS [Extent1]
      INNER JOIN [dbo].[MessageRecipients] AS [Extent2] ON [Extent1].[MessageId] = [Extent2].[MessageId]
      WHERE ([Extent2].[RecipientId] = @p__linq__0) AND (1 &lt;&gt; [Extent2].[StatusCode]) AND (3 &lt;&gt; [Extent2].[StatusCode]) AND ([Extent1].[Timestamp] &gt;= @p__linq__1)
  )  AS [Project1]
  ORDER BY [Project1].[Timestamp] DESC
  </code></pre>
  
  <p><strong>And if it can be optimized, How would this look in c#?</strong> </p>