EF将多个表合并为一个IQueryable

合并 “三张表合成一个结果。

我有3个表和一个界面,如下所示:

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

正如您所看到的,这三个表之间有一些明显的相似之处,但由于某些原因,它们需要保留在单独的表中。我需要的是使用分页并按名称搜索这三个页面,并将它们显示在用户的同一列表中。

我正在寻找在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

有没有办法把三个表合并成一个 IQueryable ?

我想把这三个表合并成一个 IQueryable<IAssociationEntity> 。我确实需要使用接口(或者可能是基类)并将结果作为 IQueryable(查询) 用于我的OData实现。类似于此,但无法编译:

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

以及我对odata控制器的用法:

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

不用说,在创建 IQueryable(查询) 。实际上我确实需要使用分页,因为这三个表中有几个表有数百万行。

最终解决方案结束时为:

  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;

在对查询表执行时:

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

生成的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