Need help doing a Group count with a join

Question

Hi all, I hope someone could help me out with this linq query. I am new to Linq and having a hard time figuring out how to do the CountSoldByColor. What I need is to be able to do the count where from the grouped items and alo include a join table Color where the Name of the color is say "blue". If you need more info please don't hestiate to ask. Thanks in advance! Cars ---- ID ModelID Year Status Color ---- ID CarID Name from s in ( from t in Cars select new { t } ) group s by new { s.t.ModelID, s.t.Year } into g select new { Model = (from k in Models where k.ID == g.Key.ModelID select k.Name), Year = g.Key.Year, CountSold = g.Count(p => (p.t.Status == "Sold" )), // Need to Count the number of Cars that were Sold and is Color blue (lets say for example and where Color is another table) CountSoldByColor = }

Faisal Pathan · Answer

var counts = from c in Cars join clr in Colors on c.ID equals clr.PlaceID into clrCar join mdl in Models on c.ModelID equals mdl.ID into mdlCar select new { Model = mdl.Name, CountSold = c.Count(p => (p.t.Status == "Sold")), CountTalk = clrCar.Count(p => (p.t.ColorName == "Blue")) }; I'm not sure this will work or not, so may be you need to do minor change

New Bee · Answer

Thanks, i'll give it a try. I had something simular to that before.

Akash Ghadage · Answer

var x = (from car in Cars join color in Color on c.ID equals color.Name join model in Models on c.ModelID equals model.ID where car.Status == "Sold" && color.Name == "blue" select new { Model = model.Name, Year = mode.Year, Color = color.Name, CountSold = car.Count() });

Need help doing a Group count with a join

Answers (3)