代码之家 › 专栏 › 技术社区 › markzzz

如何指定另一个键?还是以更快的方式实现两个巨大列表的差异<>?

linq performance .net c#

markzzz · 技术社区 · 7 年前

我有一份清单 AE_AlignedPartners 数据库中的项,我使用以下方法检索:

List<AE_AlignedPartners> ae_alignedPartners_olds = ctx.AE_AlignedPartners.AsNoTracking().ToList();

List<AE_AlignedPartners> ae_alignedPartners_news = GetJSONPartnersList();

然后我得到了两者的交叉点:

var IDSIntersections = (from itemNew in ae_alignedPartners_news
                        join itemOld in ae_alignedPartners_olds on itemNew.ObjectID equals itemOld.ObjectID
                        select itemNew).Select(p => p.ObjectID).ToList();

现在,由于这些交叉点,我需要创建两个新列表,其中包括添加的项目(ae_alignedPartners_news-交叉点)和删除的项目(ae_alignedPartners_olds-Interestions)。代码如下:

// to create
IList<AE_AlignedPartners> ae_alignedPartners_toCreate = ae_alignedPartners_news.Where(p => !IDSIntersections.Contains(p.ObjectID)).ToList();

// to delete
IList<AE_AlignedPartners> ae_alignedPartners_toDelete = ae_alignedPartners_olds.Where(p => !IDSIntersections.Contains(p.ObjectID)).ToList();

有没有一种 Except<> 指定需要比较的密钥?就我而言,它不是 p.ID (哪个是 Primary Key 在数据库上),但是 p.ObjectID .

或者其他更快的方法?

2 回复 | 直到 7 年前

Murray Foxcroft 7 年前

有一个 Except 可与自定义比较器一起使用的函数:

    class PartnerComparer : IEqualityComparer<AE_AlignedPartners>
    {
        // Partners are equal if their ObjectID's are equal.
        public bool Equals(AE_AlignedPartners x, AE_AlignedPartners y)
        {         
            //Check whether the partner's ObjectID's are equal.
            return x.ObjectID == y.ObjectID;
        }

        public int GetHashCode(AE_AlignedPartners ap) {
            return ap.ObjectId.GetHashCode();
        }
    }

   var intersect = ae_alignedPartners_news.Intersect(ae_alignedPartners_olds);
   var creates = ae_alignedPartners_news.Except(intersect, new PartnerComparer);
   var deletes = ae_alignedPartners_old.Except(intersect, new PartnerComparer);

这将合理地提高性能。

Harald Coppoolse 7 年前

您不需要内部联接,您需要主键上的完全外部联接。LINQ不知道完整的外部联接,但很容易用函数扩展IEnumerable。

StackOverlow: LINQ full outer join ,我采用了使用延迟执行的解决方案。此解决方案仅在KeySelector使用唯一键时有效。

    public static IEnumerable<TResult> FullOuterJoin<TA, TB, TKey, TResult>(
        this IEnumerable<TA> sequenceA,
        IEnumerable<TB> sequenceB,
        Func<TA, TKey> keyASelector, 
        Func<TB, TKey> keyBSelector,
        Func<TKey, TA, TB, TResult> resultSelector,
        IEqualityComparer<TKey> comparer)
{
    if (comparer == null) comparer = EqualityComparer<TKey>.Default;

    // create two lookup tables:
    var alookup = a.ToLookup(selectKeyA, comparer);
    var blookup = b.ToLookup(selectKeyB, comparer);

    // all used keys:
    var aKeys = alookup.Select(p => p.Key);
    var bKeys = blookup.Select(p => p.Key);
    var allUsedKeys = aKeys.bKeys.Distinct(comparer);

    // for every used key:
    // get the values from A with this key, or default if it is not a key used by A
    // and the value from B with this key, or default if it is not a key used by B
    // put the key, and the fetched values in the ResultSelector
    foreach (TKey key in allUsedKeys)
    {
        TA fetchedA = aLookup[key].FirstOrDefault();
        TB fetchedB = bLookup[key].FirstOrDefault();
        TResult result = ResultSelector(key, fetchedA, fetchedB);
        yield result;
    }

B中的值而非A中的值:(null,B)=>必须删除
A和B中的值:(A,B)=>需要进一步检查以确定是否需要更新

IEnumerable<AlignedPartners> olds = ...
IEnumerable<AlignedPartners> news = ...

var joinResult = olds.FullOuterJoin(news, // join old and new
    oldItem => oldItem.Id,                // from every old take the Id
    newItem => newItem.Id,                // from every new take the Id
    (key, oldItem, newItem) => new        // when they match make one new object
    {                                     // containing the following properties
         OldItem = oldItem,
         NewItem = newItem,
    });

注意:到目前为止,还没有列举任何内容!

foreach (var joinedItem in joinResult)
{
    if (joinedItem.OldItem == null)
    {
        // we won't have both items null, so we know NewItem is not null
        AddItem(joinedItem.NewItem);
    }
    else if (joinedItem.NewItem == null)
    {   // old not null, new equals null
        DeleteItem(joinedItem.OldItem);
    }
    else
    {  // both old and new not null, if desired: check if update needed
        if (!comparer.Equals(old, new))
        {   // changed
            UpdateItems(old, new)
        }
    }
}