How would one create summary columns in query results using Linq-to-SQL?

Question

Let's say we have these tables:

CAR
ID  Name
1   Mustang
2   Taurus

CAR_PART
ID   CAR_ID   PART_NUMBER
1    1        M772
2    1        A443
3    2        Z889

CAR_COLOR
ID     CAR_ID  COLOR_NAME
1      1       Red
2      1       Blue
3      2       Yellow

We need to use Linq-to-SQL to get this result:

CAR_ID   CAR_NAME   CAR_PART_LIST     CAR_COLOR_LIST
1        Mustang    M772,A443         Red,Blue

How would this be accomplished? I have a new class created with the result column names, and figure a select new MyClass{}; at the end would be good, but am not sure how to handle the multiple groupings for the CAR_PART_LIST and CAR_COLOR_LIST columns.

Any ideas?

---

edit: here is what I have so far:

from car in db.CAR
join part in db.CAR_PART on car.ID equals part.CAR_ID
join color in db.CAR_COLOR on car.ID equals color.CAR_ID
where car.ID = 1
select new MySearchResult{
     CAR_ID = car.ID,
     CAR_NAME = car.Name,
     CAR_PART_LIST = ?,
     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() { }
}

Answer 1

Using the obvious String extension method:

public static string Join(this IEnumerable<string> s, string sep) => String.Join(s, sep);

You can compute the answer by using group join on each related table:

var ans = from car in db.CAR
          join part in db.CAR_PART on car.ID equals part.CAR_ID into partj
          join color in db.CAR_COLOR on car.ID equals color.CAR_ID into colorj
          where car.ID == 1
          select new MySearchResult {
              CAR_ID = car.ID,
              CAR_NAME = car.Name,
              CAR_PART_LIST = partj.Select(p => p.PART_NUMBER).Join(","),
              CAR_COLOR_LIST = colorj.Select(c => c.COLOR_NAME).Join(",")
          };

Answer 2

Do you have foreign keys set up for db.CAR_PART and db.CAR_COLOR? If so, that linq-to-sql will automatically give you properties for the joins. So, it becomes:

var q = from car in db.Car
where car.ID == 1
select new MySearchResult
{
    CAR_ID = car.ID,
    CAR_NAME = car.Name,
    CAR_PART_LIST = String.Join(",", car.CAR_PARTs.Select(cp=>cp.PART_NUMBER))
    CAR_PART_LIST = String.Join(",", car.CAR_COLORs.Select(cp=>cp.COLOR_NAME))
};

Answer 3

I writed it here dotnetfiddle please check it out:

    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);

Answer 4

So you have a table of CarParts, where every CarPart has a CarId and a PartNumber; and you have a table of CarColours, where every CarColour has a Carid and a ColourName.

I assume you do not support invisible cars, so every car has at least one part, and one colour.

You want a sequence of all CarIds, each CarId with the CarName, a list of all CarParts belonging to this CarId(= that have this CarId as foreign key) and a list of all ColourNames belonging to this CarId (again using the foreign key.

To do this, first we get all CarIds with their CarParts and all CarIds with their ColourNames, then we can Join the results on common CarId.

If you think there might be cars without parts or without colours, you need to do a 'Full outer Join' instead of a normal Join. This is not part of standard LINQ, but you can write the extension function yourself. See LINQ Full Outer Join

After the join on common CarId, we Join the result with your Cars on CarId

var partsGroupedByCarId = carParts.GroupBy(     // make groups of carParts
    carPart => carPart.CarId);                  // with common CarId as Key

var coloursGroupedByCarId = carColours.GroupBy( // make groups of carColours
    carColour => carColour.CarId);,             // with common CarId as Key

var joinResult = partsGroupedByCarId.Join(      // Join the two groups
    coloursGroupedByCarId,
    partGroup => partGroup.Key,                 // from each group of parts take the key (= CarId)
    colourGroup =>                              // from each group of colours take the key (= CarId)
    (partGroup, colourGroup) => new             // when they match make a new object
    {
        CarId = partGroup.Key,                  // with the common CarId
        CarParts = partGroup                    // all partNumbers of the car with CarId
           .Select(item => item.PartNumber),
        CarColours = colourGroup                 // all colourNames of the car with carId
            .Select(item => item.ColourName),
    });

Finally a Join of the Cars with all their Colours and Parts:

var result = Cars.Join(joinResult,    // Join all cars with the joinResult
    car => Id,                        // from every car take the id
    joinedItem => joinedItem.CarId,   // from every joinedItem take the CarId
    (car, joinedItem) => new          // for every car with its matching joinedItem
    {                                 // make a new object
         Id = car.Id,
         Name = car.Name,
         Parts = joinedItem.CarParts.ToList(),
         Colours = joinedItem.CarColours.ToList(),             
    });

TODO: consider creating one big LINQ statements. As all statements use deferred execution I don't think this will improve efficiency. It certainly will decrease readability.