从DbSet< TEntity>获取所有实体.具有IEnumerable中的属性值 [英] Get all entities from DbSet<TEntity> with a property value that is in an IEnumerable

问题描述

这似乎很简单，但我似乎无法解决.使用EF Core，我的DbContext中有一个DbSet<Rule> Rules.

This seems like it would be really simple, but I cannot seem to resolve it. Using EF Core, I have a DbSet<Rule> Rules in my DbContext.

public class Rule
{
    public int Id { get; set; }
    public string Raw { get; set; }
}

我正在尝试编写一个查询，其中给定IEnumerable<string> lines，将DbSet中的所有Rule都给我，其中Raw的值是lines中的元素(完全匹配，不是值的子字符串).

I am trying to write a query where, given an IEnumerable<string> lines, give me all of the Rules from the DbSet where its Raw value is an element in lines (exact match, not a substring of a value).

一段时间以来，我一直在使用类似的东西:

For some time, I was using something like:

private IQueryable<Rule> GetExistingRules() =>
    dbContext.Rules.Where(r => lines.Contains(r.Raw));

但是，从那以后，我发现(我认为)这没有达到我的期望. (此方法之后立即为当前不存在的lines的所有元素插入新的Rule.我得到具有相同Raw值的重复Rule s ...)我想，相反，我需要使用.Intersect()吗?

But, I have since discovered that (I think) this was not doing what I was expecting. (This method is immediately followed by inserting new Rules for all elements of lines that do not currently exist. I was getting duplicate Rules with the same Raw value...) I think, instead, I need to use .Intersect()?

我尝试按照此使用自定义EqualityComparer，但会引发异常.

I tried using a custom EqualityComparer per this, but it throws an exception.

    private IQueryable<Rule> GetExistingRules()
    {
        var lineRules = lines.Select(l => new Rule {Raw = l});
        return dbContext.Rules.Intersect(lineRules, new RuleRawEqualityComparer());
    }

    private class RuleRawEqualityComparer : IEqualityComparer<Rule>
    {
        public bool Equals(Rule x, Rule y) => x?.Raw == y?.Raw;
        ...
    }

无法解析表达式 '值(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1 [FilterLists.Data.Entities.Rule]).Intersect(__ p_0， __p_1)':当前不支持方法'System.Linq.Queryable.Intersect'的重载.

Could not parse expression 'value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[FilterLists.Data.Entities.Rule]).Intersect(__p_0, __p_1)': This overload of the method 'System.Linq.Queryable.Intersect' is currently not supported.

撰写此查询的最佳方法是什么?请注意，它位于DbContext交互链中，因此我希望将返回类型保留为IQueryable，以启用EF的惰性查询组合.

What is the best way to compose this query? Note that it is in a chain of DbContext interactions, so I'd prefer to keep the return type as an IQueryable to enable EF's lazy query composition.

GitHub上的上下文

更新:有关为什么我怀疑Contains()方法无效的更多信息:

Update: More info on why I suspected the Contains() approach was not working:

这是使用查询的类.我看到如下异常，因为数据库中的Raw列具有唯一约束.我以为我的逻辑(在CreateNewRules()中使用Except())可以防止Rules中的任何行具有重复的Raw值，但是也许我的问题出在其他地方...

This is the class where the query is being used. I am seeing exceptions like below because the Raw column in the database has a unique constraint. I thought my logic (use of Except() in CreateNewRules()) would prevent any rows in Rules with duplicate Raw values, but maybe my issue lies elsewhere...

public class SnapshotBatch
{
    private readonly FilterListsDbContext dbContext;
    private readonly IEnumerable<string> lines;
    private readonly Data.Entities.Snapshot snapEntity;

    public SnapshotBatch(FilterListsDbContext dbContext, IEnumerable<string> lines,
        Data.Entities.Snapshot snapEntity)
    {
        this.dbContext = dbContext;
        this.lines = lines;
        this.snapEntity = snapEntity;
    }

    public async Task SaveAsync()
    {
        var existingRules = GetExistingRules();
        var newRules = CreateNewRules(existingRules);
        dbContext.Rules.AddRange(newRules);
        var rules = existingRules.Concat(newRules);
        AddSnapshotRules(rules);
        await dbContext.SaveChangesAsync();
    }

    private IQueryable<Rule> GetExistingRules() =>
        dbContext.Rules.Where(r => lines.Contains(r.Raw));

    private List<Rule> CreateNewRules(IQueryable<Rule> existingRules) =>
        lines.Except(existingRules.Select(r => r.Raw))
             .Select(r => new Rule {Raw = r})
             .ToList();

    private void AddSnapshotRules(IQueryable<Rule> rules) =>
        snapEntity.AddedSnapshotRules
                  .AddRange(rules.Select(r => new SnapshotRule {Rule = r}));
}

来自StackTrace异常的片段(其中'### Meebo:AdElement.Root'是Rules表中Raw的样本值):

Snippet from exception StackTrace (where '###Meebo:AdElement.Root' is a sample value for Raw in the Rules table):

FilterLists.Services.Snapshot.Snapshot.TrySaveAsync()在 /home/travis/build/collinbarrett/FilterLists/src/FilterLists.Services/Snapshot/Snapshot.cs:line 43的键"IX_rules_Raw"的条目"### Meebo:AdElement.Root"重复 MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) C:\ projects \ mysqlconnector \ src \ MySqlConnector \ MySql.Data.MySqlClient \ MySqlDataReader.cs:line 93 at

FilterLists.Services.Snapshot.Snapshot.TrySaveAsync() in /home/travis/build/collinbarrett/FilterLists/src/FilterLists.Services/Snapshot/Snapshot.cs:line 43 Duplicate entry '###Meebo:AdElement.Root' for key 'IX_rules_Raw' at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 93 at

更新2 :我相当确定我在Contains()中遇到的问题是由于

Update 2: I am fairly sure the issue I was seeing with Contains() was due to this issue that has an active PR. Because my strings have all kinds of special characters, I think they were not getting escaped properly when passed into Contains(), but they seem to be properly escaped as parameters in a Join().

推荐答案

不要忘记，当您将linQ与EFCore和IQueryable一起使用时，它将在Sql语句中转换c#代码.

Do not forget that when you use linQ with EFCore and IQueryable, it translate the c# code in Sql statements.

您尝试过这个吗?

var query = from rule in dbContext.Rules
            join line in lines
                on rule.Raw equals line
            select rule;

这篇关于从DbSet< TEntity>获取所有实体.具有IEnumerable中的属性值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！