将多个表合并为一个IQueryable

Question

我将使用 OData（WebAPI 和 EF） 来查询数据库。现在，我需要将三个表“合并”成一个结果。

我有 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 

- smoksnes

---

首先想到的是分别查询这三个表，然后合并结果。但是，如何在多个表之间进行分页、排序等操作呢？ - Daniel

你能告诉我为什么这里有这样的区别：一个地方用了 WHERE Name LIKE '%Bob%'，而另一个地方却使用了 Where(x => x.Name == "Bob")？为什么呢？ - Sampath

@Sampath - 很好的发现！那只是一个可能的SQL输出示例，不是我真正想要的。我已经更新了问题。 - smoksnes

好的，我们可以对 https://dev59.com/U5vga4cB1Zd3GeqPwRTI#39696353 给出反馈吗？ - Sampath

@Sampath，是的。朋友，请耐心等待。我正在尝试它。 - smoksnes

啊哈..好的，当然 :D - Sampath

Answer 1

你需要使用 Class 替代 Interface IAssociationEntity。我将其命名为 AssociationEntity。

我已经将您原来的 TSQL 查询 转换成了：

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

将以下 Linq To Entity Query 进行翻译：

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

- Sampath

Answer 2

为什么不按照以下步骤进行操作：

var result = db.Companies.Where(x => x.Name == "Bob").Select(x => new { x.Name })
             .Concat(db.Organizations.Where(y => y.Name == "Bob").Select(y => new { y.Name }))
             .Concat(db.Authorities.Where(z => z.Name == "Bob").Select(z => new { z.Name })
             .OrderBy(x => x.Name).Skip(2).Take(10);

您可以将Select方法中的匿名对象替换为基类。

- user449689

Answer 3

试试这个：

var query = db.Companies.Select(x => new { Id = x.CompanyId, x.Name })
            .Concat(db.Organizations.Select(x => new { Id = x.OrganizationId, x.Name }))
            .Concat(db.Authorities.Select(x => new { Id = x.AuthorityId, x.Name }));

var result = query.Where(x => x.Name == "Bob").OrderBy(x => x.Name).Skip(2).Take(10);

生成的SQL是：

SELECT
    [UnionAll2].[CompanyId] AS [C1],
    [UnionAll2].[CompanyId1] AS [C2],
    [UnionAll2].[Name] AS [C3]
    FROM  (SELECT
        [Extent1].[CompanyId] AS [CompanyId],
        [Extent1].[CompanyId] AS [CompanyId1],
        [Extent1].[Name] AS [Name]
        FROM [dbo].[Companies] AS [Extent1]
        WHERE N'Bob' = [Extent1].[Name]
    UNION ALL
        SELECT
        [Extent2].[OrganizationId] AS [OrganizationId],
        [Extent2].[OrganizationId] AS [OrganizationId1],
        [Extent2].[Name] AS [Name]
        FROM [dbo].[Organizations] AS [Extent2]
        WHERE N'Bob' = [Extent2].[Name]
    UNION ALL
        SELECT
        [Extent3].[AuthorityId] AS [AuthorityId],
        [Extent3].[AuthorityId] AS [AuthorityId1],
        [Extent3].[Name] AS [Name]
        FROM [dbo].[Authorities] AS [Extent3]
        WHERE N'Bob' = [Extent3].[Name]) AS [UnionAll2]
    ORDER BY [UnionAll2].[Name] ASC
    OFFSET 2 ROWS FETCH NEXT 10 ROWS ONLY

- haim770

---

谢谢你的回答。它与其他答案非常相似，但我需要将我的查询作为IQueryable返回，然后我就无法使用匿名类型了。 - smoksnes