c# - 不支持嵌套查询。操作 1 ='UnionAll' 操作 2 ='MultiStreamNest'

Question

我有以下查询。

var query = Repository.Query<Product>()
    .Where(p => !p.IsDeleted && p.Article.ArticleSections.Count() > 0)
    .Select(p => new
    {
        OfficeId = p.TariffCategory.Office.Id,
        Office = p.TariffCategory.Office.Name,
        Category = p.TariffCategory.Description,
        ArticleId = p.Article.Id,
        Article = p.Article.Title,
        Destinations = p.ProductDestinations.OrderBy(pd => pd.Destination.Description).Select(pd => new { Id = pd.DestinationId, Name = pd.Destination.Description }),
        GlobalDestinations = p.AllDestinationsInOffice,
        p.Article.LastReviewedDate,
        p.Article.CreatedDate,
        p.Article.CreatedByEmployee
    });
query = query.Concat(Repository.Query<Package>()
    .Where(pkg => !pkg.IsDeleted && pkg.Article.ArticleSections.Count() > 0)
    .Select(pkg => new
    {
        OfficeId = pkg.TariffCategory.Office.Id,
        Office = pkg.TariffCategory.Office.Name,
        Category = pkg.TariffCategory.Description,
        ArticleId = pkg.Article.Id,
        Article = pkg.Article.Title,
        Destinations = pkg.PackageDestinations.OrderBy(pd => pd.Destination.Description).Select(pd => new { Id = pd.DestinationId, Name = pd.Destination.Description }),
        GlobalDestinations = pkg.AllDestinationsInOffice,
        pkg.Article.LastReviewedDate,
        pkg.Article.CreatedDate,
        pkg.Article.CreatedByEmployee
    }));
query = query.Concat(Repository.Query<Backgrounder>()
    .Where(bkgd => !bkgd.IsDeleted && bkgd.Article.ArticleSections.Count() > 0)
    .Select(bkgd => new
    {
        OfficeId = bkgd.TariffCategory.Office.Id,
        Office = bkgd.TariffCategory.Office.Name,
        Category = bkgd.TariffCategory.Description,
        ArticleId = bkgd.Article.Id,
        Article = bkgd.Article.Title,
        Destinations = bkgd.BackgrounderDestinations.OrderBy(bd => bd.Destination.Description).Select(bd => new { Id = bd.DestinationId, Name = bd.Destination.Description }),
        GlobalDestinations = bkgd.AllDestinationsInOffice,
        bkgd.Article.LastReviewedDate,
        bkgd.Article.CreatedDate,
        bkgd.Article.CreatedByEmployee
    }));

// Apply filters
if (OfficeIds.Any())
    query = query.Where(a => OfficeIds.Contains(a.OfficeId));
if (DestinationIds.Any())
    query = query.Where(a => a.GlobalDestinations || a.Destinations.Any(d => DestinationIds.Contains(d.Id)));
if (!string.IsNullOrEmpty(ArticleTitle))
    query = query.Where(a => a.Article.Contains(ArticleTitle));
if (!string.IsNullOrEmpty(TariffCategory))
    query = query.Where(a => a.Category.Contains(TariffCategory));

// Sort results
query = query.OrderBy(a=> a.Office).ThenBy(a => a.Category).ThenBy(a => a.Article);

var articles = query.ToList();

但是，当我运行此查询时出现异常。

The nested query is not supported. Operation1='UnionAll' Operation2='MultiStreamNest'

查询在我的数据库中搜索文章。因为文章可能与 Product、Package 或 Backgrounder 相关，而且我需要来自相关表的信息，所以我为每个文章串联了单独的查询那些项目。

我已将其缩小到对 Destinatons 的分配。显然，这构成了与 Concat() 关联的查询中的查询。 (如果我删除后两个查询和关联的 Concat() 调用，它工作正常。)

看了一会儿之后，我很难找到另一种构建查询的方法，而且不会使查询变得非常非常慢。

