title: EF Combine several tables into one IQueryable
body: <p>I'm using OData (WebAPI and EF) to query a database. And now I have to "<em>merge</em>" three tables into one result.</p>
  
  <p>I have 3 tables and an interface, which looks like this:</p>
  
  <pre><code>public class Authority : IAssociationEntity
  {
      public string Name { get; set; }
      public int AuthorityId { get; set; }
  }
  
  public class Company : IAssociationEntity
  {
      public string Name { get; set; }
      public int CompanyId { get; set; }
  }
  
  public class Organization : IAssociationEntity
  {
      public string Name { get; set; }
      public int OrganizationId { get; set; }
  }
  
  public interface IAssociationEntity
  {
      string Name { get; set; }
  }
  </code></pre>
  
  <p>As you can see there are some obvious similarities between the three tables, but for some reasons they need to stay in separate tables. What I need is to use paging and search all three by name, and present them in the same list for a user.</p>
  
  <p>I'm looking for something that would look like this in SQL</p>
  
  <pre><code>SELECT TOP 4 a.* FROM
  (
      SELECT CompanyID, Name from Company WHERE Name = 'Bob'
      UNION
      SELECT OrganizationID, Name from Organization WHERE Name = 'Bob'
      UNION
      SELECT AuthorityID, Name from Authority WHERE Name = 'Bob'
  ) AS a
  </code></pre>
  
  <p>Is there a way to merge three tables into one <code>IQueryable</code>?</p>
  
  <p>I would like to merge these three tables into an <code>IQueryable&lt;IAssociationEntity&gt;</code>. I really do need to use the interface (or possibly a base class) and to get the results as an <code>IQueryable</code> for my OData implementation. Something like this, but it doesn't compile:</p>
  
  <pre><code>var query = db.Companies
      .Concat(db.Organizations)
      .Concat(db.Authorities);
  IQueryable&lt;IAssociationEntity&gt; mergedTables = query.Cast&lt;IAssociationEntity&gt;();
  
  // Here is an EXAMPLE usage.
  // What I really need is to return the IQueryable&lt;IAssociationEntity&gt; for my OData.
  var result = mergedTables.Where(x =&gt; x.Name == "Bob").OrderBy(x =&gt; x.Name).Skip(2).Take(10);
  </code></pre>
  
  <p>And my usage for the odata controller:</p>
  
  <pre><code>public class AssociationController : ODataController
  {
      [EnableQuery]
      public override IQueryable&lt;IAssociationEntity&gt; Get(ODataQueryOptions&lt;IAssociationEntity&gt; q)
      {
          // return my IQueryable here...
      }
  }
  </code></pre>
  
  <p>Needless to say, I do not want to read the entire table into memory when creating the <code>IQueryable</code>. I actually do need to use paging since several of these three tables have millions of rows.</p>
  
  <p><strong>FINAL SOLUTION ENDED UP AS:</strong></p>
  
  <pre><code>  var query = db.Companies.Select(x =&gt; new AssociationEntity { Name = x.Name })
  .Concat(db.Organizations.Select(x =&gt; new AssociationEntity { Name = x.Name }))
  .Concat(db.Authorities.Select(x =&gt; new AssociationEntity { Name = x.Name }));
      return query;
  </code></pre>
  
  <p>And when executing against the queryable:</p>
  
  <pre><code>_query.Where(x =&gt; x.Name.Contains("M")).OrderBy(x =&gt; x.Name).Skip(10).Take(50).ToList();
  </code></pre>
  
  <p>Generated SQL:</p>
  
  <pre><code>SELECT 
      [UnionAll2].[C1] AS [C1], 
      [UnionAll2].[Name] AS [C2]
      FROM  (SELECT 
          1 AS [C1], 
          [Extent1].[Name] AS [Name]
          FROM [dbo].[Company] AS [Extent1]
          WHERE [Extent1].[Name] LIKE N'%M%'
      UNION ALL
          SELECT 
          1 AS [C1], 
          [Extent2].[Name] AS [Name]
          FROM [dbo].[Organization] AS [Extent2]
          WHERE [Extent2].[Name] LIKE N'%M%'
      UNION ALL
          SELECT 
          1 AS [C1], 
          [Extent3].[Name] AS [Name]
          FROM [dbo].[Authority] AS [Extent3]
          WHERE [Extent3].[Name] LIKE N'%M%') AS [UnionAll2]
      ORDER BY [UnionAll2].[Name] ASC
      OFFSET 10 ROWS FETCH NEXT 50 ROWS ONLY 
  </code></pre>