与LINQ的更新对象更好的性能 [英] Better performance on updating objects with linq

问题描述

我有自定义对象的两个列表，并希望如果有其中在另一对场相匹配的其他列表中的对象，以更新在一个列表中的所有对象的字段

I have two lists of custom objects and want to update a field for all objects in one list if there is an object in the other list which matches on another pair of fields.

这代码说明问题更好，产生我想要的结果。然而，对于大名单20K和20K列表匹配的对象，这需要相当长的时间（31S）。我可以用〜使用泛型列表查找（谓语）方法改善这种50％

This code explains the problem better and produces the results I want. However for larger lists 20k, and a 20k list with matching objects, this takes a considerable time (31s). I can improve this with ~50% by using the generic lists Find(Predicate) method.

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
namespace ExperimentFW3
{
    public class PropValue
    {
    	public string Name;
    	public decimal Val;
    	public decimal Total;
    }
    public class Adjustment
    {
    	public string PropName;
    	public decimal AdjVal;
    }
    class Program
    {
    	static List<PropValue> propList;
    	static List<Adjustment> adjList;

    	public static void Main()
    	{
    		propList = new List<PropValue>{
    			new PropValue{Name = "Alfa", Val=2.1M},
    			new PropValue{Name = "Beta", Val=1.0M},
    			new PropValue{Name = "Gamma", Val=8.0M}
    		};
    		adjList = new List<Adjustment>{
    			new Adjustment{PropName = "Alfa", AdjVal=-0.1M},
    			new Adjustment{PropName = "Beta", AdjVal=3M}
    		};

    		foreach (var p in propList)
    		{
    			Adjustment a = adjList.SingleOrDefault(
    				av => av.PropName.Equals(p.Name)
    				);
    			if (a != null)
    				p.Total = p.Val + a.AdjVal;
    			else
    				p.Total = p.Val;
    		}
    	}
    }
}

的

期望的结果是：阿尔法总= 2，β=共4，伽玛总= 8

The desired result is: Alfa total=2,Beta total=4,Gamma total=8

但我不知道这是否可能以更快的速度做。内循环在结果超过20K项目时加入了两个列表需要很少的时间，甚至

But I wonder if this is possible to do even faster. Inner joining the two lists takes very little time, even when looping over 20k items in the resultset.

var joined = from p in propList
             join a in adjList on p.Name equals a.PropName
             select new { p.Name, p.Val, p.Total, a.AdjVal };

所以我的问题是，如果有可能做这样的事我会用T-SQL吗？从左边的UPDATE联接使用的调整值ISNULL（VAL，0）。

So my question is if it's possible to do something like I would do with T-SQL? An UPDATE from a left join using ISNULL(val,0) on the adjustment value.

推荐答案

这是加入应该是相当快的，因为它会遍历所有 adjList 的第一循环创建一个查询，然后在 proplist这样每个元素它将只使用查找。这比你的O（N * M）方法在更大的代码快 - 尽管这很容易被调用的 ToLookup （或的 ToDictionary 因为你只需要在一个值） adjList 循环之前

That join should be fairly fast, as it will first loop through all of adjList to create a lookup, then for each element in propList it will just use the lookup. This is faster than your O(N * M) method in the larger code - although that could easily be fixed by calling ToLookup (or ToDictionary as you only need one value) on adjList before the loop.

编辑：下面是使用修改后的代码 ToDictionary 。未经检验的，你要知道...

Here's the modified code using ToDictionary. Untested, mind you...

var adjDictionary = adjList.ToDictionary(av => av.PropName);
foreach (var p in propList)
{
    Adjustment a;
    if (adjDictionary.TryGetValue(p.Name, out a))
    {
        p.Total = p.Val + a.AdjVal;
    }
    else
    {
        p.Total = p.Val;
    }
}

这篇关于与LINQ的更新对象更好的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！