有没有人看到我可能错过的任何解决异常的技巧？

Answer 1

不幸的是没有技巧。我认为在不完全重写它的情况下使它工作的唯一合理方法是单独执行查询(应用所有可能的过滤器)然后执行 Concat 并在内存中排序，如下所示:

var queries = new [] 
{
    Repository.Query<Product>()
    .Where(p => !p.IsDeleted && p.Article.ArticleSections.Count() > 0)
    .Select(p => new
    {
        OfficeId = p.TariffCategory.Office.Id,
        Office = p.TariffCategory.Office.Name,
        Category = p.TariffCategory.Description,
        ArticleId = p.Article.Id,
        Article = p.Article.Title,
        Destinations = p.ProductDestinations.OrderBy(pd => pd.Destination.Description).Select(pd => new { Id = pd.DestinationId, Name = pd.Destination.Description }),
        GlobalDestinations = p.AllDestinationsInOffice,
        p.Article.LastReviewedDate,
        p.Article.CreatedDate,
        p.Article.CreatedByEmployee
    }),

    Repository.Query<Package>()
    .Where(pkg => !pkg.IsDeleted && pkg.Article.ArticleSections.Count() > 0)
    .Select(pkg => new
    {
        OfficeId = pkg.TariffCategory.Office.Id,
        Office = pkg.TariffCategory.Office.Name,
        Category = pkg.TariffCategory.Description,
        ArticleId = pkg.Article.Id,
        Article = pkg.Article.Title,
        Destinations = pkg.PackageDestinations.OrderBy(pd => pd.Destination.Description).Select(pd => new { Id = pd.DestinationId, Name = pd.Destination.Description }),
        GlobalDestinations = pkg.AllDestinationsInOffice,
        pkg.Article.LastReviewedDate,
        pkg.Article.CreatedDate,
        pkg.Article.CreatedByEmployee
    }),

    Repository.Query<Backgrounder>()
    .Where(bkgd => !bkgd.IsDeleted && bkgd.Article.ArticleSections.Count() > 0)
    .Select(bkgd => new
    {
        OfficeId = bkgd.TariffCategory.Office.Id,
        Office = bkgd.TariffCategory.Office.Name,
        Category = bkgd.TariffCategory.Description,
        ArticleId = bkgd.Article.Id,
        Article = bkgd.Article.Title,
        Destinations = bkgd.BackgrounderDestinations.OrderBy(bd => bd.Destination.Description).Select(bd => new { Id = bd.DestinationId, Name = bd.Destination.Description }),
        GlobalDestinations = bkgd.AllDestinationsInOffice,
        bkgd.Article.LastReviewedDate,
        bkgd.Article.CreatedDate,
        bkgd.Article.CreatedByEmployee
    }),
};

// Apply filters
if (OfficeIds.Any())
    for (int i = 0; i < queries.Length; i++) queries[i] = queries[i].Where(a => OfficeIds.Contains(a.OfficeId));
if (DestinationIds.Any())
    for (int i = 0; i < queries.Length; i++) queries[i] = queries[i].Where(a => a.GlobalDestinations || a.Destinations.Any(d => DestinationIds.Contains(d.Id)));
if (!string.IsNullOrEmpty(ArticleTitle))
    for (int i = 0; i < queries.Length; i++) queries[i] = queries[i].Where(a => a.Article.Contains(ArticleTitle));
if (!string.IsNullOrEmpty(TariffCategory))
    for (int i = 0; i < queries.Length; i++) queries[i] = queries[i].Where(a => a.Category.Contains(TariffCategory));

// Switch to LINQ to Objects and concatenate the results
var result = queries.Select(query => query.AsEnumerable()).Aggregate(Enumerable.Concat);

// Sort results
result = result.OrderBy(a=> a.Office).ThenBy(a => a.Category).ThenBy(a => a.Article);

var articles = result.ToList();