Complicated Linq to SQL Query

CSharp · Console

/Program.cs

using System;
using System.Collections.Generic;
using System.Linq;

					
public class Program
{
	public static void Main()
	{
		 IEnumerable<CAR> cars = new CAR[] { 
        new CAR { ID = 1, Name = "Mustang" },
			new CAR { ID = 2, Name = "Taurus" },};
     

            IEnumerable<CAR_PART> carparts = new CAR_PART[] {
        new CAR_PART { ID = 1, CAR_ID = 1, PART_NUMBER = "M772" },
        new CAR_PART { ID = 2, CAR_ID = 1, PART_NUMBER = "A443"},
        new CAR_PART { ID = 3, CAR_ID = 2, PART_NUMBER = "Z889" },};

             IEnumerable<CAR_COLORS> carcolors = new CAR_COLORS[] {
        new CAR_COLORS{ ID = 1, CAR_ID = 1, COLOR_NAME = "Red" },
        new CAR_COLORS { ID = 2, CAR_ID = 1, COLOR_NAME = "Blue"},
        new CAR_COLORS { ID = 3, CAR_ID = 2, COLOR_NAME = "Yellow" },};

var q = from car in cars
join part in carparts on car.ID equals part.CAR_ID  into parts
join color in carcolors on car.ID equals color.CAR_ID into clrs
where  car.ID == 1

select new MySearchResult{
     CAR_ID = car.ID,
     CAR_NAME =  car.Name,
     CAR_PART_LIST =  String.Join(",",parts.Select(p => p.PART_NUMBER)),
     CAR_COLOR_LIST = String.Join(",",clrs.Select(c => c.COLOR_NAME))
};
       
				
            foreach (var item in q)
            Console.WriteLine("{0} {1} {2} {3}",
                item.CAR_ID,
                item.CAR_NAME,
                item.CAR_PART_LIST,
                item.CAR_COLOR_LIST);
	}
}
public class MySearchResult{
    public int CAR_ID { get; set; }
    public string CAR_NAME { get; set; }
    public string CAR_PART_LIST { get; set; }
    public string CAR_COLOR_LIST { get; set; }

    public MySearchResult() { }
}
public class CAR
    {
        public int ID { get; set; }
        public string Name { get; set; }
    }

public class CAR_PART
    {
        public int ID { get; set; }
        public int CAR_ID { get; set; }
        public string PART_NUMBER { get; set; }
      
    }

public class CAR_COLORS
    {
        public int ID { get; set; }
        public int CAR_ID { get; set; }
        public string COLOR_NAME { get; set; }
      
    }