More efficient ways for foreach loop to add database Items to results List?

Question

Is there a more efficient way to add the foundItems to the resultList (for example with more efficient LINQ query / more efficient foreach loop)?

            // Ex: Local List
            var localSysList1 = new List<SomeType1>()
            {
                new SomeType1 { Id = "1", Name = "Spinach", Value = "TXT_FLD_SPINA", ExtraInfo = "something1" },
                new SomeType1 { Id = "2", Name = "Broccoli", Value = "TXT_FLD_BRO", ExtraInfo = "something else5" },
                new SomeType1 { Id = "3", Name = "Wheatgrass", Value = "TXT_FLD_WHE", ExtraInfo = "something else4" },
            };

            // Ex: Retrieved from DbContext
            var databaseList = new List<SomeType1>()
            {
                new SomeType1 { Id = "1", Name = "Spinach", Value = "TXT_FLD_SPINA", ExtraInfo = "Some additional info" },
                new SomeType1 { Id = "4", Name = "Banana", Value = "TXT_FLD_BANA", ExtraInfo = "something else" },
                new SomeType1 { Id = "5", Name = "Tomatoes", Value = "TXT_FLD_TOM", ExtraInfo = "something else2" },
            };

            List<SomeType1> resultList = new List<SomeType1>();
            foreach (var localItem in localSysList1)
            {
                var foundItems = databaseList.Where(x => x.Id == localItem.Id);
                resultList.Add(foundItems);
            }

Why would you add items where the Ids match? Typically you'd update those. You'd add ones where the Ids don't match (i.e., are not already in the database). — Heretic Monkey, Oct 01 '21 at 23:11
Sounds like a LINQ intersect based on property - [this](https://stackoverflow.com/a/10633506/6650475) should help. — Andrew S, Oct 01 '21 at 23:19
LINQ usually used for first-code fast approach rather than efficiency. Never saw ORM which would outperform stored procedure with fixed plan, or bulked insert/update. — eocron, Oct 01 '21 at 23:51
Heretic: Correct, I wrote some example code, it should actually match on the Value. — Jay, Oct 02 '21 at 07:23
Andre: can Intersect also be used when the types don't match, but one of the properties (Id) does? — Jay, Oct 02 '21 at 08:03
It's hard to offer improvements to code that doesn't compile: `resultList.Add(foundItems)`. — Gert Arnold, Oct 02 '21 at 18:49

score 0 · Answer 1 · answered Oct 02 '21 at 18:15

Write your own equality comparer.

It will be much more quicker than the .Where call for every object in the loop. Since you have a local list, it seems that ORM optimisations aren't really what you are looking for.

This should work.

Main.cs

...
var eqComparer = new SomeType1EqualityComparer();
var resultList = localSysList1.Intersect(databaseList, eqComparer).ToList();

SomeType1EqualityComparer.cs

public class SomeType1EqualityComparer : IEqualityComparer<SomeType1>
    {
        public bool Equals(SomeType1 x, SomeType1 y)
        {
            if (ReferenceEquals(x, y))
                return true;

            if (ReferenceEquals(x, null))
                return false;
            
            if (ReferenceEquals(y, null))
                return false;

            if (x.GetType() != y.GetType())
                return false;
            
            return x.Id == y.Id && x.Name == y.Name && x.Value == y.Value && x.ExtraInfo == y.ExtraInfo;
        }

        public int GetHashCode(SomeType1 obj)
        {
            return HashCode.Combine(obj.Id, obj.Name, obj.Value, obj.ExtraInfo);
        }
    }

More efficient ways for foreach loop to add database Items to results List?

1 Answers1