C# LINQ - Part 3: More Advanced LINQ Queries

[Music] [Applause] [Music] [Applause] [Music] so i guess you understand now order buy take select where to list forage this shouldn't be a big problem anymore are you ready for the next layer next level of complexity let's add group by group by is super important and we are going to need it quite frequently so let me show you what i would like to have this play the number of modals per make that appeared after um i think we have a year correct yes here we have it that appeared after 1959. display the number of modals modals would be golf per make would be volkswagen and appeared after 1995 would be a filter on the car year now this one is more complex and i don't ask you how it works i want to show you how it works let's start with the modals per make we will write cars dot group by car goes to car.make do you understand what grouping means group by understand how that works what grouping is generally not just in c sharp yes correct it's like a group in sql but this doesn't answer my question do you understand grouping in sql no i guess so it's not trivial to understand let me show you an example don't follow along just follow along in your mind okay imagine that we have the following data make modal make is volkswagen golf volkswagen beetle toyota prius toyota uh i don't have no let's take something else let's take tesla modal three and then we have another volkswagen um this is the polo i don't know that's okay so how does grouping work let's make it like that grouping works like that first whenever you do grouping you have to sort by the column by which you would like to group so we have to move the polo this one up here so now we have all the volkswagens the toyota and the tesla okay if we sort it we have to also cut and paste it here because tesla comes before toyota grouping that means that we go through all the distinct values we have volkswagen tesla and toyota and we put them into different groups so this would be the group one volkswagen it works like this it takes a look at the previous value compares it with the current value if these two things are the same the value the grouping value stays the same so we have still group one we have still group one if we compare volkswagen to tesla it changes so we have group two and if we compare tesla to toyota we have group three and now we have three different groups and we call the make the group key so key of group one is volkswagen so we can change that label and can say this is the group key what is the group key for the group one it's volkswagen volkswagen volkswagen the group key for group two is tesla and the group key for two for for all these ones is toyota so we have this group this group this group and now we have a single group key we cannot write it like that merge it you see so we have the group volkswagen we have the group tesla and we have the group toyota and to this group here belongs again an i innumerable of cars so we group the cars into group keys which are yeah the level the column on which we do the grouping sounds very difficult but let's take a look the result of our group by method is an i innumerable of i grouping of string and card data what does that mean we have a group key which is of type string what is the group key the make volkswagen toyota and tesla and per make we have a collection of car data which belongs to this make is anybody still following me or did i lose a lot of you i guess i lost a lot of you because it's really complicated let me show you how that looks like if i do a two list and do for each item goes to console writeline item something like this and if i run this guy i will get these very strange grouping things it's difficult to understand what grouping is but change your code now to item.key see what's going on now we get all the group keys we get the distinct makes we get something like maybach only once because we grouped the data by make it's just like in my excel we do not get volkswagen three times but because we grouped based on this column we get the group key only once understand what i mean good nice now we have this group by car make the next thing is that we want to have the number of modals anybody has an idea how we can do that yep select let's take a look what's inside of c inside of c is the key which is the make and if i scroll down a little bit i also will find not nothing else just the key that's fine we want to have the number of modals per make and you are thinking about the return type of the select right what we can easily write is we can write an anonymous class this is how you can create an object without class does that help you yeah that was my question i want to have the result however we reach it c dot key that will give me an object that contains the key okay c dot sum count i agree count [Music] c dot what doesn't work there is no c dot model see that count is a very good idea see that count is a very good idea what would give us this first we get a an error because we need to assign a name let's say number of modals and the error goes away what will give us this query help your colleague it will give us per make the number of modals because we are counting per group how many items we have in each group think of the excel if we say count on the group we get the number of items inside the group for volkswagen this will be three for tesla this will be one and for toyota this will also be one get the idea so we can ask for the key and we can then apply a nested link query a link query inside the link query based on each group oops sorry i clicked on something i don't want so let's print this thing to list for each item console writeline let's print the item.key and let's print the item dot number of modals if i run this it will show me that we have here you see it two ferraris for instance let's verify that this really is true just look for ferrari this is one and this is the second one see so this this looks good we have two ferraris so seems that our program looks pretty good do you understand what we have written here with group by we are creating a grouping with a group key and each item is again a collection of rows which belong to this group i repeat myself consciously a group key and for each group key we have a collection of data rows which belong to this group this is why we can apply link to each group but we are not done yet because here we have a filter that appear after 1995. yes before you do the group by yes that's a possibility so we can say uh where car car dot year greater equal 1995. something like this let's check the ferrari the ferrari is 2006 and the second one no ferrari 2007 and 2006 so the ferrari should still be present with two items and here it is let me check again 2006 and 2007 okay [Applause] okay i will give you a chance to read this code again to try to understand it good next level i will now change the requirements and say that appeared after 2008 we can just change the year to 2008 and we know that ferrari should now be no longer in the list because we don't have a ferrari which appeared after 2008 in our list let's try it let's run it and if we are lucky we don't see a ferrari anymore that looks pretty good we can take a look no ferrari anymore but now i would like to add a little bit complexity makes should be displayed with a number of zero if there are no modals after 2008. assign what i mean the result should contain ferrari zero if we don't have any ferraris after 2008 what can we do who has an idea anybody else good give us your idea you would delete the where i will do that just to make sure i will put it into the clipboard because maybe we find a better place for the where clause after the select that will be pretty difficult because if i put it after the select clause we don't have the year anymore because after the select clause we only have the key which is the make and the number of modals five we cannot take a look into each row so nope that's not a good idea let's undo that i'll show you the trick do you understand what i did let's check whether it's whether it works okay let's run it and if we are lucky yes ferrari zero why does it work yep select but after that yeah what we essentially do is we take the group volkswagen we know that part of this group are these elements here and then we specify a where clause but not over the entire data set but we specify a where clause inside of this data set understand what i mean i am not filtering before i do the grouping i am now filtering on the grouped data so i get a group of modals for ferrari and before i print the group uh the the list of elements per ferrari i filter them before we did the filtering and afterwards we did the grouping after the filter ferrari was gone so the result didn't contain any ferrari record understand what i mean do you see the difference and luckily because this is so common that you want would like to count something which is filtered we can shorten that we can take the lambda method here remove the where clause here and put the lambda method directly into the count so count optionally takes a filter and counts only those elements which return true for this filter so if i run this guy we should still see haha a ferrari did you think that link is trivial do you still think that link is trivial no it isn't definitely not there is a nice principle in software design which is called simple things should be simple complex things should be possible and that's link if you have a very simple query it's very simple to write complex queries are complex to write but they are possible questions to this example here clear [Applause] ready for the next one next one display a list of makes that have at least two modals that appeared after 1995. now that that's boring again this this is let's change it a little bit so we have something new display a list of makes that have at least two models with greater equal 400 horsepowers i have no idea if we have such makes get the idea i want to find out how many makes build really how many car manufacturers build really powerful cars not just one powerful car but multiple powerful cars cars dot what where car goes to car dot horsepower greater equal 400. got it now we have a list of cars which with at least 400 horsepowers good good first step anybody else what can we do now do you think that grouping would now be a good idea yes whenever you have something like a list per something you always need grouping good let's do a group by group by and we group by what car uh make of course good now we have a grouping by make select i agree very good i like that select by what or select what so the same as above i think we can do pretty much the same as above right so we can say something like make equals car dot key and number of powerful cars equals to what car dot count nice i like that so now we have the number of cars with at least 400 horsepowers and the corresponding make but we want to have those that have at least two models yes you want to do another filter where here inside of the select no we don't need to print zero we only want to have a list of makes exactly and that's the point now we can apply another where make goes to make dot number of powerful cars e greater equal to do you understand the principle we now have two different where clauses the first where clause operated on the raw list the second where clause operates on the grouped data this is not trivial i understand that let's try it do the two lists for each make goes to console writeline make make if i run this thing let's see if we have any makes yes we have some for instance hummer question yes yes what you could do what you are now suggesting is to comment this one and to here write a block of code and do something if i understand you correctly do something like if if make dot number of greater equals two then do a console write line something like this right yeah that would definitely be possible and it would be okay but it is possible to do the where clause and today we are practicing link so for my solution i would like to stick with the console writeline because then i can display or describe the concept of having multiple where clauses in sql structure query language you have a group by you already mentioned that could i also do a where clause on the grouped results in sql or is there a different clause in sql which i used for filtering grouped values yeah having exactly correct answer in sql you couldn't do that you couldn't say where where you would have to say select group by having correct right understand the principle good nice nice next one this plane the average horsepower per make anybody else i like that you you make suggestions but i just want to see if anybody else also has have an idea the first one is easy cars dot what the average horsepowers per make help me do we need a group buy do we need to select where what do you think who thinks we need a group bar yeah that's the majority okay i follow you group by that was a good idea group by what take a look at the requirements per make so make if we do that we get a list of makes and for each make all the modals with its data so how do we get the average horsepowers select i agree and again again just like above i agree make equals car dot key and then average horsepowers equals to what car yes yes exactly it's as simple as that ah sorry average does not work if you take a look at average oh sorry of course it works sorry sorry my mistakes we need this to tell what it should average sorry my mistake was good everything's fine so let's print these guys just copy this one and take a look whether it works it should work run it oh i don't display the average horsepower sorry i have to do something like this the make and the average horsepowers now it's good nice good i want to try it and then i give you time yeah looks good looks very good do you start to see a pattern how this works i guess so i'll give you another second to type in the code and think about potential questions that you might have are you ready for the next level of complexity good we are working our way towards the end boss how many car how many makes build cars with horsepower between 0 and 100 100 and 200 101 and 200 201 and 300 301 and 400 not 3001 and i think the maximum the maximum was what i think 500 right 500 yes 500 was the maximum so the last one is 401 to 500 so i want to have a range i want to see 0 to 100 horsepower three makes build cars in that horsepower range 100 to 200 5 makes build cars between 101 and 200 horsepower who how could we approach that just tell me ideas i will solve all your syntactical problems but just come up with ideas you have an idea okay so what you would say is you would say cars dot group by car goes to now what is the grouping expression here what you can write i give you an idea right car dot horsepower switch case no i don't need a case here sorry lower equal 100 goes to 0 to 100 lower equal goes to 101 to 200. understand the principle did you know this syntax in c sharp isn't that cool c sharp nine this is brand new hot stuff this wasn't possible a few months ago a few weeks ago but now it is lower equal 300 goes to 201 300 lower equals 400 goes to 301 to 400 else goes to 401 to 500 can you read this code so we are now building groups not just on a trivial property but we are building groups on a calculated value which is with a switch expression this is pretty damn powerful i can tell you that many other programmers in other programming languages than c sharp they are envious they would like to have something like this this is not something that is available in all programming languages it's really really nice to be able to write that how many makes build cars between these things here now we have a grouping by horsepower category but we are not at the end what would be the next one let me make a suggestion and then you can tell me whether it's correct i will just copy the select up here and tweak it a little bit i will say here horsepower category and here i will say number of i number of makes equals to car dot count is that correct if i ask you like that the answer is always no of course why is this one here really the number of makes no it's the number of modals because the make volkswagen creates multiple modals like the golf the polo and so on so here we are counting really the rows and not the makes if we have volkswagen we count it three times if we have the golf the passat and the polo but we only want to con to count it once because the question was how many makes build any car in this range anybody has yeah you have a question a suggestion yeah that's possible we could do a group by but we don't need a group by here because there is something which is which is pretty powerful which is called distinct and if we take a look at what the distinct thing can can do it will return a distinct value based on this item and what we can do i will write it down and oops i will write it down and then you can try to understand it and we will discuss it we can say car dot select c goes to c dot make distinct and if we want to have the number of makes not a list of makes we can do a count wow do you get the idea what we say is build a group of data rows per category and then we say for each line in our data file just pick out the make that will return favi for the for the toyota toyota toyota tesla tesla tesla tesla ferrari ferrari ferrari ferrari understand what i mean for each line we will get just the make that distinct let's fall all the items together in a single line what distinct essentially does is it changes this view see that one it changes this view with the four times volkswagen into boom a single line with just one volkswagen this is what distinct does ain't deutsch just each value one time i understand what i mean and then we are just counting the distinct values we are just counting the makes understand the the concept to list for each item console right line we just say item dot key oh hp category like this and the item dot number of make i just want to check whether it works properly yep looks good we could sort it but i think you get the idea how how this works good i will give you a second to write that down [Applause] okay let's quickly talk about what you need to remember from all this stuff i am absolutely sure that some in this class are not 100 comfortable with this level of complexity in link queries that's normal it takes weeks and months and maybe years of practice in sql or something like link to come up with queries like that to be able to successfully complete this course you need definitely to understand what where and select does you need to understand simple group by scenarios something like this this is definitely necessary for completing this course what you probably will not be asked in an exam is things like that a nested group by a sorry a nested link query or things like that a switch expression in a single link query with subselects and distincts and counts and things like that nevertheless it would be good if you remember things like that it would be good if you practice things like that because that makes the difference between a great developer and an average developer a great developer can use tools like that but for this course the basic things are sufficient if you just want to pass the course if you want to get a real good grade you should probably master more complex queries yeah you will get a homework today where you can practice some link queries on your own and you will see there that you can earn extra points if you manage to write it in a single link query but it's not absolutely necessary you can also combine simpler link queries by writing a little bit of more code that's fine question so far concerning this one can anybody remember what i told you how this syntax construct is called it is called a switch expression in contrast to the classical switch statement that you might already know from other programming languages good good so let me quickly show you the homework that you will have to do until next week it is again a quiz where you have some starter code i will give you the link in the next lesson okay the link to the github classroom where you can create the data currently just take a look at the screen um it is it does it already contains some some code and it also contains as you can see here a list of unit tests so you can always check whether your functionality is already correct by running the unit test at the beginning the unit tests will all be red and at the end all your unit tests should be green that's the goal you already know the drill if we take a look at the library here you see a quiz dot c sharp and if you take a look here for instance first one is really simple get even numbers exclusive upper limit and you have to return an integer array so you have to write some code which generates all the numbers between one and the upper limit and return an array of end if you manage to write that so replace just this line with your code if you manage to write that the first test is green if we scroll down the next one here exclusive upper limits but this time you have to calculate the square and so on get family statistics is a little bit more complicated and get letter statistics is again a little bit more complicated you can take a look at home how can you earn extra points you can earn one extra point by solving all unit tests again write an issue mention me in that issue if you think that you have earned one extra point i will not give you a second extra point you can just earn this time my deepest respect if you manage to implement each method in a single line of code now no extra points but this is just something a question of honor for those of you who want to have the extra challenge try to find a solution a single line of code it is possible definitely question are you also checking issues where you weren't mentioned because i didn't mention your issue but it is easier for me if you mention me explicitly no i will find it out but it's easier for me because then i get a notification i'm not sure if i get a notification if you just add an issue but i will take definitely take a look yeah when it comes to grading i will also show you which extra points i have noted and you have the possibility to tell me hey mr stropak you forgot that i created something like this in many cases for the grade it's not a question of one point more or less if you do all the homeworks you get a lot of points if you do yeah but if when it comes to really a single or two points check me and i will show you my notes and you can check whether i have uh noted everything correctly do you understand this homework it's net five it's completely up to date so i think it shouldn't be that complicated i guess it will take you maybe an hour maybe two maximum then it should be done good yes of course i can send the classroom link in discord that's not a problem i will do that i will do that in either the next order um the following lesson we had a question here no good so your colleague showed you at the beginning of this today's series of lectures showed you the basics of link and now we took the time to dive in deeply we understood now what i enumerable is how deferred execution works we worked with a lot of different link operators like where select skip take group by order by order body sending count max sum and many of them and in the next two exercise lessons that we have together we will take this knowledge and take the previous exercise the one with the csv you know with the tab delimited data and we'll try to solve this homework together by applying this knowledge about link understand what i mean yeah this is our goal for the exercise lesson for today at this point in time do you have any questions about link anymore at the moment that we should discuss no good i will check in the code so you have it if you want to remember some concepts when preparing for the exam but with that i would like to close this lesson and we have a short break i think five minutes and after five minutes we'll go into the exercise lesson okay good enjoy the break thank you

Channel: Rainer Stropek

Views: 6,119

Rating: 4.9302325 out of 5

Keywords: C#, CSharp, .NET, .NET Core, .NET 5, HTL Leonding

Id: 2oNhVmUda_U

Length: 47min 26sec (2846 seconds)

Published: Tue Oct 20 2020