英文:
Find SQL element that matches properties stored in a dictionary of a target using Entity Framework Core
问题
使用`.Net 6`和`EF Core 7`，
我有一个`Candidates`的`DbSet`。每个候选人都有一个`Id`和一个存储`LanguageId`和`LanguageLevel`的`Language`对象列表。
要搜索一个`Candidate`，我有一个存储`LanguageIds`作为键和`LanguageLevels`作为值的字典的`TargetProfile`。
候选人需要匹配语言和级别要求。
我尝试了类似以下的方式，检查是否在删除所有匹配后仍然存在任何所需的语言
```cs
var matches = from candidate in DbContext.Candidates
where
!targetProfile.Languages.Keys.Except(candidate.Languages
.Where(l => targetProfile.Languages.Keys.Contains(l.LanguageId) &&
targetProfile.Languages[l.LanguageId] <= l.Level).Select(l => l.LanguageId))
.Any()
select candidate;
但当我尝试通过ToList()执行时，我收到了一个实体框架错误，说我的查询太复杂。
'LINQ 表达式无法被转译。要么以可转译的形式重写查询，要么通过插入对 'AsEnumerable'、'AsAsyncEnumerable'、'ToList' 或 'ToListAsync' 的调用来明确切换到客户端评估。有关更多信息，请参见 https://go.microsoft.com/fwlink/?linkid=2101038。'
然后我尝试使用LINQ，但我还没有找到一种动态访问字典的方法。
是否有一种好的方式可以像这样实现？
var result = DbContext.Candidates.Where(
candidate => {
foreach (language in candidate.Languages) => {
targetProfile[language.LanguageId] == language.LanguageLevel;
}}).ToList();
我知道如何在普通的C#中做到这一点，但不知道如何在EF Core查询中实现。
<details>
<summary>英文:</summary>
Using `.Net 6` and `EF Core 7`,
I have a DbSet of `Candidates`. Each candidate has an `Id` and a list of `Language` objects that stores the `LanguageId` and the `LanguageLevel`.
To search for a `Candidate`, I got a `TargetProfile` with a dictionary, storing `LanguageIds` as key and `LanguageLevels` as values.
~~~cs
public class CandidateEntity {
public List<LanguageEntity> Languages;
}
public class LanguageEntity {
public int LanguageId;
public int LanguageLevel;
}
public class TargetProfileDto {
public Dictionary<int,int> Languages;
}
~~~
The candidates need to match both the language and level requirements.
I tried something like this where I checked, if any required language would remain after I remove all that match
~~~cs
var matches = from candidate in DbContext.Candidates
where
!targetProfile.Languages.Keys.Except(candidate.Languages
.Where(l => targetProfile.Languages.Keys.Contains(l.LanguageId) &&
targetProfile.Languages[l.LanguageId] <= l.Level).Select(l => l.LanguageId))
.Any()
select candidate;
~~~
but when I try to execute via ToList(), I get an entity framework error, that my query is too complex.
> 'The LINQ expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'
I then tried using `LINQ`, but I have not found a way to access the dictionary dynamically.
Is there a good way to have something like this?
~~~cs
var result = DbContext.candidates.Where(
candidate => {
foreach(language in candidate.Language) => {
targetProfile[language.LanguageId) == language.LanguageLevel;
}}).ToList();
~~~
I know how to do this in plain C#, but not as a EF Core query.
</details>
# 答案1
**得分**: 1
以下是您要求的中文翻译部分：
这种查询需要动态表达式构建。思路是构建类似于这样的表达式：
```cs
var query = DbContext.Candidates
.Where(c => c.Languages.Count(l => l.LanguageId == 1 && l.LanguageLevel == 2 || l.LanguageId == 2 && l.LanguageLevel == 3 || ...)
== targetProfile.Languages.Count);
对于这种方法的一个要求是：匹配的项应该是一组唯一的项，这由Dictionary容器来保证。
使用方法很简单：
var query = DbContext.Candidates
.MatchByCriteria(targetProfile.Languages, c => c.Languages, (l, t) => l.LanguageId == t.LanguageId && l.LanguageLevel == t.LanguageLevel)
以及实现：
public static class MatchExtensions
{
public static IQueryable<TEntity> MatchByCriteria<TEntity, TProp, TItem>(
this IQueryable<TEntity> query,
IEnumerable<TItem> items,
Expression<Func<TEntity, IEnumerable<TProp>>> prop,
Expression<Func<TProp, TItem, bool>> filterPattern)
{
var itemsList = items.ToList();
var matchPredicate = GetItemsPredicate(itemsList, filterPattern);
var propBody = prop.Body;
// e.SomeCollection.Count(matchPredicate)
var countExpr = Expression.Call(typeof(Enumerable), nameof(Enumerable.Count), new[] { typeof(TProp) },
propBody, matchPredicate);
// e.SomeCollection.Count(matchPredicate) == items.Count
var equalsExpr = Expression.Equal(countExpr, Expression.Constant(itemsList.Count));
// e => e.SomeCollection.Count(matchPredicate) == items.Count
var matchLambda = Expression.Lambda<Func<TEntity, bool>>(equalsExpr, prop.Parameters[0]);
// query.Where(e => e.SomeCollection.Count(matchPredicate) == items.Count)
return query.Where(matchLambda);
}
public static Expression<Func<TEntity, bool>> GetItemsPredicate<TEntity, TItem>(
IEnumerable<TItem> items,
Expression<Func<TEntity, TItem, bool>> filterPattern, bool isOr = true, bool emptyValue = false)
{
Expression? predicate = null;
foreach (var item in items)
{
var itemExpr = Expression.Constant(item);
var itemCondition = ReplacingExpressionVisitor.Replace(filterPattern.Parameters[1], itemExpr, filterPattern.Body);
if (predicate == null)
predicate = itemCondition;
else
{
predicate = Expression.MakeBinary(isOr ? ExpressionType.OrElse : ExpressionType.AndAlso, predicate,
itemCondition);
}
}
predicate ??= Expression.Constant(emptyValue);
var filterLambda = Expression.Lambda<Func<TEntity, bool>>(predicate, filterPattern.Parameters[0]);
return filterLambda;
}
}
请注意，这只是代码的翻译部分，不包括问题的回答。如果您需要进一步的帮助，请随时提问。
英文:
Such query needs dynamic expression building. Idea is to build expression like this:
var query = DbContext.Candidates
.Where(c => c.Languages.Count(l => l.LanguageId == 1 && l.LanguageLevel == 2 || l.LanguageId == 2 && l.LanguageLevel == 3 || ...)
== targetProfile.Languages.Count);
One requirement for such approcach: match items should be list of unique items, which is guaranteed by Dictionary container.
Usage is simple:
var query = DbContext.Candidates
.MatchByCriteria(targetProfile.Languages, c => c.Languages, (l, t) => l.LanguageId == t.LanguageId && l.LanguageLevel == t.LanguageLevel)
And realuisation:
public static class MatchExtensions
{
public static IQueryable<TEntity> MatchByCriteria<TEntity, TProp, TItem>(
this IQueryable<TEntity> query,
IEnumerable<TItem> items,
Expression<Func<TEntity, IEnumerable<TProp>>> prop,
Expression<Func<TProp, TItem, bool>> filterPattern)
{
var itemsList = items.ToList();
var matchPredicate = GetItemsPredicate(itemsList, filterPattern);
var propBody = prop.Body;
// e.SomeCollection.Count(matchPredicate)
var countExpr = Expression.Call(typeof(Enumerable), nameof(Enumerable.Count), new[] { typeof(TProp) },
propBody, matchPredicate);
// e.SomeCollection.Count(matchPredicate) == items.Count
var equalsExpr = Expression.Equal(countExpr, Expression.Constant(itemsList.Count));
// e => e.SomeCollection.Count(matchPredicate) == items.Count
var matchLambda = Expression.Lambda<Func<TEntity, bool>>(equalsExpr, prop.Parameters[0]);
// query.Where(e => e.SomeCollection.Count(matchPredicate) == items.Count)
return query.Where(matchLambda);
}
public static Expression<Func<TEntity, bool>> GetItemsPredicate<TEntity, TItem>(
IEnumerable<TItem> items,
Expression<Func<TEntity, TItem, bool>> filterPattern, bool isOr = true, bool emptyValue = false)
{
Expression? predicate = null;
foreach (var item in items)
{
var itemExpr = Expression.Constant(item);
var itemCondition = ReplacingExpressionVisitor.Replace(filterPattern.Parameters[1], itemExpr, filterPattern.Body);
if (predicate == null)
predicate = itemCondition;
else
{
predicate = Expression.MakeBinary(isOr ? ExpressionType.OrElse : ExpressionType.AndAlso, predicate,
itemCondition);
}
}
predicate ??= Expression.Constant(emptyValue);
var filterLambda = Expression.Lambda<Func<TEntity, bool>>(predicate, filterPattern.Parameters[0]);
return filterLambda;
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论