c# - 确保记录包含所有关系，而不是任何关系

Question

我有一个正在处理的 linqpad 脚本，它可以工作，但问题是它使用了 .AsEnumerable()它将集合调用到内存中。有一天可能会有数千个，所以我想尽可能地推迟一切。

我正在尝试简单地执行检查以确保我是否通过了 new long[] { 2, 4 }到函数，然后将返回任何同时 ID 为 2 和 4 的体验。

以前，我只使用 .Contains()但这将返回 Experiences 2 或 4。

有没有更好的方法来编写这段代码，使其返回一个 IQueryable<Experience>而不是 List<Experience>所以我不必为了执行字符串连接而将所有结果加载到内存中？

void Main()
{
    var AllExperiences = new List<_Experience>();
    AllExperiences.Add(new _Experience { Id = 1, Name = "Experience 1" });
    AllExperiences.Add(new _Experience { Id = 2, Name = "Experience 2" });

    AllExperienceTags.Add(new _ExperienceTag { ExperienceId = 1, TagId = 2 });
    AllExperienceTags.Add(new _ExperienceTag { ExperienceId = 1, TagId = 4 });
    AllExperienceTags.Add(new _ExperienceTag { ExperienceId = 2, TagId = 2 });

    var experiences = FilterBySelectedTags(AllExperiences, new[] { 2, 4 }.ToList());

    experiences.Dump();
}

public List<_ExperienceTag> AllExperienceTags = new List<UserQuery._ExperienceTag>();

// Define other methods and classes here
public List<_Experience> FilterBySelectedTags(List<_Experience> experiences, List<int> selectedTagIds)
{
    var filteredExperiencesTags = AllExperienceTags.Where(x => selectedTagIds.Contains(x.TagId));

    var obj = filteredExperiencesTags.OrderBy(x => x.TagId).GroupBy(x => x.ExperienceId).AsEnumerable().Select(x => new
    {
        ExperienceId = x.Key,
        ExpTags = string.Join(", ", x.Select(y => y.TagId))
    });

    var filteredTags = obj.Where(x => x.ExpTags == string.Join(", ", selectedTagIds));

    // make sure all the selected tags are found in the experience, not just any
    return experiences.Where(x => filteredTags.Select(y => y.ExperienceId).Contains(x.Id)).ToList();
}

public class _Experience
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class _ExperienceTag
{
    public int ExperienceId { get; set; }
    public int TagId { get; set; }
}

Answer 1

Experience 实体应该有一个指向 ExperienceTags 的导航属性:

public virtual ICollection<ExperienceTag> ExperienceTags{get;set;}

如果是这种情况，这应该可行:

var query= from e in Experiences
           let experienceTagIds=e.ExperiencesTags.Select(et=>et.TagId)
           where selectedTagIds.All(x=>experienceTagIds.Contains(x))
           select e;