Algorithm to add data from one list to another

Question

I have a list of objects as such:

someID: 1234   values: A, B
someID: 891    values: C

I have a datatable with some rows.

1234 D
891  E
1234 F

I want to add these values from the datatable to their respective objects in the list in-memory. So for example, I want to find the object with ID 1234 in the list and add values D and F to the object.

What would have the best performance?

Sort the datatable the iterate it through, search list each time
Store datatable into a hashtable/dictionary, then iterate through the list performing a dictionary lookup on each key
Something else?

Edit: not really a DB question, I'll try to make it more clear

You don't need to do anything with the datatagble other than iterate so no need to sort the datatable or put it in a dictionary. — paparazzo, Sep 23 '14 at 19:29

score 1 · Answer 1 · edited May 23 '17 at 11:49

You can use Linq-To-DataSet:

var both  = from row in dt.AsEnumerable()
            join objA in objectAList
            on row.Field<int>(0) equals objA.someID
            select new { row, objA };
foreach(var x in both)
{
    x.objA.values.Add(x.row.Field<string>(1)); // presuming an `IList<T>`
}

If it's not a list but for example an array, you can use:

x.objA.values = x.objA.values.Concat(new[]{x.row.Field<string>(1)}).ToArray();

Enumerable.Join uses a set under the hood so it's rather efficient.

Skeept Espinoza · Answer 2 · 2014-09-23T22:55:36.773

1

You just need to iterate through the rows, search up the id and add the value to it

foreach (DataRow item in datatable.Rows)
{
    <YourType> thing = list.Find(x => x.someID == (int)item[0]);
    if (thing == null)
    {
        List<string> values = new List<string>();
        values.Add(item[1].ToString());
        list.Add(new <YourType> { someID = (int)item[0], values = values }); 
    }
    else
    {
        thing.values.Add(item[1].ToString());
    }
}

edited Sep 23 '14 at 22:55

answered Sep 23 '14 at 22:49

Skeept Espinoza

11
2

What is the runtime of this and would it be better or worse than first putting it into a hashtable/dictionary then searching that? – cppNoob Sep 24 '14 at 21:40
Inserted 100000 random values with ids from 1 to 9, took 0.789 seconds to process them while writing them to the screen as well, I see no benefit in inserting them into a hashtable or dictionary since you can already iterate through Rows in the datable, the only benefit would be being able to reference the 'columns' by a name instead of the positions inside the row array – Skeept Espinoza Sep 26 '14 at 13:27

score 0 · Answer 3 · answered Sep 23 '14 at 19:24

For what you need, create primary key on the ID and do upinsert (update or insert is not exist) - so you will need to performa 1 operation for each ID and can create store procedure that append the new data.

From your question seem that you want to do bulk operation - meaning doing all those operation in memory and than update the database in 1 operation. For that you use hash table

Algorithm to add data from one list to another

3 Answers3