如何将通用EF Core实体与组合键进行比较? [英] How to compare generic EF Core entities with composite keys?

问题描述

我有一个问题，关于如何最好地将EF Core实体与组合键进行比较.

I have a question regarding how to best compare EF Core entities with a composite key.

因此，我最初提出此要求的原因是我目前正在使用通用存储库(是的，我知道人们对此有非常不同的看法)，并且想为实体列表.签名看起来像这样:

So, the reason why I initially came up with this requirement was that I am currently working on a generic repository (Yes, I know people have a very split up opinion of this), and wanted to implement a CreateOrUpdate method for a list of entities. The signature would look like this:

public async Task CreateOrUpdate(List<TEntity> entities, Action<TEntity> updateFunc)

因此，EF Core已经提供了 ValueTask< TEntity>.FindAsync(params object [] keyValues)方法，该方法基于一组键检索单个实体.但是，方法列表没有内置任何内容.请记住，如果未找到任何内容，FindAsync将执行数据库查询，因此这意味着调用500个项目可能意味着500个数据库查询，这在我看来是非常浪费的.

So, EF Core already offers the ValueTask<TEntity> FindAsync (params object[] keyValues) method, which retrieves a single entity based on a set of keys. However, there's nothing built in for a list of methods. Keep in mind, that FindAsync will do a DB query if nothing was found, so this means that e.g. calling this for 500 items might mean 500 DB queries, which is pretty wasteful in my opinion.

我当时想的是，我可以改写Equals并使用常规的.Where语句，从而使它有所不同，

What I was thinking instead was that I could instead to it a bit different, by overriding Equals and using a regular .Where statement, so something along those lines:

public async Task CreateOrUpdate(List<TEntity> entities, Action<TEntity> updateFunc)
{
    await using var ctx = CreateContext();

    var set = ctx.Set<TEntity>();

    var existingItem = await set.Where(x => entities.Contains(x)).ToListAsync();

    ...

}

所以，我现在想知道:这是正确的方法吗?我可以仅使我的所有TEntity类型覆盖Equals并这样做吗?可悲的是，我不能仅仅将我的所有TEntity类型限制为提供"Id"属性的接口.属性并进行比较，因为我也有一些提供复合键的实体.

So, I was now wondering: Is this a proper approach? Can I just make all my TEntity types override Equals and do it like this? Sadly I can't just constrain all my TEntity types to an interface which supplies an "Id" property and compare that, as I also have a few entities providing a composite key.

推荐答案

否， IEquatable 将无济于事.您必须创建巨大的谓词才能解决此问题.

No, IEquatable will not help. You have to create huge predicate to solve this problem.

基本上应该看起来像这样(很抱歉，从内存中写入)

Basically it should looks like this (sorry writing from memory)

public static IQueryable<T> FilterByPrimaryKey(this IQueryable<T> query, IEnumerable<T> items, Model model)
{
   var pk = model.FindEntityType(typeof(T))
      .FindPrimaryKey().Properties.Select(p => p.PropertyInfo).ToArray();

   var param = Expression.Parameter(typeof(T), "e");
   Expression predicate = null;

   foreach (var item in items)
   {
      var obj = Expression.Constant(item, typeof(T));
      var matchPredicate = pk.Select(p => 
        (Expression)Expression.Equal(
           Expression.MakeMemberAccess(param, p),
           Expression.MakeMemberAccess(obj, p)
        )).Aggregate((e1, e2) => Expression.AndAlso(e1, e2));

      predicate = predicate == null ? matchPredicate : Expression.OrElse(predicate, matchPredicate);
   }

   predicate ??= Expression.Equal(Expression.Constant(1), Expression.Constant(2))
 
   var lambda = Expression.Lambda(predicate, param);

   var whereExpr = Expression.Call(typeof(Queryable), "Where", 
      new[] {typeof(T)}, 
      query.Expression,
      lambda
   );
 
   return query.Provider.CreateQuery<T>(whereExpr);
}

但是，最后您会发现此解决方案具有很高的内存使用率和CPU消耗，并且可能在大项目列表上失败.

BUT, finally you will find that this solution has high memory usage and CPU consuming and probably will fail on big items list.

根据要求，我可以使用 linq2db.EntityFrameworkCore 提供最快的解决方案，它是 Merge运算符，并且可能是临时表的用法.

On request I can provide fastest solution using linq2db.EntityFrameworkCore and it's Merge operator and probably temporary table usage.

这篇关于如何将通用EF Core实体与组合键进行比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！