Need help doing a Group count with a join

New Bee

7y1.2kLINQ

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

----

ModelID

Year

Status

Color

----

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 =
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Faisal Pathan7y ago
Accepted 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
Akash Ghadage7y ago
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()
});
New Bee7y ago
Thanks, i'll give it a try. I had something simular to that before.