C# Update or insert from a List by some criteria

Question

I have 2 lists, both with a different number of items, both with 1 parameter in common that I have to compare. If the value of the parameter is the same I have to update the DB, but if the item in a list doesn't have an item in the second list, I have to insert it into the DB.

This is what I was trying:

foreach (var rep in prodrep)
{
    foreach (var crm in prodcrm)
    {
        if (rep.VEHI_SERIE.Equals(crm.VEHI_SERIE))
        {
            updateRecord(rep.Data);

        } 
        else 
        {
            insertRecords(rep.Data);
        }
    }  
}

The first problem with this is that it is very slow. The second problem is that obviously the insert statement would't work, but I don't want to do another for each inside a foreach to verify if it doesn't exist, because that would take double the time.

How can I make this more efficient?

Try creating a join between the two sets. An inner join will give you those with the same ID; a left join looking for records on one side with a null ID will give you those that do not having a common ID. — Peter Smith, Jul 05 '17 at 22:21

score 0 · Answer 1 · answered Jul 05 '17 at 22:22

0

var comparators = prodcrm.Select(i => i.VEHI_SERIE).ToList();
foreach (var rep in prodrep)
{
  if (comparators.Contains(rep.VEHI_SERIE)
    // do something
  else
    // do something else
}

answered Jul 05 '17 at 22:22

Jonathan

4,916
2
20
37

I'm pretty sure `Contains` uses a for loop under the hood, so this probably doesn't do anything but add the overhead of LINQ – Jeff Woodard Jul 05 '17 at 22:29
Didn't now you could do something like this, but if the comparator is true, can I send the item as rep.x? meaning that that item is the one that exists? and update it, right? – elunap Jul 05 '17 at 22:30
@JeffWoodard that's probably true, but I'm not sure how rich the prodcrm object is. At least this way, you're only enumerating that object collection once, and then you have a (possibly) lighter-weight string array from that point. I also find it more readable, but that could be personal. – Jonathan Jul 05 '17 at 23:31

Ylli Prifti · Accepted Answer · 2017-07-05T22:47:05.187

0

This is now not as efficient but this should work.

var existing = prodrep.Where(rep => prodcrm.Any(crm => rep.VEHI_SERIE.Equals(crm.VEHI_SERIE)).Select(rep=> Rep = rep, Crm=prodcrm.FirstOrDefault(crm=>rep.VEHI_SERIE.Equals(crm.VEHI_SERIE));

existing.ForEach(mix=>updateRecord(mix.Rep.Data, mix.Crm.Id));

prodrep.Where(rep => !existing.Any(mix=>mix.Rep==rep)).ForEach(rep=>insertRecords(rep.Data));

edited Jul 05 '17 at 22:47

answered Jul 05 '17 at 22:31

Ylli Prifti

323
3
11

This is awesome! just one thing, on the update I also have to send an item from the crm like, updateRecord(rep.Data, crm.Id), how can I do that? – elunap Jul 05 '17 at 22:39

score 0 · Answer 3 · answered Jul 05 '17 at 22:38

see Algorithm to optimize nested loops

it's an interesting read, and a cool trick, however not necessarily something that you should apply in every situation.

Also, be careful about answers providing you with LINQ queries. often it "looks cool" because you're not using the word "for", but really it's just hiding those for loops under the hood.

If you're really concerned about performance and the computer can handle it, you can look at the Task Parallel Library. it's not necessarily going to solve all of your problems, because you can be limited by processor/memory and you could end up making your application slower.

Is this something that a user of your application is going to be regularly doing? If so, is it something you can you make it an asynchronous task that they can come back to later, or is it an offline process that they aren't ever going to see. Depending on usage expectations, sometimes the time something takes isn't the end of the world.

https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/linq-to-objects — Jeff Woodard, Jul 05 '17 at 22:45

C# Update or insert from a List by some criteria

3 Answers3