c# - 带有过滤器的 C# 中的 MongoDB Linq 查询
问题描述
我正在尝试对几个 Mongo 集合进行 LINQ 查询。所有集合都必须基于 ApplicationId 加入,并且必须进行外部加入 - 这样也可以返回没有状态的人。JOIN 部分及其周围的一切都按预期工作。问题是,当我向其中一个集合添加过滤器时,整个事情都会中断
An exception of type 'System.ArgumentException' occurred in System.Linq.Expressions.dll but was not handled in user code: 'Expression of type 'System.Collections.Generic.IEnumerable`1[CDM.Person]' cannot be used for parameter of type 'System.Linq.IQueryable`1[CDM.Person]' of method 'System.Linq.IQueryable`1[CDM.Person] Where[Person](System.Linq.IQueryable`1[CDM.Person], System.Linq.Expressions.Expression`1[System.Func`2[CDM.Person,System.Boolean]])''
这是我的查询
var applications = _dbContext.GetCollection<Application>(typeof(Application).Name).AsQueryable().Where(
x => x.OrganizationID == TokenContext.OrganizationID);
var persons = _dbContext.GetCollection<Person>(typeof(Person).Name).AsQueryable().Where(p =>p.FirstName == "j");
var statuses = _dbContext.GetCollection<ApplicationStatus>(typeof(ApplicationStatus).Name).AsQueryable();
var mortgages = _dbContext.GetCollection<Mortgage>(typeof(Mortgage).Name).AsQueryable();
var statusQuery = from a in applications
join p in persons on a.ApplicationID equals p.ApplicationID
join s in statuses on a.ApplicationID equals s.ApplicationID into pas
join m in mortgages on a.ApplicationID equals m.ApplicationID into morgs
from subWHatever in pas.DefaultIfEmpty()
select new ApplicationStatusProjection
{
ApplicationId = a.ApplicationID,
FirstName = p.FirstName,
LastName = p.Surname,
Prefix = p.Prefix,
DateOfBirth = p.DateOfBirth,
Initials = p.Initials,
PostalCode = p.Addresses.First().PostalCode,
MortgageAmount = morgs.Sum(i => i.MortgageTotal) ?? 0,
StatusExpireAt = subWHatever.ExpireAt ?? DateTime.MinValue,
StatusMessageText = subWHatever.MessageText ?? "",
StatusMessage = subWHatever.MessageStatus ?? ""
};
if (!String.IsNullOrEmpty(orderBy))
{
statusQuery = statusQuery?.OrderBy(orderBy);
}
if (nrOfRecords != null)
{
statusQuery = statusQuery?.Take(nrOfRecords.Value);
}
// Execute the query
var result = statusQuery?.ToList();
return result;
我遵循了这里的指导方针https://mongodb.github.io/mongo-csharp-driver/2.6/reference/driver/crud/linq/我也试过这个
var statusQuery =
from a in applications
join p in persons on a.ApplicationID equals p.ApplicationID into pa
from paObject in pa.DefaultIfEmpty()
join s in statuses on paObject.ApplicationID equals s.ApplicationID into pas
但我得到了和以前一样的错误。
先感谢您。
解决方案
因此,经过可能的试用后,我发现由于 LINQ 查询被转换为 Mongo 查询的方式,您无法在加入之前进行过滤。对此的解决方法是在 statusQuery 对象上进行过滤。在这种情况下,必须在投影对象上进行过滤(因此需要一个新的过滤器)。请参阅下面我如何解决它:
//get collections
var applications = _dbContext.GetCollection<Application>(typeof(Application).Name).AsQueryable().Where(
x => x.OrganizationID == TokenContext.OrganizationID);
var persons = _dbContext.GetCollection<Person>(typeof(Person).Name).AsQueryable();
var statuses = _dbContext.GetCollection<ApplicationStatus>(typeof(ApplicationStatus).Name).AsQueryable();
var mortgages = _dbContext.GetCollection<Mortgage>(typeof(Mortgage).Name).AsQueryable();
//query
var query = from a in applications
join p in persons on a.ApplicationID equals p.ApplicationID
join s in statuses on a.ApplicationID equals s.ApplicationID into applicationStatusView
join m in mortgages on a.ApplicationID equals m.ApplicationID into morgs
from subStatus in applicationStatusView.DefaultIfEmpty()
select new ApplicationStatusProjection
{
ApplicationId = a.ApplicationID,
FirstName = p.FirstName,
LastName = p.Surname,
Prefix = p.Prefix,
DateOfBirth = p.DateOfBirth,
Initials = p.Initials,
Addresses = p.Addresses ?? new List<Address>(),
PostalCode = p.Addresses.First().PostalCode,
MortgageAmount = morgs.Sum(i => i.MortgageTotal) ?? 0,
StatusExpireAt = subStatus.ExpireAt ?? DateTime.MinValue,
StatusMessageText = subStatus.MessageText ?? "",
StatusMessage = subStatus.MessageStatus ?? "",
StatusDate = subStatus.StatusDate
};
//filter & order
var filteredResult = ApplyFilters(query, searchCriteria);
if (!String.IsNullOrEmpty(orderBy))
{
filteredResult = filteredResult?.OrderBy(orderBy);
}
if (nrOfRecords != null)
{
filteredResult = filteredResult?.Take(nrOfRecords.Value);
}
// Execute the query
var result = filteredResult?.ToList();
return result;
并应用过滤器(可能有更聪明的方法来做到这一点):
private IQueryable<ApplicationStatusProjection> ApplyFilters(IQueryable<ApplicationStatusProjection> query, ApplicationStatusProjectionSearch searchCriteria)
{
if (!string.IsNullOrEmpty(searchCriteria.FirstName))
{
query = query.Where(x => x.FirstName.ToLower().StartsWith(searchCriteria.FirstName));
}
if (!string.IsNullOrEmpty(searchCriteria.LastName))
{
query = query.Where(x => x.LastName.ToLower().StartsWith(searchCriteria.LastName));
}
if (!string.IsNullOrEmpty(searchCriteria.PostalCode))
{
query = query.Where(x => x.Addresses.Any(a => a.PostalCode.ToLower().StartsWith(searchCriteria.PostalCode)));
}
//other irrelevant filters
return query;
}
推荐阅读
- php - PHP file_get_contents() SSL 操作失败,代码为 1
- python - 我的简单强化学习模型不学习。我不知道为什么
- asp.net-core - 为什么我的 Azure App Svc 拒绝启动?
- java - 在无法访问的 Java 线程中处理未捕获的异常
- powershell - EMS Powershell V2 如何附加到 CSV
- regex - 正则表达式排除一系列数字
- session - ASP Classic `Session_OnEnd` 重定向不起作用
- c - 在 C 中使用 printf 时出现奇怪的符号
- excel - 使用 VBA 将特定列从工作簿复制到另一个
- algorithm - 为什么在递归矩阵遍历算法中返回 0 或 1?