C#Linq表达式无法翻译

问题描述

我正在尝试执行linq查询，以获取具有某些特定技能的所有雇员.search.skills是具有一些技能的字符串列表，我想检索所有具有所有这些技能(和条件)的用户.在我关于员工的where子句中，exp.Skills是ICollection，expSkill.SkillName是技能名称

I am trying to execute a linq query to get all employes that have some specific skills. search.skills is a list of strings with some skills and I want to retrieve all the user which have all those skills (And condition).In my where clause on employees, exp.Skills is ICollection and expSkill.SkillName is the skill name

                .Where(
                emp => search.Skills.All(
                    searchSkill => emp.Experiences.Select(exp => exp.Skills).SelectMany(x => x).Select(expSkill => expSkill.SkillName).Contains(searchSkill)
                ))
            .ToListAsync();

尝试执行该错误时出现以下错误.我正在使用EntityFramework Core 3

I am getting the following error while trying to execute it. I am using entityframework core 3

The LINQ expression 'DbSet<Employee>
.Where(e => __search_Skills_0
    .All(searchSkill => DbSet<Experience>
        .Where(e0 => EF.Property<Nullable<Guid>>(e, "Id") != null && EF.Property<Nullable<Guid>>(e, "Id") == EF.Property<Nullable<Guid>>(e0, "EmployeeId"))
        .SelectMany(
            source: e0 => DbSet<ExperienceSkill>
                .Where(e1 => EF.Property<Nullable<Guid>>(e0, "EmployeeId") != null && new AnonymousObject(new object[]
                {
                    (object)EF.Property<Nullable<Guid>>(e0, "EmployeeId"),
                    (object)EF.Property<string>(e0, "ProjectCode")
                }) == new AnonymousObject(new object[]
                {
                    (object)EF.Property<Nullable<Guid>>(e1, "ExperienceEmployeeId"),
                    (object)EF.Property<string>(e1, "ExperienceProjectCode")
                })),
            collectionSelector: (e0, c) => new TransparentIdentifier<Experience, ExperienceSkill>(
                Outer = e0,
                Inner = c
            ))
        .Select(ti => ti.Inner.SkillName)
        .Contains(searchSkill)))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

谁能告诉我我在查询中做错了什么?谢谢

Can anyone tell me what I am doing wrong in my query? Thanks

推荐答案

除简单的 Contains 之外，EF Core当前无法翻译LINQ运算符，例如您的 search.Skills

EF Core currently cannot translate LINQ operators other than simple Contains on memory collections like your search.Skills.

因此，您需要找到 memoryCollection.All 运算符的替代方法.在这种情况下，我使用的是计数匹配"方法，该方法查找所有匹配的记录并比较不同值的计数.

So you need to find an alternative to memoryCollection.All operator. What I use in such case is the "count matches" approach, which find all matching records and compares the counts of the distinct values.

例如替换

emp => search.Skills.All(
    searchSkill => emp.Experiences.Select(exp => exp.Skills).SelectMany(x => x).Select(expSkill => expSkill.SkillName).Contains(searchSkill)

使用

emp => emp.Experiences
    .SelectMany(exp => exp.Skills, (exp, skill) => skill.SkillName)
    .Where(skillName => search.Skills.Contains(skillName))
    .Distinct().Count() == search.Skills.Distinct().Count()

这篇关于C#Linq表达式无法翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！