EF Combine several tables into one IQueryable

Question

I'm using OData (WebAPI and EF) to query a database. And now I have to "merge" three tables into one result.

I have 3 tables and an interface, which looks like this:

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; }
}

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.

I'm looking for something that would look like this in SQL

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

Is there a way to merge three tables into one IQueryable?

I would like to merge these three tables into an IQueryable<IAssociationEntity>. I really do need to use the interface (or possibly a base class) and to get the results as an IQueryable for my OData implementation. Something like this, but it doesn't compile:

var query = db.Companies
    .Concat(db.Organizations)
    .Concat(db.Authorities);
IQueryable<IAssociationEntity> mergedTables = query.Cast<IAssociationEntity>();

// Here is an EXAMPLE usage.
// What I really need is to return the IQueryable<IAssociationEntity> for my OData.
var result = mergedTables.Where(x => x.Name == "Bob").OrderBy(x => x.Name).Skip(2).Take(10);

And my usage for the odata controller:

public class AssociationController : ODataController
{
    [EnableQuery]
    public override IQueryable<IAssociationEntity> Get(ODataQueryOptions<IAssociationEntity> q)
    {
        // return my IQueryable here...
    }
}

Needless to say, I do not want to read the entire table into memory when creating the IQueryable. I actually do need to use paging since several of these three tables have millions of rows.

FINAL SOLUTION ENDED UP AS:

  var query = db.Companies.Select(x => new AssociationEntity { Name = x.Name })
.Concat(db.Organizations.Select(x => new AssociationEntity { Name = x.Name }))
.Concat(db.Authorities.Select(x => new AssociationEntity { Name = x.Name }));
    return query;

And when executing against the queryable:

_query.Where(x => x.Name.Contains("M")).OrderBy(x => x.Name).Skip(10).Take(50).ToList();

Generated SQL:

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 

Answer 1

You have to use Class instead of Interface IAssociationEntity.I named it as AssociationEntity.

I have converted your original TSQL query this :

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

To Linq To Entity Query as shown below.

var queryKey ="Bob";

var query = ((from c in db.Company  where (c.Name = queryKey) select new AssociationEntity { Name = c.Name }).Take(4))
.Concat((from o in db.Organization  where (o.Name = queryKey) select new AssociationEntity { Name = o.Name }).Take(4))
.Concat((from a in db.Authority  where (a.Name = queryKey) select new AssociationEntity { Name = a.Name }).Take(4));