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