LINQ Query to compare two values of an object to a list, where both objects must match

Question

I have a list if bills, and I have a list of cars. Each car has two identifiers that are separate columns on the list of bills (car_init and car_nbr). I need to select the bills where the car identifiers (both initials and numbers) match.

For example, if I have car_a (car_init: ABC, car_nbr: 123) and car_b (car_init:DEF, car_nbr: 456) - I need to create a list of all of the bills that match car_a and car_b.

The list of bills can have over 70k rows, so I'm trying to find a way to not iterate through the entire list for every query - because the user can search for up to 200 cars in a single action.

Here is what I currently have, which is not having the desired results. It is currently return 0 results.

bills = bills
    .Where(w => request.CARS
        .Any(car => w.CarInit == car.CarInit && w.CarNbr == car.CarNbr))
    .ToList();

And you have definitely checked to see that you actually have at least one row with the values you're testing with? — Lasse V. Karlsen, Aug 24 '18 at 16:18
Change you output variable from 'bills' to something else so you do not loose you input. — jdweng, Aug 24 '18 at 16:18
Yes, I have verified that the input matches a row on the list. And I'm okay with losing any non-matching input (unless I'm missing something)... shouldn't bills be left with ONLY the matching rows? — Shaw, Aug 24 '18 at 16:53
Your use of rows and `List` confuses me - are you talking to a database or are you using an in-memory `List`? Also, you realize that using LINQ __is__ iterating through the `List`s? — NetMage, Aug 24 '18 at 17:57
Your LINQ looks correct to me, your issue must be somewhere else. — NetMage, Aug 24 '18 at 18:00
@NetMage Is it iterating the same way as a foreach statement? In my mind the LINQ statement would be faster. Also, I'm using the result list of a DB query. — Shaw, Aug 24 '18 at 18:20
LINQ to objects is never faster and often slower than using discrete logic. It is more succinct, and can be more understandable if you appreciate functional programming. Consider at a minimum that each lambda is a method call, but in a `foreach` you would just execute the lambda body directly and not create a method. — NetMage, Aug 24 '18 at 18:47
Basically an intersect seems to be what your looking for: https://stackoverflow.com/questions/2381049/intersect-linq-query — Kelly, Aug 24 '18 at 19:14

score 2 · Accepted Answer · answered Aug 24 '18 at 19:55

Here a solution with binarySearch (making O(log n) comparisons, where n is the number of elements in the array), I sort first both the bills and the cars on the car_init and then the car_nbr. Because they are both sorted, we know that we can move the index along with the start of the search what makes this even faster.

for 70k bills in max 17 comparisons we find the element, enjoy!

using System.Collections.Generic;

namespace ConsoleApp1
{


    class Program
    {

        static void Main(string[] args)
        {
            List<Numbers> bills = new List<Numbers>();
            List<Numbers> cars = new List<Numbers>();

            bills.Add(new Bill("ABC", "123"));
            bills.Add(new Bill("ABC", "654"));
            bills.Add(new Bill("WER", "123"));
            bills.Add(new Bill("ABC", "375"));
            bills.Add(new Bill("ABC", "762"));
            bills.Add(new Bill("WER", "792"));
            bills.Add(new Bill("DDR", "123"));
            bills.Add(new Bill("DEF", "123"));
            bills.Add(new Bill("DEF", "045"));
            bills.Add(new Bill("OLY", "123"));
            bills.Add(new Bill("ABC", "342"));
            bills.Add(new Bill("QWE", "874"));
            bills.Add(new Bill("ABC", "986"));
            bills.Add(new Bill("OLY", "123"));
            bills.Add(new Bill("QWE", "123"));
            bills.Add(new Bill("QWE", "143"));

            CarBillComparer cb = new CarBillComparer();

            cars.Add(new Car("ABC", "375"));
            cars.Add(new Car("QWE", "874"));
            cars.Add(new Car("ABC", "762"));

            bills.Sort(cb);
            cars.Sort(cb);

            List<Numbers> returnBills = new List<Numbers>();

            int index = 0;

            foreach (Car onecar in cars)
            {
                int count = bills.Count - index;
                index = bills.BinarySearch(index, count, onecar, cb);
                if (index > -1)
                {
                    //Item found
                    returnBills.Add(bills[index]);
                }
                else
                {
                    index = ~index;
                }
            }

        }
        public class CarBillComparer : IComparer<Numbers>
        {
            public int Compare(Numbers x, Numbers y)
            {
                int compResult = x.car_init.CompareTo(y.car_init);
                if (compResult == 0)
                {
                    compResult = x.car_nbr.CompareTo(y.car_nbr);
                }
                return compResult;
            }
        }

        public interface Numbers
        {
            string car_init { get; }
            string car_nbr { get; }
        }

        public class Bill:Numbers
        {
            public Bill(string car_init, string car_nbr)
            {
                this.car_init = car_init;
                this.car_nbr = car_nbr;
            }

            public string car_init { get; }
            public string car_nbr { get; }
        }

        public class Car : Numbers
        {
            public Car(string car_init, string car_nbr)
            {
                this.car_init = car_init;
                this.car_nbr = car_nbr;
            }

            public string car_init { get; }
            public string car_nbr { get; }
        }


    }
}

LINQ Query to compare two values of an object to a list, where both objects must match

1 Answers1