从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 Rule
s 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 Rule
s for all elements of lines
that do not currently exist. I was getting duplicate Rule
s 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.
更新:有关为什么我怀疑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()
中遇到的问题是由于有效PR .因为我的字符串具有各种特殊字符,所以我认为它们在传递到Contains()
时没有得到正确的转义,但是在Join()
中它们似乎可以作为参数正确地进行转义.
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屋!