我有以下查询。
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();
但是,当我运行此查询时,出现异常。
不支持嵌套查询。Operation1 ='UnionAll'Operation2 ='MultiStreamNest'
该查询搜索我数据库中的文章。由于文章可以涉及到Product
,Package
或者Backgrounder
,我需要从相关表中的信息,我串连为每个项目单独的查询。
我已将其范围缩小到Destinatons
。显然,这构成了与相关联的查询中的查询Concat()
。(如果我删除了后两个查询和关联的Concat()
调用,它将正常工作。)
在看了一段时间之后,我无法看到另一种构造查询的方法,而又没有使其变得非常慢。
有人能看到我可能错过的任何技巧来解决该异常吗?
不幸的是,没有技巧。我认为在不完全重写的情况下完成这项工作的唯一合理方法是分别执行查询(应用所有可能的过滤器),然后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();
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句