title: Multiple Joins in Entity Framework query
body: <p>Long time lurker, first time poster. I have found some good answers on here in the past so figure I would come here and see if I can get a little help!</p>
  
  <p>I am pretty new to Linq and I am using the Entity Framework for my object. I have an .edmx file in my project.</p>
  
  <p>First of all I imported the using System.Linq.Dynamic class from the sample pages that came with VS 2010, so I can add this into my page:</p>
  
  <p>using System.Linq.Dynamic;</p>
  
  <p>The problem is, I don't think my join is working well.</p>
  
  <p>This is my current code:</p>
  
  <pre><code>private void FetchData()
  {
      using (var Context = new ProjectEntities())
      {
          var Query =
              Context.Users
              .Join(Context.UserStats,            // Table to Join
                  u =&gt; u.msExchMailboxGuid,       // Column to Join From
                  us =&gt; us.MailboxGuid,           // Column to Join To
                  (u, us) =&gt; new                  // Alias names from Tables
                  {
                      u,
                      us
                  })
              .Join(Context.TechContacts,         // Table to Join
                  u =&gt; u.u.UserPrincipalName,     // Column to Join From
                  tc =&gt; tc.UPN,                   // Column to Join To
                  (u, tc) =&gt; new                  // Alias names from Tables
                  {
                      u = u,
                      tc = tc
                  })
                 .Where(u =&gt; true)
                 .OrderBy("u.u.CompanyName")
                 .Select("New(u.u.CompanyName,tc.UPN,u.us.TotalItemSize)");
  
          // Add Extra Filters
          if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
          {
  
              Query = Query.Where("u.CompanyName.Contains(@0)", SearchCompanyNameTextBox.Text);
          }
  
          // Set the Record Count
          GlobalVars.TotalRecords = Query.Count();
  
          // Add Paging
          Query = Query
              .Skip(GlobalVars.Skip)
              .Take(GlobalVars.Take);
  
          // GridView Datasource Binding
          GridViewMailboxes.DataSource = Query;
          GridViewMailboxes.DataBind();
      }
  }
  </code></pre>
  
  <p>How can I write it so it works like this would in normal SQL?</p>
  
  <pre><code>SELECT u.Column1,
  u.Column2,
  us.Column1,
  tc.Column1
  FROM Users AS u
  INNER JOIN UserStats AS us
  ON u.msExchMailboxGuid = us.MailboxGuid
  INNER JOIN TechContacts AS tc
  ON u.UserPrincipalName = tc.UPN
  </code></pre>
  
  <p>I need to keep the dynamic .Where clauses and .Select field names, the problem as you can see right now is that I need to do u.u.CompanyName to get back the u.CompanyName field as it's in my joins twice.</p>
  
  <p>I've googled for this for a while now but no dice yet.</p>
  
  <p>Any help much appreciated!</p>
  
  <p>EDIT - this is my current query. It works but it is a bit of a nightmare to behold.</p>
  
  <p>Bear with me. I wanted to include everything here if I can even if it is a bit much.</p>
  
  <p>Dynamic column selection is a must for me. Otherwise I might as well stick with my table adapters and stored procs. Being able to reduce my query to returning less data is one of my goals with this. If anyone can suggest improvements I am all ears?</p>
  
  <p>I couldn't find a way to stop having to select my joins into subitems, in SQL when I join I simply have to return the columns I want by way of my SELECT statement.</p>
  
  <pre><code>private void FetchData()
  {
      using (var Context = new ProjectEntities())
      {
          string Fields = GetDynamicFields();
  
          var Query =
              Context.Users
              .Join(Context.UserStats,            // Table to Join
                  u =&gt; u.msExchMailboxGuid,       // Column to Join From
                  us =&gt; us.MailboxGuid,           // Column to Join To
                  (u, us) =&gt; new                  // Declare Columns for the next Join
                  {
                      ObjectGuid = u.objectGuid,
                      msExchMailboxGuid = u.msExchMailboxGuid,
                      CompanyName = u.CompanyName,
                      ResellerOU = u.ResellerOU,
                      DisplayName = u.DisplayName,
                      MBXServer = u.MBXServer,
                      MBXSG = u.MBXSG,
                      MBXDB = u.MBXDB,
                      MBXWarningLimit = u.MBXWarningLimit,
                      MBXSendLimit = u.MBXSendLimit,
                      MBXSendReceiveLimit = u.MBXSendReceiveLimit,
                      extensionAttribute10 = u.extensionAttribute10,
                      legacyExchangeDN = u.legacyExchangeDN,
                      UserPrincipalName = u.UserPrincipalName,
                      Mail = u.Mail,
                      lastLogonTimeStamp = u.lastLogonTimestamp,
                      createTimeStamp = u.createTimeStamp,
                      modifyTimeStamp = u.modifyTimeStamp,
                      altRecipient = u.altRecipient,
                      altRecipientBL = u.altRecipientBL,
                      DeletedDate = u.DeletedDate,
                      MailboxGuid = us.MailboxGuid,
                      Date = us.Date,
                      AssociatedItemCount = us.AssociatedItemCount,
                      DeletedItemCount = us.DeletedItemCount,
                      ItemCount = us.ItemCount,
                      LastLoggedOnUserAccount = us.LastLoggedOnUserAccount,
                      LastLogonTime = us.LastLogonTime,
                      StorageLimitStatus = us.StorageLimitStatus,
                      TotalDeletedItemSize = us.TotalDeletedItemSize,
                      TotalItemSize = us.TotalItemSize,
                      MailboxDatabase = us.MailboxDatabase
                  })
              .Join(Context.TechContacts,         // Table to Join
                  u =&gt; u.UserPrincipalName,       // Column to Join From
                  tc =&gt; tc.UPN,                   // Column to Join To
                  (u, tc) =&gt; new                  // Declare Final Column Names
                  {
                      ObjectGuid = u.ObjectGuid,
                      msExchMailboxGuid = u.msExchMailboxGuid,
                      CompanyName = u.CompanyName,
                      ResellerOU = u.ResellerOU,
                      DisplayName = u.DisplayName,
                      MBXServer = u.MBXServer,
                      MBXSG = u.MBXSG,
                      MBXDB = u.MBXDB,
                      MBXWarningLimit = u.MBXWarningLimit,
                      MBXSendLimit = u.MBXSendLimit,
                      MBXSendReceiveLimit = u.MBXSendReceiveLimit,
                      extensionAttribute10 = u.extensionAttribute10,
                      legacyExchangeDN = u.legacyExchangeDN,
                      UserPrincipalName = u.UserPrincipalName,
                      Mail = u.Mail,
                      lastLogonTimeStamp = u.lastLogonTimeStamp,
                      createTimeStamp = u.createTimeStamp,
                      modifyTimeStamp = u.modifyTimeStamp,
                      altRecipient = u.altRecipient,
                      altRecipientBL = u.altRecipientBL,
                      DeletedDate = u.DeletedDate,
                      MailboxGuid = u.MailboxGuid,
                      Date = u.Date,
                      AssociatedItemCount = u.AssociatedItemCount,
                      DeletedItemCount = u.DeletedItemCount,
                      ItemCount = u.ItemCount,
                      LastLoggedOnUserAccount = u.LastLoggedOnUserAccount,
                      LastLogonTime = u.LastLogonTime,
                      StorageLimitStatus = u.StorageLimitStatus,
                      TotalDeletedItemSize = u.TotalDeletedItemSize,
                      TotalItemSize = u.TotalItemSize,
                      MailboxDatabase = u.MailboxDatabase,
                      // New Columns from this join
                      UPN = tc.UPN,
                      Customer_TechContact = tc.Customer_TechContact,
                      Customer_TechContactEmail = tc.Customer_TechContactEmail,
                      Reseller_TechContact = tc.Reseller_TechContact,
                      Reseller_TechContactEmail = tc.Reseller_TechContact,
                      Reseller_Name = tc.Reseller_Name
                  })
              .Where(u =&gt; true)
              .OrderBy(GlobalVars.SortColumn + " " + GlobalVars.SortDirection)
              .Select("New(" + Fields + ")");
  
          // Add Extra Filters
          if (!(string.IsNullOrWhiteSpace(SearchCompanyNameTextBox.Text)))
          {
              Query = Query.Where("CompanyName.StartsWith(@0)", SearchCompanyNameTextBox.Text);
          }
  
          // Set the Record Count
          GlobalVars.TotalRecords = Query.Count();
  
          // Add Paging
          Query = Query
              .Skip(GlobalVars.Skip)
              .Take(GlobalVars.Take);
  
          // GridView Datasource Binding
          GridViewMailboxes.DataSource = Query;
          GridViewMailboxes.DataBind();
      }
  }
  </code></pre>
  
  <p>This is what SQL runs in the background:</p>
  
  <pre><code>SELECT TOP (20) 
  [Project1].[C1] AS [C1], 
  [Project1].[objectGuid] AS [objectGuid], 
  [Project1].[msExchMailboxGuid] AS [msExchMailboxGuid], 
  [Project1].[CompanyName] AS [CompanyName], 
  [Project1].[ResellerOU] AS [ResellerOU], 
  [Project1].[DisplayName] AS [DisplayName], 
  [Project1].[MBXServer] AS [MBXServer], 
  [Project1].[MBXSG] AS [MBXSG], 
  [Project1].[MBXDB] AS [MBXDB], 
  [Project1].[MBXWarningLimit] AS [MBXWarningLimit], 
  [Project1].[MBXSendLimit] AS [MBXSendLimit], 
  [Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], 
  [Project1].[extensionAttribute10] AS [extensionAttribute10], 
  [Project1].[legacyExchangeDN] AS [legacyExchangeDN], 
  [Project1].[UserPrincipalName] AS [UserPrincipalName], 
  [Project1].[Mail] AS [Mail], 
  [Project1].[lastLogonTimestamp] AS [lastLogonTimestamp], 
  [Project1].[createTimeStamp] AS [createTimeStamp], 
  [Project1].[modifyTimeStamp] AS [modifyTimeStamp], 
  [Project1].[altRecipient] AS [altRecipient], 
  [Project1].[altRecipientBL] AS [altRecipientBL], 
  [Project1].[DeletedDate] AS [DeletedDate]
      FROM ( SELECT [Project1].[objectGuid] AS [objectGuid],
          [Project1].[msExchMailboxGuid] AS [msExchMailboxGuid],
          [Project1].[CompanyName] AS [CompanyName],
          [Project1].[ResellerOU] AS [ResellerOU],
          [Project1].[DisplayName] AS [DisplayName],
          [Project1].[MBXServer] AS [MBXServer],
          [Project1].[MBXSG] AS [MBXSG],
          [Project1].[MBXDB] AS [MBXDB],
          [Project1].[MBXWarningLimit] AS [MBXWarningLimit],
          [Project1].[MBXSendLimit] AS [MBXSendLimit],
          [Project1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit],
          [Project1].[extensionAttribute10] AS [extensionAttribute10],
          [Project1].[legacyExchangeDN] AS [legacyExchangeDN],
          [Project1].[UserPrincipalName] AS [UserPrincipalName],
          [Project1].[Mail] AS [Mail],
          [Project1].[lastLogonTimestamp] AS [lastLogonTimestamp],
          [Project1].[createTimeStamp] AS [createTimeStamp],
          [Project1].[modifyTimeStamp] AS [modifyTimeStamp],
          [Project1].[altRecipient] AS [altRecipient],
          [Project1].[altRecipientBL] AS [altRecipientBL],
          [Project1].[DeletedDate] AS [DeletedDate],
          [Project1].[C1] AS [C1],
          row_number() OVER (ORDER BY [Project1].[CompanyName] ASC) AS [row_number]
              FROM ( SELECT 
                  [Extent1].[objectGuid] AS [objectGuid], 
                  [Extent1].[msExchMailboxGuid] AS [msExchMailboxGuid], 
                  [Extent1].[CompanyName] AS [CompanyName], 
                  [Extent1].[ResellerOU] AS [ResellerOU], 
                  [Extent1].[DisplayName] AS [DisplayName], 
                  [Extent1].[MBXServer] AS [MBXServer], 
                  [Extent1].[MBXSG] AS [MBXSG], 
                  [Extent1].[MBXDB] AS [MBXDB], 
                  [Extent1].[MBXWarningLimit] AS [MBXWarningLimit], 
                  [Extent1].[MBXSendLimit] AS [MBXSendLimit], 
                  [Extent1].[MBXSendReceiveLimit] AS [MBXSendReceiveLimit], 
                  [Extent1].[extensionAttribute10] AS [extensionAttribute10], 
                  [Extent1].[legacyExchangeDN] AS [legacyExchangeDN], 
                  [Extent1].[UserPrincipalName] AS [UserPrincipalName], 
                  [Extent1].[Mail] AS [Mail], 
                  [Extent1].[lastLogonTimestamp] AS [lastLogonTimestamp], 
                  [Extent1].[createTimeStamp] AS [createTimeStamp], 
                  [Extent1].[modifyTimeStamp] AS [modifyTimeStamp], 
                  [Extent1].[altRecipient] AS [altRecipient], 
                  [Extent1].[altRecipientBL] AS [altRecipientBL], 
                  [Extent1].[DeletedDate] AS [DeletedDate], 
                  1 AS [C1]
                  FROM   [dbo].[Users] AS [Extent1]
                  INNER JOIN [dbo].[UserStats] AS [Extent2] ON [Extent1].[msExchMailboxGuid] = [Extent2].[MailboxGuid]
                  INNER JOIN [dbo].[TechContacts] AS [Extent3] ON [Extent1].[UserPrincipalName] = [Extent3].[UPN]
              )  AS [Project1]
      )  AS [Project1]
  WHERE [Project1].[row_number] &gt; 120
  ORDER BY [Project1].[CompanyName] ASC
  </code></pre>