1 00:00:00,430 --> 00:00:02,930 Since we're tracking our bird sightings and 2 00:00:02,930 --> 00:00:07,010 conservation status, let's write a query to see how many sightings 3 00:00:07,010 --> 00:00:11,800 have been reported of birds that are at risk of becoming endangered. 4 00:00:11,800 --> 00:00:14,280 Make sure you've got your birds list and your C# REPL. 5 00:00:15,340 --> 00:00:19,623 First, let's check out the different conservation statuses the birds have. 6 00:00:19,623 --> 00:00:27,787 for statuses equals birds.Select, 7 00:00:27,787 --> 00:00:35,540 b goes to b.Conservationstatus and 8 00:00:35,540 --> 00:00:41,390 then I call Distinct so I remove the duplicates statuses. 9 00:00:42,910 --> 00:00:46,400 So we've got least concerned, endangered, vulnerable, 10 00:00:46,400 --> 00:00:49,320 near threatened and critically endangered. 11 00:00:49,320 --> 00:00:53,820 We only want to concern ourselves with the endangered statuses So, 12 00:00:53,820 --> 00:00:57,130 why don't we take out least concerned and near threatened. 13 00:00:57,130 --> 00:01:00,866 Let's reassign it the same variable but without those statuses. 14 00:01:00,866 --> 00:01:07,147 So statuses = statuses.Where 15 00:01:07,147 --> 00:01:11,797 s goes to s is not equal to 16 00:01:11,797 --> 00:01:16,217 "LeastConcern" and 17 00:01:16,217 --> 00:01:23,900 s is not equal to "NearThreatened". 18 00:01:27,960 --> 00:01:33,170 Okay, so now our statuses has only what we want to deal with. 19 00:01:33,170 --> 00:01:35,500 We'll need to access the sightings of birds and 20 00:01:35,500 --> 00:01:37,940 then only get those that are in our list of statuses. 21 00:01:37,940 --> 00:01:42,360 I bet there are a lot of ways we can achieve this. 22 00:01:42,360 --> 00:01:46,910 The first thing I think of when I've got two different collections is a join 23 00:01:46,910 --> 00:01:50,160 we can join our list of birds to the status as a variable 24 00:01:50,160 --> 00:01:52,784 then we'll only get the birds that have that status. 25 00:01:52,784 --> 00:01:57,997 FarendangeredSightings equals 26 00:01:57,997 --> 00:02:03,213 start with birds.join statuses 27 00:02:05,310 --> 00:02:06,780 I'll do a new line here. 28 00:02:08,000 --> 00:02:12,399 So our outer key would be birds b goes to 29 00:02:12,399 --> 00:02:18,980 b.ConservationStatus facial and 30 00:02:18,980 --> 00:02:23,597 then our inner key selector From our statuses list, 31 00:02:23,597 --> 00:02:26,730 s => s, cuz it's just strings. 32 00:02:29,280 --> 00:02:30,450 All right, now for our result. 33 00:02:31,620 --> 00:02:36,045 I'll just use the two input parameters (b, 34 00:02:36,045 --> 00:02:43,670 s) => I'm gonna create a anonymous type here 35 00:02:44,690 --> 00:02:50,586 new status equals s and 36 00:02:50,586 --> 00:02:59,910 birds equals b, okay. 37 00:02:59,910 --> 00:03:01,440 Let's see what that gives us. 38 00:03:01,440 --> 00:03:02,550 EndangeredSightings. 39 00:03:03,780 --> 00:03:08,590 We've got lists of birds, but now we need the sightings. 40 00:03:08,590 --> 00:03:11,500 Well we could actually put in sightings 41 00:03:11,500 --> 00:03:15,750 inside our anonymous objects to get the sightings instead of the birds. 42 00:03:15,750 --> 00:03:21,670 So I'm gonna use my Up arrow key to get most of the query back so I can reuse it. 43 00:03:23,240 --> 00:03:25,810 B goes to B dot conservation status. 44 00:03:27,360 --> 00:03:33,790 Then S goes to S but here I'm going to do B S 45 00:03:35,040 --> 00:03:38,420 goes to new we'll keep the statuses. 46 00:03:38,420 --> 00:03:39,520 We like statuses. 47 00:03:40,720 --> 00:03:44,790 Equals s but this time I'm gonna say Sightings 48 00:03:44,790 --> 00:03:49,750 equals b.Sightings. 49 00:03:49,750 --> 00:03:55,710 Okay, let see what that gives us, endangered sightings. 50 00:03:57,600 --> 00:04:01,030 So we've got one element for each bird. 51 00:04:01,030 --> 00:04:04,890 We need to group them and then get a total count for each status. 52 00:04:04,890 --> 00:04:07,330 So let's group by status. 53 00:04:10,499 --> 00:04:14,274 So var endangered sightings. 54 00:04:14,274 --> 00:04:16,536 Actually how about I clear the console? 55 00:04:21,655 --> 00:04:25,205 Okay, and then I'm gonna use my up arrow key again to get my history. 56 00:04:25,205 --> 00:04:32,761 var endangeredSightings = birds.Join(statuses,. 57 00:04:32,761 --> 00:04:33,700 Yep, looks good. 58 00:04:33,700 --> 00:04:36,600 b => b.ConservationStatus and then. 59 00:04:36,600 --> 00:04:37,730 S goes to S. 60 00:04:37,730 --> 00:04:43,708 But then I'll do b, s goes to 61 00:04:43,708 --> 00:04:48,900 new status. 62 00:04:48,900 --> 00:04:50,660 Actually we're gonna do the same thing. 63 00:04:50,660 --> 00:04:51,900 I coulda used my arrow key. 64 00:04:51,900 --> 00:04:53,430 But that's okay. 65 00:04:53,430 --> 00:05:00,095 Status = s and then Sightings 66 00:05:00,095 --> 00:05:05,960 = b.Sightings then I'm 67 00:05:05,960 --> 00:05:11,292 going to call .GroupBy 68 00:05:11,292 --> 00:05:17,969 b goes to b.Status because, 69 00:05:20,315 --> 00:05:25,531 Our anonymous type here, the property is status so we're gonna group by that. 70 00:05:25,531 --> 00:05:31,125 Okay, and endangeredSightings. 71 00:05:31,125 --> 00:05:32,240 All right. 72 00:05:32,240 --> 00:05:37,330 Well it looks like our statuses are there, and our sightings Okay we do have 73 00:05:37,330 --> 00:05:43,400 a group it's a little hard to see like this so let's do that same query again. 74 00:05:43,400 --> 00:05:45,079 I'm gonna clear the console. 75 00:05:48,023 --> 00:05:53,286 All right, endangeredSightings b goes to 76 00:05:53,286 --> 00:05:59,135 b.ConservationStatus s goes to s then that but 77 00:05:59,135 --> 00:06:04,252 then, instead of returning sightings, 78 00:06:04,252 --> 00:06:09,970 let's do a select here .Select b goes to new. 79 00:06:09,970 --> 00:06:16,140 Then we'll do Status = b.Key 80 00:06:16,140 --> 00:06:21,050 which should be our status, right, because we just did the GroupBy Status. 81 00:06:21,050 --> 00:06:25,650 Next it's key and then the Sightings 82 00:06:27,010 --> 00:06:31,170 is going to be equal to the b.Sum and 83 00:06:31,170 --> 00:06:36,410 then s goes to s.Sightings .Count. 84 00:06:36,410 --> 00:06:41,260 So we're gonna get the sum of the count of the sightings for each grouping by status. 85 00:06:42,870 --> 00:06:47,910 Then we need to curly brace to end our anonymous type and 86 00:06:47,910 --> 00:06:52,980 then close the Select method and Okay, 87 00:06:52,980 --> 00:06:57,420 let's see what we get now endangeredsightings. 88 00:06:59,060 --> 00:07:00,300 All right, great. 89 00:07:00,300 --> 00:07:03,110 We've got 14 sightings of endangered birds. 90 00:07:03,110 --> 00:07:06,880 29 are vulnerable and 12 of critically endangered. 91 00:07:08,220 --> 00:07:09,622 Looks like I misspelled endangered. 92 00:07:09,622 --> 00:07:11,209 [LAUGH]