给定一个ID列表，我可以通过以下方式查询所有相关行：
context.Table.Where(q => listOfIds.Contains(q.Id));
但是当表具有复合主键时，如何实现相同的功能呢？
EntityFramework - 包含复合主键的查询
69
- sternr
6
3我喜欢EntityFramework，也喜欢复合键，但是我不喜欢它们在一起。EntityFramework显然是为简单的代理键而建立的。 - Kirk Broadhurst
我认为这不是正确的问题，因为这在SQL中并不自然。你会如何在后来的语言中处理它？ - Daniel Lobo
任何来到这里的人：我推荐这个答案。 - Gert Arnold
也值得查看这里发布的答案（https://dev59.com/lWIj5IYBdhLWcg3wOCuS）。 - Saket Kumar
1@GertArnold 我通常同意你的观点，但是你推荐的答案虽然不错（尽管我见过其他类似的开源软件包），但它是针对SqlServer特定的，不能普遍使用。所以在没有EF Core集成支持的情况下，我仍然认为你的答案是最完整和普适的，并且我一直都会将其作为EF Core标签中类似问题的重复目标来使用。 - Ivan Stoev
1@Ivan 是的，当然它开始时说它是特定于 SQL Server 的，但我本可以提到那一点。在那种情况下，它工作得相当不错（尽管个人而言，我对在生产中使用这样的包持谨慎态度。再次强调，我们希望 EF 自己能支持这一点）。 - Gert Arnold
12个回答
87
这是一个很棘手的问题，我不知道有什么优雅的解决方案。
假设你有一些键盘组合，并且你只想选择标记了星号（*）的那些。
Id1 Id2
--- ---
1 2 *
1 3
1 6
2 2 *
2 3 *
... (many more)
如何以一种 Entity Framework 乐意的方式进行操作？让我们看看一些可能的解决方案，看看它们是否可行。
解决方案1: 使用带有对 (Join) 或 包含 (Contains) 的对
最好的解决方案是创建一个您想要的对的列表，例如元组 (List<Tuple<int,int>>)，并将数据库数据与此列表连接：
from entity in db.Table // db is a DbContext
join pair in Tuples on new { entity.Id1, entity.Id2 }
equals new { Id1 = pair.Item1, Id2 = pair.Item2 }
select entity
在LINQ to objects中，这是完美的解决方案，但不幸的是，EF会抛出一个异常，如下所示：
无法创建类型为“System.Tuple`2（...）”的常量值。 在此上下文中仅支持原始类型或枚举类型。
这种方式比较啰嗦地告诉您它无法将此语句转换为SQL，因为Tuples不是原始值的列表（例如int或string）。 出于同样的原因，使用Contains（或任何其他LINQ语句）的类似语句也会失败。
解决方案2：内存中
当然，我们可以将问题转化为简单的LINQ to objects，如下所示：
from entity in db.Table.AsEnumerable() // fetch db.Table into memory first
join pair Tuples on new { entity.Id1, entity.Id2 }
equals new { Id1 = pair.Item1, Id2 = pair.Item2 }
select entity
毫无疑问，这并不是一个好的解决方案。db.Table 可能包含数百万条记录。
解决方案3：两个Contains语句（不正确）
因此，让我们向 EF 提供两个基本值列表，[1,2] 用于 Id1 和 [2,3] 用于 Id2。我们不想使用连接，因此让我们使用 Contains：
from entity in db.Table
where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2)
select entity
但是现在结果中也包含实体{1,3}！当然，这个实体完全匹配这两个谓词。但让我们记住，我们正在接近目标。我们现在只获取了其中四个实体，而不是将数百万实体全部加载到内存中。
解决方案4：使用计算值的一个Contains
解决方案3失败是因为两个独立的Contains语句不仅过滤它们值的组合。如果我们首先创建一个组合列表并尝试匹配这些组合会怎么样呢？我们从方案1中知道该列表应该包含基本值。例如：
var computed = ids1.Zip(ids2, (i1,i2) => i1 * i2); // [2,6]
以及这个LINQ语句：
from entity in db.Table
where computed.Contains(entity.Id1 * entity.Id2)
select entity
这种方法存在一些问题。首先，你会发现它也返回了实体{1,6}。组合函数（a*b）不能产生唯一标识数据库中一对数据的值。现在我们可以创建一个字符串列表，例如["Id1=1,Id2=2","Id1=2,Id2=3]"并执行以下操作：
from entity in db.Table
where computed.Contains("Id1=" + entity.Id1 + "," + "Id2=" + entity.Id2)
select entity
（这种方法可以在EF6中使用，但是在早期版本中不可行）。
问题变得有些混乱了。但更重要的是，这个解决方案不是sargable的，这意味着它绕过了在Id1和Id2上可能被使用的任何数据库索引。这会导致非常非常低效的性能。
解决方案5：方案2和3的结合
所以我能想到的最可行的解决方案是将Contains和内存中的join组合起来：首先像解决方案3一样执行包含语句。记住，它让我们离目标非常接近。然后通过将结果作为内存列表连接来精炼查询结果：
var rawSelection = from entity in db.Table
where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2)
select entity;
var refined = from entity in rawSelection.AsEnumerable()
join pair in Tuples on new { entity.Id1, entity.Id2 }
equals new { Id1 = pair.Item1, Id2 = pair.Item2 }
select entity;
这不是一种优雅的方法，可能很混乱，但目前我找到并在我的代码中应用的唯一可扩展1解决方案。
解决方案6：使用OR子句构建查询
使用像Linqkit或其他可选工具一样的Predicate builder，您可以构建一个查询，其中包含列表组合中每个元素的OR子句。对于非常短的列表，这可能是一个可行的选择。但是，对于有几百个元素的列表，查询性能将开始变得非常差。因此，除非您可以100%确定始终只有少量元素，否则我不认为这是一种好的解决方案。关于此选项的一个详细解释可以在这里找到。
解决方案7：联合查询
还有一种使用UNION的解决方案，我稍后在这里发布了。
1就“Contains”语句而言是可扩展的：针对SQL后端的可扩展LINQ Contains方法
- Gert Arnold
7
1这（解决方案5）是我最终采取的方法，但这感觉像是一种可怕的做法... - sternr
1问题的根源在于我们必须处理一个非常过时的语言规范(SQL)，它从未提供一种方法来在一个语句中连接临时多维列表(就像我们可以通过
IN语句来处理简单列表)。虽然有特定于RDBMS的解决方法或修复方法(Oracle有一个非常好的解决方案)，但EF可能不会投资于实现这些方法。 - Gert Arnold非常感谢您提供这个非常有见地的答案。 - Dirk Boer
假设这是一种替代方案。“更好”意味着一种比较，我无法为每个人做出。 - Gert Arnold
显示剩余2条评论
10
使用SQL Server解决实体框架核心问题
以下解决方案利用了QueryableValues。这是一个我编写的库，主要解决由使用Contains LINQ 方法合成本地值导致的查询计划缓存污染所造成的 SQL Server 问题。它还允许您以高效的方式在查询中组合复杂类型的值，从而实现此问题中所需的功能。
首先，您需要安装和设置库，之后您可以使用以下任何模式之一来查询您的实体，使用复合键：
// Required to make the AsQueryableValues method available on the DbContext.
using BlazarTech.QueryableValues;
// Local data that will be used to query by the composite key
// of the fictitious OrderProduct table.
var values = new[]
{
new { OrderId = 1, ProductId = 10 },
new { OrderId = 2, ProductId = 20 },
new { OrderId = 3, ProductId = 30 }
};
// Optional helper variable (needed by the second example due to CS0854)
var queryableValues = dbContext.AsQueryableValues(values);
// Example 1 - Using a Join (preferred).
var example1Results = dbContext
.OrderProduct
.Join(
queryableValues,
e => new { e.OrderId, e.ProductId },
v => new { v.OrderId, v.ProductId },
(e, v) => e
)
.ToList();
// Example 2 - Using Any (similar behavior as Contains).
var example2Results = dbContext
.OrderProduct
.Where(e => queryableValues
.Where(v =>
v.OrderId == e.OrderId &&
v.ProductId == e.ProductId
)
.Any()
)
.ToList();
有用的链接
QueryableValues 在MIT许可下分发。
- yv989c
5
1我认为这是解决这个问题的最佳方案。@sternr，如果你还在，请标记它为已接受，以便更容易找到。 - Gert Arnold
非常感谢您提供这个优秀而又优雅的库！我有一个问题。我一直在使用自己开发的解决方案，虽然它有点繁琐，但它可以让我的查询受益于索引查找。我已经阅读了您的库的GitHub和Nuget页面，但我无法确定您的解决方案是否也使用了类似索引的东西。它使用了吗？无论如何，我最终都会使用它，但我认为在某些情况下，您的库的优雅和简洁可能不足以证明错过索引查找的价值。 - Daniel
1嗨@Daniel。我很高兴这有帮助。
QueryableValues是从头开始为性能而设计的，因此，生成的查询将受益于任何可用于覆盖您的查询的索引。只需避免进行任何将破坏索引使用的即时转换即可。处理字符串（例如varchar，nvarchar）时，请仔细阅读相关AsQueryableValues方法的提供文档。为了确定，我建议您运行一次配置文件会话，捕获生成的查询，然后在SSMS中运行它以显示执行计划，以便确认。 - yv989c@yv989c，你使用JOIN语法进行评论是更受欢迎的，但我发现使用WHERE ANY语法可以更明显地表达意图。使用JOIN语法是否有性能优势，使你将其标记为首选呢？ - undefined
1@ChrisDaMour，每个方法生成的SQL语句都不同。LINQ的
Join方法会生成一个简单的SQL JOIN，而Where和Any的组合会生成更复杂的SQL，通常依赖于EXISTS运算符，其性能不一定与简单的SQL JOIN相同。归根结底，SQL服务器的查询引擎负责优化查询的执行计划，在我的经验中，SQL越简单，它执行得越好。 - undefined6
您可以针对每个复合主键使用 Union ：
var compositeKeys = new List<CK>
{
new CK { id1 = 1, id2 = 2 },
new CK { id1 = 1, id2 = 3 },
new CK { id1 = 2, id2 = 4 }
};
IQuerable<CK> query = null;
foreach(var ck in compositeKeys)
{
var temp = context.Table.Where(x => x.id1 == ck.id1 && x.id2 == ck.id2);
query = query == null ? temp : query.Union(temp);
}
var result = query.ToList();
- Slava Utesinov
4
我可以问一下，这种方法有什么弱点吗？在我看来，这是一个很好的解决方案，应该被接受为答案。有人能看出这个答案的缺点吗？ - Sam
1@Sam，它将为每个复合键构建单独的SQL查询，然后联合结果——这是该方法的最弱点——性能。 - lissajous
@H.Wojtowicz，针对每个复合键不会有单独的SQL查询，而是将是一个由多个子查询组成的一个查询。 - Slava Utesinov
1@SlavaUtesinov 我表述不够准确。我是指联合操作发生在 SQL Server 端。我的意思是，查询语句过长是不必要的，因为所有所需条件都可以放在一个 SQL WHERE 语句中——请参见此处。 - lissajous
3
您可以按以下方式创建包含键的字符串集合（假设您的键是int类型）：
然后您可以在数据库中使用“包含”：
var id1id2Strings = listOfIds.Select(p => p.Id1+ "-" + p.Id2);
然后您可以在数据库中使用“包含”：
using (dbEntities context = new dbEntities())
{
var rec = await context.Table1.Where(entity => id1id2Strings .Contains(entity.Id1+ "-" + entity.Id2));
return rec.ToList();
}
- juanora
1
3正如 Gert Arnord 在他的评论中提到的那样，“这个解决方案不是 sargable，也就是说：它绕过了可能本来可以使用的对 Id1 和 Id2 的数据库索引。” - thefolenangel
1
我也遇到了这个问题，需要一个既不执行表扫描又提供精确匹配的解决方案。可以通过结合Gert Arnold's Answer中的解决方案3和解决方案4来实现。
var firstIds = results.Select(r => r.FirstId);
var secondIds = results.Select(r => r.SecondId);
var compositeIds = results.Select(r => $"{r.FirstId}:{r.SecondId}");
var query = from e in dbContext.Table
//first check the indexes to avoid a table scan
where firstIds.Contains(e.FirstId) && secondIds.Contains(e.SecondId))
//then compare the compositeId for an exact match
//ToString() must be called unless using EF Core 5+
where compositeIds.Contains(e.FirstId.ToString() + ":" + e.SecondId.ToString()))
select e;
var entities = await query.ToListAsync();
- Justin
1
对于 EF Core，我使用稍微修改过的 EricEJ 的 bucketized IN 方法 来将复合键映射为元组。它在小数据集上表现良好。
示例用法
List<(int Id, int Id2)> listOfIds = ...
context.Table.In(listOfIds, q => q.Id, q => q.Id2);
Implementation
public static IQueryable<TQuery> In<TKey1, TKey2, TQuery>(
this IQueryable<TQuery> queryable,
IEnumerable<(TKey1, TKey2)> values,
Expression<Func<TQuery, TKey1>> key1Selector,
Expression<Func<TQuery, TKey2>> key2Selector)
{
if (values is null)
{
throw new ArgumentNullException(nameof(values));
}
if (key1Selector is null)
{
throw new ArgumentNullException(nameof(key1Selector));
}
if (key2Selector is null)
{
throw new ArgumentNullException(nameof(key2Selector));
}
if (!values.Any())
{
return queryable.Take(0);
}
var distinctValues = Bucketize(values);
if (distinctValues.Length > 1024)
{
throw new ArgumentException("Too many parameters for SQL Server, reduce the number of parameters", nameof(values));
}
var predicates = distinctValues
.Select(v =>
{
// Create an expression that captures the variable so EF can turn this into a parameterized SQL query
Expression<Func<TKey1>> value1AsExpression = () => v.Item1;
Expression<Func<TKey2>> value2AsExpression = () => v.Item2;
var firstEqual = Expression.Equal(key1Selector.Body, value1AsExpression.Body);
var visitor = new ReplaceParameterVisitor(key2Selector.Parameters[0], key1Selector.Parameters[0]);
var secondEqual = Expression.Equal(visitor.Visit(key2Selector.Body), value2AsExpression.Body);
return Expression.AndAlso(firstEqual, secondEqual);
})
.ToList();
while (predicates.Count > 1)
{
predicates = PairWise(predicates).Select(p => Expression.OrElse(p.Item1, p.Item2)).ToList();
}
var body = predicates.Single();
var clause = Expression.Lambda<Func<TQuery, bool>>(body, key1Selector.Parameters[0]);
return queryable.Where(clause);
}
class ReplaceParameterVisitor : ExpressionVisitor
{
private ParameterExpression _oldParameter;
private ParameterExpression _newParameter;
public ReplaceParameterVisitor(ParameterExpression oldParameter, ParameterExpression newParameter)
{
_oldParameter = oldParameter;
_newParameter = newParameter;
}
protected override Expression VisitParameter(ParameterExpression node)
{
if (ReferenceEquals(node, _oldParameter))
return _newParameter;
return base.VisitParameter(node);
}
}
/// <summary>
/// Break a list of items tuples of pairs.
/// </summary>
private static IEnumerable<(T, T)> PairWise<T>(this IEnumerable<T> source)
{
var sourceEnumerator = source.GetEnumerator();
while (sourceEnumerator.MoveNext())
{
var a = sourceEnumerator.Current;
sourceEnumerator.MoveNext();
var b = sourceEnumerator.Current;
yield return (a, b);
}
}
private static TKey[] Bucketize<TKey>(IEnumerable<TKey> values)
{
var distinctValueList = values.Distinct().ToList();
// Calculate bucket size as 1,2,4,8,16,32,64,...
var bucket = 1;
while (distinctValueList.Count > bucket)
{
bucket *= 2;
}
// Fill all slots.
var lastValue = distinctValueList.Last();
for (var index = distinctValueList.Count; index < bucket; index++)
{
distinctValueList.Add(lastValue);
}
var distinctValues = distinctValueList.ToArray();
return distinctValues;
}
- joakimriedel
1
你需要一组对象来代表你想查询的键。
如果你有两个列表，仅检查每个值是否出现在它们各自的列表中，那么你得到的是列表的笛卡尔积——这可能不是你想要的。相反，你需要查询所需的特定组合。
你可以创建一个同构函数（质数很适合这个），类似于哈希码，用于比较一对值。只要乘法因子互质，此模式就是同构的（一对一）-即
class Key
{
int Id1 {get;set;}
int Id2 {get;set;}
如果你有两个列表，仅检查每个值是否出现在它们各自的列表中，那么你得到的是列表的笛卡尔积——这可能不是你想要的。相反，你需要查询所需的特定组合。
List<Key> keys = // get keys;
context.Table.Where(q => keys.Any(k => k.Id1 == q.Id1 && k.Id2 == q.Id2));
我不完全确定这是否是Entity Framework的有效使用方式；您可能会在将Key类型发送到数据库时遇到问题。如果发生这种情况，您可以有创意：
var composites = keys.Select(k => p1 * k.Id1 + p2 * k.Id2).ToList();
context.Table.Where(q => composites.Contains(p1 * q.Id1 + p2 * q.Id2));
你可以创建一个同构函数（质数很适合这个），类似于哈希码，用于比较一对值。只要乘法因子互质，此模式就是同构的（一对一）-即
p1*Id1 + p2*Id2的结果将唯一地标识Id1和Id2的值，只要正确选择了质数。但是，你最终会遇到一种情况，即你正在实现复杂的概念，而某些人将不得不支持它。最好编写一个存储过程，以接受有效的密钥对象。
- Kirk Broadhurst
2
1我需要检查一下你的第一个解决方案，但是对于第二个解决方案——虽然它可以工作，但会导致全表扫描，而不是使用直接针对键的查询。 - sternr
@sternr 完全正确。这很不愉快。请注意我的最后一句话，你最好编写一个存储过程。 - Kirk Broadhurst
0
我最终编写了一个依赖于System.Linq.Dynamic.Core的辅助程序来解决这个问题；
这是很多代码，目前没有时间重构，但欢迎提供输入/建议。
public static IQueryable<TEntity> WhereIsOneOf<TEntity, TSource>(this IQueryable<TEntity> dbSet,
IEnumerable<TSource> source,
Expression<Func<TEntity, TSource,bool>> predicate) where TEntity : class
{
var (where, pDict) = GetEntityPredicate(predicate, source);
return dbSet.Where(where, pDict);
(string WhereStr, IDictionary<string, object> paramDict) GetEntityPredicate(Expression<Func<TEntity, TSource, bool>> func, IEnumerable<TSource> source)
{
var firstP = func.Parameters[0];
var binaryExpressions = RecurseBinaryExpressions((BinaryExpression)func.Body);
var i = 0;
var paramDict = new Dictionary<string, object>();
var res = new List<string>();
foreach (var sourceItem in source)
{
var innerRes = new List<string>();
foreach (var bExp in binaryExpressions)
{
var emp = ToEMemberPredicate(firstP, bExp);
var val = emp.GetKeyValue(sourceItem);
var pName = $"@{i++}";
paramDict.Add(pName, val);
var str = $"{emp.EntityMemberName} {emp.SQLOperator} {pName}";
innerRes.Add(str);
}
res.Add( "(" + string.Join(" and ", innerRes) + ")");
}
var sRes = string.Join(" || ", res);
return (sRes, paramDict);
}
EMemberPredicate ToEMemberPredicate(ParameterExpression firstP, BinaryExpression bExp)
{
var lMember = (MemberExpression)bExp.Left;
var rMember = (MemberExpression)bExp.Right;
var entityMember = lMember.Expression == firstP ? lMember : rMember;
var keyMember = entityMember == lMember ? rMember : lMember;
return new EMemberPredicate(entityMember, keyMember, bExp.NodeType);
}
List<BinaryExpression> RecurseBinaryExpressions(BinaryExpression e, List<BinaryExpression> runningList = null)
{
if (runningList == null) runningList = new List<BinaryExpression>();
if (e.Left is BinaryExpression lbe)
{
var additions = RecurseBinaryExpressions(lbe);
runningList.AddRange(additions);
}
if (e.Right is BinaryExpression rbe)
{
var additions = RecurseBinaryExpressions(rbe);
runningList.AddRange(additions);
}
if (e.Left is MemberExpression && e.Right is MemberExpression)
{
runningList.Add(e);
}
return runningList;
}
}
辅助类：
public class EMemberPredicate
{
public readonly MemberExpression EntityMember;
public readonly MemberExpression KeyMember;
public readonly PropertyInfo KeyMemberPropInfo;
public readonly string EntityMemberName;
public readonly string SQLOperator;
public EMemberPredicate(MemberExpression entityMember, MemberExpression keyMember, ExpressionType eType)
{
EntityMember = entityMember;
KeyMember = keyMember;
KeyMemberPropInfo = (PropertyInfo)keyMember.Member;
EntityMemberName = entityMember.Member.Name;
SQLOperator = BinaryExpressionToMSSQLOperator(eType);
}
public object GetKeyValue(object o)
{
return KeyMemberPropInfo.GetValue(o, null);
}
private string BinaryExpressionToMSSQLOperator(ExpressionType eType)
{
switch (eType)
{
case ExpressionType.Equal:
return "==";
case ExpressionType.GreaterThan:
return ">";
case ExpressionType.GreaterThanOrEqual:
return ">=";
case ExpressionType.LessThan:
return "<";
case ExpressionType.LessThanOrEqual:
return "<=";
case ExpressionType.NotEqual:
return "<>";
default:
throw new ArgumentException($"{eType} is not a handled Expression Type.");
}
}
}
使用方式如下：
// This can be a Tuple or whatever.. If Tuple, then y below would be .Item1, etc.
// This data structure is up to you but is what I use.
[FromBody] List<CustomerAddressPk> cKeys
var res = await dbCtx.CustomerAddress
.WhereIsOneOf(cKeys, (x, y) => y.CustomerId == x.CustomerId
&& x.AddressId == y.AddressId)
.ToListAsync();
希望这能帮助到其他人。
- ttugates
0
在没有通用解决方案的情况下，我认为有两件事需要考虑：
- 避免使用多列主键（这也会使单元测试更容易）。
- 但是如果必须使用，则很有可能其中一个将把查询结果大小减少到O(n)，其中n是理想查询结果的大小。从这里开始，采用Gerd Arnold上面的第5种解决方案。
例如，导致我提出这个问题的问题是查询订单行，其中关键字是订单ID + 订单行号 + 订单类型，并且源具有隐含的订单类型。也就是说，订单类型是一个常量，订单ID将减少相关订单的订单行查询集，并且每个订单通常只有5个或更少的订单行。
换句话说，如果您有一个复合键，则更改其中一个键的机会非常小。使用上述解决方案5。
- Robert Jørgensgaard Engdahl
0
我尝试了这个解决方案，它对我起作用了，输出查询结果完美无缺，没有任何参数。
using LinqKit; // nuget
var customField_Ids = customFields?.Select(t => new CustomFieldKey { Id = t.Id, TicketId = t.TicketId }).ToList();
var uniqueIds1 = customField_Ids.Select(cf => cf.Id).Distinct().ToList();
var uniqueIds2 = customField_Ids.Select(cf => cf.TicketId).Distinct().ToList();
var predicate = PredicateBuilder.New<CustomFieldKey>(false); //LinqKit
var lambdas = new List<Expression<Func<CustomFieldKey, bool>>>();
foreach (var cfKey in customField_Ids)
{
var id = uniqueIds1.Where(uid => uid == cfKey.Id).Take(1).ToList();
var ticketId = uniqueIds2.Where(uid => uid == cfKey.TicketId).Take(1).ToList();
lambdas.Add(t => id.Contains(t.Id) && ticketId.Contains(t.TicketId));
}
predicate = AggregateExtensions.AggregateBalanced(lambdas.ToArray(), (expr1, expr2) =>
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<CustomFieldKey, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
});
var modifiedCustomField_Ids = repository.GetTable<CustomFieldLocal>()
.Select(cf => new CustomFieldKey() { Id = cf.Id, TicketId = cf.TicketId }).Where(predicate).ToArray();
- Amro Samy
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接