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