title: EF6 LINQ nested includes
body: <p>So, I've been stuck for hours trying to include two nested collections in my model for a WebAPI. After hours of trial and error, searching, reading and more trial and error I still cant figure out how to do this properly.</p>
  
  <p>Im using MySQL as database.</p>
  
  <p>My model relationships looks like this:</p>
  
  <pre><code>Account &lt;-1:m-&gt; Character
  Character &lt;-m:1-&gt; Corporation
  Corporation &lt;-1:m-&gt; CorporationWallet
  Corporation &lt;-1:m-&gt; CorporationTaxes
  </code></pre>
  
  <p>The model is set up through EF Code-First and it's working in the database, the navigation relationships are working as well.</p>
  
  <p>What I'm trying to accomplish is to fetch an account and it's characters, each character should include their corporation and that corporations wallets and taxes.</p>
  
  <p>The following works and fetches everything needed, except the taxes:</p>
  
  <pre><code>Account account = db.Accounts
      .Include(x =&gt; x.Characters.Select(y =&gt; y.Corporation.Wallets))
      .Where(o =&gt; o.AccessToken == accessToken)
      .Take(1)
      .ToList()
      .FirstOrDefault();
  </code></pre>
  
  <p>However when i wish to include the taxes it throws an exception and doesn't continue:</p>
  
  <pre><code>Account account = db.Accounts
      .Include(x =&gt; x.Characters.Select(y =&gt; y.Corporation.Wallets))
      .Include(x =&gt; x.Characters.Select(y =&gt; y.Corporation.Taxes))
      .Where(o =&gt; o.AccessToken == accessToken)
      .Take(1)
      .ToList()
      .FirstOrDefault();
  </code></pre>
  
  <p>It fails with the following exception:</p>
  
  <pre><code>An error occurred while executing the command definition. See the inner exception for details.
  </code></pre>
  
  <p>Inner exception:</p>
  
  <pre><code>&lt;Message&gt;An error has occurred.&lt;/Message&gt;
  &lt;ExceptionMessage&gt;Unknown column 'UnionAll1.C1' in 'field list'&lt;/ExceptionMessage&gt;
  &lt;ExceptionType&gt;MySql.Data.MySqlClient.MySqlException&lt;/ExceptionType&gt;
  &lt;StackTrace&gt;
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
  at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32&amp; affectedRow, Int64&amp; insertedId)
  at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32&amp; affectedRows, Int64&amp; insertedId)
  at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
  at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.&lt;Reader&gt;b__c(DbCommand t, DbCommandInterceptionContext`1 c)
  at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
  at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
  at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
  &lt;/StackTrace&gt;
  </code></pre>
  
  <p>Let me know if there is any more information i can provide.</p>
  
  <p>Thanks in advance for your help.</p>
  
  <p>Edit 1:</p>
  
  <p>The following gives the same exception and error:</p>
  
  <pre><code>var query = from acc in db.Accounts
              where acc.AccessToken == accessToken
              select new
              {
                  acc,
                  Characters = from cha in acc.Characters
                               select new
                               {
                                   cha,
                                   Account = cha.Account,
                                   Corporation = cha.Corporation,
                                   Taxes = from tax in cha.Corporation.Taxes
                                           select new
                                           {
                                               tax,
                                               Corporation = tax.Corporation
                                           },
                                   Wallets = from wallet in cha.Corporation.Wallets
                                             select new
                                             {
                                                 wallet,
                                                 Corporation = wallet.Corporation
                                             }
                               }
              };
  Account account = query
      .Select(x =&gt; x.acc)
      .FirstOrDefault()
  </code></pre>