C# Linq eqiuvalent of SQL Count()

Question

I have a fairly complicated join query that I use with my database. Upon running it I end up with results that contain an baseID and a bunch of other fields. I then want to take this baseID and determine how many times it occurs in a table like this:

TableToBeCounted (Many to Many)
{
     baseID,
     childID
}

How do I perform a linq query that still uses the query I already have and then JOINs the count() with the baseID?

Something like this in untested linq code:

from k in db.Kingdom
join p in db.Phylum on k.KingdomID equals p.KingdomID
where p.PhylumID == "Something"
join c in db.Class on p.PhylumID equals c.PhylumID
select new {c.ClassID, c.Name};

I then want to take that code and count how many orders are nested within each class. I then want to append a column using linq so that my final select looks like this:

select new {c.ClassID, c.Name, o.Count()}//Or something like that.

The entire example is based upon the Biological Classification system.

Update:

Assume for the example that I have multiple tables:

Kingdom
|--Phylum
    |--Class
       |--Order

Each Phylum has a Phylum ID and a Kingdom ID. Meaning that all phylum are a subset of a kingdom. All Orders are subsets of a Class ID. I want to count how many Orders below to each class.

I hope this is clear now.

Answer 1

Normally this is done with a group. For example:

from k in db.Kingdom
join p in db.Phylum on k.KingdomID equals p.KingdomID
where p.PhylumID == "Something"
join c in db.Class on p.PhylumID equals c.PhylumID
group c by new { c.ClassID, c.Name } into g
select new { Count = g.Count(), g.Key.ClassID, g.Key.Name };

That will basically count how many entries you have for each ClassID/Name pair. However, as Winston says in the comments, you're possibly interested in another table (Order) that you haven't told us about. We can't really give much more information until we know what you're doing here. Do you already have a relationship set up for this in LINQ to SQL? Please tell us about the Order table and how it relates to your other tables.

EDIT: Okay, with the modified question, I suspect we can ignore phylum and kingdom completely, unless I'm missing something. (I also can't see how this relates to a many-to-many mapping...)

I think this would work:

from o in db.Order
group o by o.ClassID into g
join c in db.Class on g.Key.ClassID equals c.ClassID
select new { c.ClassID, c.Name, g.Count() };