Join two list using LINQ query

Question

I am trying to join two lists and implement LINQ query on them to display White and Black cars from these two lists, so far code does the job, but that not the result that I exactly wanted, here is my code:

static void Main()
{
    List<Cars> cars = new List<Cars>
    {
        new Cars { Make = "Honda", Model = 2000, Color = "Black" },
        new Cars { Make = "Suzuki", Model = 1999, Color = "White" },
        new Cars { Make = "Toyota", Model = 2020, Color = "Green" },
        new Cars { Make = "Kia", Model = 2020, Color = "Blue" }
    };
     List<MakeBy> makeby = new List<MakeBy>
        {
            new MakeBy { Make = "Tesla", Model = 1998, Color = "Black" },
            new MakeBy { Make = "Audi", Model = 2015, Color = "White" },
            new MakeBy { Make = "Mercedes", Model = 2021, Color = "Green" },
            new MakeBy { Make = "Ford", Model = 1991, Color = "Blue" }
        };
    var CombineCars = cars.Join(makeby,
                        c => c.Color,
                        m => m.Colour,
                        (c, m) => new
                        {
                            carMake = c.Make,
                            carModel = c.Model,
                            carColor = c.Color,
                            makeByColor = m.Colour,
                            makeByCountry = m.Country
                        });
    foreach (var car in CombineCars)
    {
        Console.WriteLine($"Car model: {car.carModel}, car make: {car.carMake}, Car Color: {car.carColor}, Make By: {car.makeByCountry}, Make Color is: {car.makeByColor}"); 
    }
    Console.ReadLine();
}

This piece of code displaying this result:

Car model: 2000, car make: Honda, Car Color: Black, Make By: Japan, Make Color is: Black
Car model: 1999, car make: Suzuki, Car Color: White, Make By: China, Make Color is: White
Car model: 1999, car make: Suzuki, Car Color: White, Make By: Japan, Make Color is: White
Car model: 1999, car make: Suzuki, Car Color: White, Make By: Korea, Make Color is: White

There is only one WHITE CAR in the first list, why my code displaying 2 more white cars after joining these two lists? Please tell me if there is an improved way for displaying these lists after joining.

Answer 1

If I've understood your question correctly, you just want to filter the joined list, if this is the case, then this might work:

var combinedCars = from c in cars
               where c.Color == "White" || c.Color == "Black"
               join m in makeby on c.Color equals m.Color
               select new
               {
                   carMake = c.Make,
                   carModel = c.Model,
                   carColor = c.Color,
                   makeByColor = m.Color,
                   makeByModel = m.Model,
                   makeByMake = m.Make
               };

Courtesy: Richard Deeming

Answer 2

If you want to preserve list of items from cars collection then you can use Select method with Where to filter by Color property:

var filtered = cars
   .Where(w => w.Color == "Black" || w.Color == "White")
   .Select(s => new
   {
       s.Make,
       s.Model,
       s.Color,
       MakeByColor = makeby.FirstOrDefault(f => f.Colour == s.Color)?.Colour,
       MakeByCountry = makeby.FirstOrDefault(f => f.Colour == s.Color)?.Country
   });

UPDATE:

You can use Union method to union non related items:

var uniqueMakeby = makeby.Select(s => s.Colour).Distinct();            
var selectedCars = cars
    .Where(w => uniqueMakeby.Contains(w.Color))
    .Select(s => new
    {
        s.Make,
        s.Model,
        s.Color,
        Country = string.Empty
    });
var unioned = selectedCars.Union(
    from x in makeby select new 
    { 
        Make = string.Empty, 
        Model = 0, 
        Color = x.Colour, 
        x.Country
     }
);

Answer 3

It is expected behaviour. Join will find all records in second list which has the same value and filter out records which do not have value on the right side.

According to the data, you cannot use Join here, but Zip

var CombineCars = cars.Zip(makeby,
    (c, m) => new
    {
        carMake = c.Make,
        carModel = c.Model,
        carColor = c.Color,
        makeByColor = m.Colour,
        makeByCountry = m.Country
    });