C# LINQ - Part 2: Basic LINQ Queries

[Music] [Applause] [Music] [Applause] [Music] okay please take your seat we are going to start momentarily let's continue and let's see what we can do with what with what we have learned so far now i think it's i hope it has become clear how i enumerable does its processing so you can remove the breakpoints and we can simplify our code a little bit we can go back to lambda methods again so please change your code make it more beautiful again something like this let's start our next experiment by splitting our code up into multiple lines so if we take a look at ienum at result the type of i result is an i enumerable of int right what will be the result after the where clause and i enumerable int what will be the result after the select clause and i enumerable think so i can write the following code i can change the code a little bit i can say result equals to result dot where next line again result equals to result dot select will this code change the way the system processes our values now we we have no longer a single link query but we have the call to the generator method we have the call to the where method we have the call to the select method so will that now change the way it processes our values will generate numbers be called now here in this line because the statement has been finished here the answer is no if you take a look at how c-sharp processes this guy again i press f10 to go through the debugger now it works fine you see if i press f11 it still doesn't enter the generate number method if i press f11 it still doesn't enter still doesn't enter still doesn't enter only when i'm on the in statement i'm going into the generate numbers method that's the point so what you should take away from this example here is that the call to a generator method the call to a method that generates an i enumerable is never executed even if you split it up in multiple lines the whole query is executed when you run through the results when you iterate through the results understand what i mean that is called query composition you can add various lines of code here for instance we could say var even or odd no let's say just var even is true and then we can say if even then we change the result like this so we only add this where clause if this boolean value this could be an input of a customer a customer could check a checkbox or not it's only evaluated if even is true so we are building the whole query based on an algorithm if even is true and we run the whole application let's do that we get only 0 6 12 18 and so on if we set even to false imagine that you have built a nice ui and the user has unchecked a checkbox if we run it with even equals false we get we get much more values you see that one and that's the power of link by putting these statements in different lines we are composing the query depending on some code artifacts depending on an if statement a while statement or whatever you want understand what i mean that's not easy to understand it's called query composition or deferred execution defer fashion the execution is deferred until somebody is really interested in the results any question to that that will be super important when we do queries with entity framework against the database you will see that let's change this code again a little bit and let's remove the for each statement and instead of the for each statement i would like to introduce you to a new guy a new link function which is called count console.writeline result.count now i don't have any for each statement anymore where and why will the generator be called in the where clause no yeah in the right line when results.count is called correct the system cannot find out how many values we have if it doesn't execute the generator method so there are link mesh methods in this case for instance count which execute the entire query immediately understand what i mean so if we take a look if we set a breakpoint here and set a breakpoint here in the generator method and i let everything run it will first hit the console.writeline you see that and only because inside the console.writeline we call the count method generatenumbers is now called repeatedly i can run through it again and again and again and again and again and again until all the values are generated should be 10 values something like this and then the result is printed understand what i mean this is the count method here before the select i will do a result equals to result dot order by descending so i will sort the numbers descending upstage and before i do the select let's quickly check whether it works i will change the even to true so we have less values let's run it and as you can see we got six elements that's correct this is the count question when is the generate numbers method executed is it executed here no we have learned that is it executed here no we have learned that is it executed here or is it still deferred execution let's see let's set a breakpoint and run it you see here we are at the order by descending and the question is will we reach the select statement first because it is not executed yet because i told you it's only executed when we do a count or for each or something like this or will it be immediately executed let's see it's not executed you see and now we are in the generate numbers so even if you do complex operations something like order by it is not immediately executed you have to understand that order by the statement is called order by but it doesn't really order it is just a kind of method that you plug into a collection of things that you put together in a kind of pipeline and only when at the very end you say and now i need the results the values start flowing through this pipeline so order by is just an operator that use schedule for deferred execution but the execution happens only when somebody is interested in the results when somebody's pulling results out of the of the whole link pipeline understand what i mean difficult to understand easy to understand let your face know whether it's clear or not yeah clear good if you think hey mr stropak that's trivial i already got it believe me you will run into mistakes and i will remind you when we do entity framework um in to to to this lesson because it takes a while until you really understand what that means and what the consequences are but now i think you've got the the basic knowledge that's good good if you don't have any further questions regarding uh these generators and basic link operators i think we can switch gears and go into a larger more complex example where we take a look at various link operators and check how they work is that okay for you or do you have any further question last chance to this topic no good let's go into a more complicated example or a more elaborate example i would like to introduce you to a web page that i really like to use does anybody already know mockeroo or similar web pages no then please go to mockeroo this is how you write it i really like mockeroo.com because in practice you very often need test data and i get bored creating test data and websites like mockeroo there are many different uh websites that can can generate demo data but i personally like makaroo it's free what you can essentially do you can say which fields do i want to have and makaroo offers you a lot of different types you see so let's make an example okay um let's remove all the the fields huh let's make let's make cars okay let's make an example with cars let's add a field let's say we have a car make there here and if we take a look at car oh no do we have something like makes car make you see so we can say generate different car makes and a website like macaroo you can say give me 1000 elements and then you can hit preview and it will show you a bunch of mix car makes nice isn't it let's see do we have modals car modals yeah car model nice if we take a look at preview you will see you have now the make and the modal macaroon is nice isn't it then we can add another field i think that's um the car year car model year that's the year when the model arrived on the market and let's add um mock-up field let's say number of doors and here we can use remove that a random number something like this and we can say the minimum number of doors is two the maximum number of doors is five and we have zero decimals so we just get a random number of course it doesn't make sense because it doesn't fit to the models but it is kind of demo data it is a useful demo data and then the horsepowers and this will be at least 50 horsepowers and our most our strongest model let's say has 500 horsepower something like this now we can preview this stuff again and now we have a nice piece of dental data you see it of course these values are completely random so they don't make much sense but granted for demo for doing some examples that's really useful next we can select which format which format we would like to have we can immediately export the data into various formats see that one and we are going to go for json you can even if you if you sign into mocku you can even um not export the data but you can store this data set under a name and then you get automatically a web api with this demo data that you can use for instance in your angular project or whatever you do understand what i mean so you get a web api that generates random data for you do you understand the usefulness of this that's pretty useful isn't it good nice um i would like to add a last field that's the id and please add the roll number and i would put i will put the id at the very beginning of our data set id row number and good if you are happy with your data set please download the data it will give you a json file and then take this data take this json file this one and put it into your c-sharp application uh i called it hello link and i will put it exactly here and i'll just call it data.json so if i go into visual studio i now have my data.json file you see it here directly in my project this is what you should do so remember if you need demo data don't um don't come up with the demo data by yourself but use a mock-up tool like mockeroo or other websites most of them are for free if you just want to generate 1000 rows if you want to generate 10 million rows you have to pay for it so they have typically they have a freemium pricing model where you can do some generation for free and some generation for money you will see that is also pretty useful when you think of your diploma thesis or your two-year project you can fill your ui pretty quickly good good now that we have some demo data we can get rid of our tote of our of our code entirely and read the the data.json and deserialize it first we need to have a target where we can store these values in c-sharp so we need to create a class so let's generate a class car data something like this and there we can generate a property integer id a property string make a property string modal a property what is the next one year a property int number of doors and a property int horse powers just write hp let's do it using system text json and now we need to tell the system what is the name of the property inside of our json file because the id uppercase letters is not id here in our json you see it this id is lowercase and this is uppercase so we need to do a json property name and tell it that the property in json is called id lowercase we need to do the same for the make make is called car make so the json property name for make is car make i think you get the idea i will quickly fill it out and give you a chance to write it so the next one will be car modal right car modals i called it car modals not sure if that was a good idea but json property name and the next one is year how was that called car year good and then we have a json property name and this one is i think it was number underscore yes number of doors something like this and last but not least i think you call it hp like horsepowers good get the idea difficult no it is possible during the written exam which will come in a few weeks that i give you a json file and tell you parse this json file read it understand what i mean that's possible so you should be familiar with what i do here good reading that json file is really really simple because we can do a var file content equals to file dot oops file read all text and file not read all text good idea or do you suggest a different method come on i get frustrated if you don't say what correct thank you exactly give it a string path data.json that's good and what am i missing uh wait oh sorry you have to write this line of code before the declaration of the class this is necessary maybe you can remember that if we want to use a relative path to read in some data you have to right click on the file go to properties copy to output directory copy viewer that will copy this file into the same directory where your executable lives and therefore you can load it without specifying an absolute path good so far now let's de-serialize the content var cars equals to json serializer d serialize off car data and we pass in the file file content you should remember these two lines we will use them probably a lot in the next few weeks or days json serializer dot d serialize car data question have you heard in the past of a library which is called newtonsoft json when you learned about c sharp the first time in the school no newtons of json newtons of json is a great library it was used for many many years in the past but if you now see somebody using newton's of json you should not use it anymore it was the gold standard for json serialization and dc realization in c sharp but it is not anymore microsoft has written a new library which is called system.txt.json and it's faster granted newtons of json has more functionality but system text json is quickly catching up and don't worry that mr james newton king who is the owner and creator of newton's off jason is now without a job no he is now working for microsoft and doing the same what he did before but now he is a microsoft employee so they just hired the guy who created the best json parser in the world and now yeah microsoft has its own json parser but this is a completely rewrite so if you want to parse json in c-sharp if it's possible use system text json and not newton soft chase good now let's make a few link queries first print all cars with at least four doors var cars with at least for doors equals to help me what should i write [Applause] i have a problem where is not available what did i do wrong i made this mistake consciously the mistake is here what is the mistake here you see it in the json file take a look at the json file do we have a single car or many cars many cars is this a single car or many cars single car that's a problem right so what do i have to write here array or or list exactly or i innumerable whatever you want you can say array you can say list and of course you could also say i innumerable i will now write car data array that's fine scroll a little bit down you're welcome and with that suddenly where becomes available nice so let's print the whole guy for each of our car in cars with at least four doors and here we will say console rightline please use a template string here and say the car car dot make model has car dot number of doors doors understand it that car whatever has whatever doors the important thing is the where clause here right good let's run this guy and see what we did wrong or whether it works oh yeah looks good is that clear so far i guess it is right what you have learned now is how easy it is to parse in a json file you have learned that demo data generators are really useful and we have implemented a very simple linq query now let's make it a little bit more interesting uh print all cars um print all mazda cars with at least four doors so var mass does with at least four doors equals two cars dot what come on don't make it boring give me an idea i can't take a look where we have mazda you see that's the make master good so what do i do come on yep where good car goes to we had it here in the mic good i see okay okay we and now comes the point what we can do you are right we can say and car dot number of doors e greater equal four yes that's good we got that the point that i want to make here is that it's equally okay to do the following take a look at these two statements now i cannot zoom in i have to do it like that here i have a single where clause in with an and condition here in the middle here i have two where clauses see that one with a single condition here both is pos both are possible choose whatever you like the first one is slightly only slightly more performant at least when we run it against in memory objects when we run it against the database it doesn't matter understand what i mean good so let's comment this one out let's copy this loop let's go here and something like this the mazda car and let's try it and it should look at least way less data rose and looks good two where classes not a big problem is it does anybody need additional time to write down the code yep good i'll wait for another second if you want i can check in this file for those of you who don't follow along if you want to have the code i can check it in that's not a problem good next one print make plus modal for all makes let's start with m not that difficult var make plus modal with m equals to cars dot what where right i think i don't have to ask you that and what is the condition car dot make dot starts with m clear isn't it but now comes the important part i think this is pretty clear now i don't want to bore you so let's go one step further now i can do a select and now it is interesting because the input of the select take oops give it a second now i have it the input will be car data the input will also be some kind of int and the output will be a result difficult to read but not difficult to understand what it can write is we can write car goes to template string car dot make space car dot modal and now the result is no longer an i innumerable of car data but now it's an i enumerable of string because we are projecting the car data into a string i will zoom in a little bit so you can see it better the select clause does anybody have problems with reading this select clause is that clear do you get the idea that we are changing the data type here let's make it even more interesting for you there are a bunch of methods where we can turn an i innumerable directly into a certain collection type there is a to list and a two array method there is also a two dictionary method but i will not go into details here so what we can say here is we can say to list and that will turn the result not into an i innumerable but it will turn the result into a list of string and the interesting thing with the list is that this list supports a method which is called for each and here we can just say car goes to and now comes the interesting part console writeline i don't want to have this stuff here in whoops this stuff here in front and we can console rightline the car and then we do no longer need a variable we can write everything in a single line get it i'll zoom in a little bit now we have a filter a projection an execution into a list and then we iterate over the list and print the result we do no longer need a for each loop question do we need them to listen we can try to get rid of it if we remove the two lists we don't have the for each method anymore for each is defined on lists not on innumerables oh it has a it's a deep technical background and there are a lot of discussions in the c-sharp team going on whether for each should be available on i innumerable i i have read these discussions many years before i can't remember all of them it's a fact it's not there it is pretty simple to build for each on your own many people have done it but yeah it's not available we have to do the two list here it is like it is so if i comment out these few lines here and check whether our application still runs it looks pretty good you see mitsubishi mercedes mazda mini mercury looks good so i will wait a few more moments until everybody has written down this code i removed the variable here at the beginning see that yep because for each does not return anything for each is an action so the return type of for each is void and therefore you can't assign this value to a variable does that fix your problem yes good question clear nice nice nice good so we have where we have select we have to list we have four each next one display a list of the 10 most powerful cars in terms of horsepower i want to see make plus model of the 10 most powerful cars let's try this query before we go into a break so cars dot what this is not that trivial anymore yeah max is possible max is possible yes it's possible what you can do with max is you can give in a day a car and you can't for instance say something like this this will give you the maximum horsepower over all the car data do you think that will help you if you know that the most powerful car has 500 whatever horsepowers not really i don't think so but it was a good question now you know you have learned that there is a max function so let's get rid of it but thank you for the question you were next order by buy do you really want to do an order buy if you want to have the most powerful cars yes so you are on the right track definitely so there is an order by descending what order by descending what see your car car okay at that point in time we have a list of cars sorted by the horsepowers descending so the most powerful cars are at the beginning of the collection good next one yes we could do that but we still don't have this one only the top ten any idea just press dot and take a look maybe there is something like take can remember or can you imagine what take does there are two very important methods it will be super important when we do database programming one of them is called skip and one of them is called take they are used for paging i will describe it okay skip means skip over the first n rows and continue with the n plus one row so skip one means ignore the first row skip ten means ignore the first ten rows and start with the eleventh take on the other hand says take the first n rows and ignore the rest you do that you need that for paging imagine that you are building for your two-year project or for your diploma thesis a website where you would like to enable the customer to page through a list of customers you want to display the first 10 customers take 10 then when the user says next page you want to skip the first 10 customers and display the next so you say skip 10 take 10. on the third page you say skip 20 take 10. understand what i mean and with that you can go one window after the other understand what i mean so in our case we can just say take ten and the rest is what we already have here we can't do that one select to list forage order by horsepower descending take the top 10 build the result make it modal and print the result on the screen so the result should be a list of 10 cars of course it's completely random so in reality these cars might not have so many horsepowers but yeah that's not the point do you understand take and order buy based on this example yes good and with that the second hour is already over i think we have now a larger break right so we have 15 minutes break we will continue at 10 and then we will go even deeper and take a look at things like group by and so on okay good enjoy the break

Channel: Rainer Stropek

Views: 5,127

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

Id: tNLdFteXT6k

Length: 40min 19sec (2419 seconds)

Published: Tue Oct 20 2020