This course will be retired on June 1, 2025.
Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Let's build a query to find out how many bird sightings we have for birds that are endangered.
This video doesn't have any notes.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
Since we're tracking
our bird sightings and
0:00
conservation status, let's write
a query to see how many sightings
0:02
have been reported of birds that
are at risk of becoming endangered.
0:07
Make sure you've got your birds list and
your C# REPL.
0:11
First, let's check out the different
conservation statuses the birds have.
0:15
for statuses equals birds.Select,
0:19
b goes to b.Conservationstatus and
0:27
then I call Distinct so
I remove the duplicates statuses.
0:35
So we've got least concerned,
endangered, vulnerable,
0:42
near threatened and critically endangered.
0:46
We only want to concern ourselves
with the endangered statuses So,
0:49
why don't we take out least concerned and
near threatened.
0:53
Let's reassign it the same variable but
without those statuses.
0:57
So statuses = statuses.Where
1:00
s goes to s is not equal to
1:07
"LeastConcern" and
1:11
s is not equal to "NearThreatened".
1:16
Okay, so now our statuses has
only what we want to deal with.
1:27
We'll need to access
the sightings of birds and
1:33
then only get those that
are in our list of statuses.
1:35
I bet there are a lot of
ways we can achieve this.
1:37
The first thing I think of when I've
got two different collections is a join
1:42
we can join our list of birds
to the status as a variable
1:46
then we'll only get the birds
that have that status.
1:50
FarendangeredSightings equals
1:52
start with birds.join statuses
1:57
I'll do a new line here.
2:05
So our outer key would be birds b goes to
2:08
b.ConservationStatus facial and
2:12
then our inner key selector
From our statuses list,
2:18
s => s, cuz it's just strings.
2:23
All right, now for our result.
2:29
I'll just use the two input parameters (b,
2:31
s) => I'm gonna create
a anonymous type here
2:36
new status equals s and
2:44
birds equals b, okay.
2:50
Let's see what that gives us.
2:59
EndangeredSightings.
3:01
We've got lists of birds, but
now we need the sightings.
3:03
Well we could actually put in sightings
3:08
inside our anonymous objects to get
the sightings instead of the birds.
3:11
So I'm gonna use my Up arrow key to get
most of the query back so I can reuse it.
3:15
B goes to B dot conservation status.
3:23
Then S goes to S but
here I'm going to do B S
3:27
goes to new we'll keep the statuses.
3:35
We like statuses.
3:38
Equals s but
this time I'm gonna say Sightings
3:40
equals b.Sightings.
3:44
Okay, let see what that gives us,
endangered sightings.
3:49
So we've got one element for each bird.
3:57
We need to group them and
then get a total count for each status.
4:01
So let's group by status.
4:04
So var endangered sightings.
4:10
Actually how about I clear the console?
4:14
Okay, and then I'm gonna use my up
arrow key again to get my history.
4:21
var endangeredSightings
= birds.Join(statuses,.
4:25
Yep, looks good.
4:32
b => b.ConservationStatus and then.
4:33
S goes to S.
4:36
But then I'll do b, s goes to
4:37
new status.
4:43
Actually we're gonna do the same thing.
4:48
I coulda used my arrow key.
4:50
But that's okay.
4:51
Status = s and then Sightings
4:53
= b.Sightings then I'm
5:00
going to call .GroupBy
5:05
b goes to b.Status because,
5:11
Our anonymous type here, the property
is status so we're gonna group by that.
5:20
Okay, and endangeredSightings.
5:25
All right.
5:31
Well it looks like our statuses are there,
and our sightings Okay we do have
5:32
a group it's a little hard to see like
this so let's do that same query again.
5:37
I'm gonna clear the console.
5:43
All right, endangeredSightings b goes to
5:48
b.ConservationStatus s
goes to s then that but
5:53
then, instead of returning sightings,
5:59
let's do a select here
.Select b goes to new.
6:04
Then we'll do Status = b.Key
6:09
which should be our status, right,
because we just did the GroupBy Status.
6:16
Next it's key and then the Sightings
6:21
is going to be equal to the b.Sum and
6:27
then s goes to s.Sightings .Count.
6:31
So we're gonna get the sum of the count of
the sightings for each grouping by status.
6:36
Then we need to curly brace
to end our anonymous type and
6:42
then close the Select method and Okay,
6:47
let's see what we get
now endangeredsightings.
6:52
All right, great.
6:59
We've got 14 sightings
of endangered birds.
7:00
29 are vulnerable and
12 of critically endangered.
7:03
Looks like I misspelled endangered.
7:08
[LAUGH]
7:09
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up