Querying DB With More Complex LINQ and Raw SQL

[Music] okay let's get started into the second lesson into the second hour of today's lesson and this time in this hour we want to talk about doing the search we will start with searching for a single word single word search is relatively simple because we need to look for a single word in the title in the description and in the tags the more difficult question is where should we put the search logic i could think of three different places and i would like to discuss with you where i should put it i could put it in the program.cs directly inside of the web api that would be one option i can put it directly into the database context this is our db context it could be a method in the database context then everybody who has the database context has access to our to our such logic all the sir the third option would be a separate class what do you think what would you do would you put it directly in the web api would you put it in the database context or would you put it in a separate class and why we can definitely agree on not putting it in the web api do not put it in the web api in a real world project because that would be spaghetti code you know single responsibility pattern a piece of code should perform one specific task and if the task is implement a web api it should be implementing web api and not implement a web api and implement the search logic so we will not put it in the web api now when it comes to putting it in the database context or in a separate class the answer is more difficult to give because if you have a very small application and you just have let's say a handful of methods which are related to your database context why not putting it in the database context it doesn't hurt anybody it's just there if you don't if you don't need it just ignore it i mean we are talking about a handful of methods but if you are doing a bigger application with more functionality then i'm with you then i'm for the separate class because then the database context would be too large it would be hard to find whatever you look for so go for the database context if you have a single app a single a single app with just a few functionalities and go for the separate class for larger applications let's just simulate for today that we are doing a larger application so let's practice the separate class approach it's the cleaner approach okay let's let's try that one so please add to the data library a new class and let's call this class offering search i will use the new syntax with the namespace on the top without the indentation but it's completely up to you whether you want to do that offering search the first thing is really simple exactly the same as we had before let's add a constructor ctor tab tab let's add a database context share for future database context context generate the field save it in the field and we are good now let's set up the basic functionality the basic method signature for our application public what will be the result the result will be a query result so therefore it will be an i queryable off the question is what are we going to give back let's say offerings because we are looking for offerings and let's call it single word search link because we are going to use link in the first example we will take the filter string as a parameter and that's it that looks like a nice methodic method signature so i think we should stick to that i queryable is an interface representing the result of a query that is why it's called an i queryable let's do a context and what do we want to return well the offerings we want to return the offerings we want to filter the offerings so we have to use a where o represents an offering first the filter could be in the title o dot title dot contains filter first one the filter could also be in the description o dot description contains filter but the filter could also be in the tags so we say o dot tags dot any if any of the tags t dot tag contains filter then we also have a hit so that simply says if the filter is in the title or the filter this one is wrong by the way it's description or the filter is in the description or the filter is in any tag i already told you that if you query something from the database which will never be altered you can turn off so-called object tracking can you remember what it is so what we can do is we can say as no oops now it's good as no tracking maybe you need another using yes you need another using statement for that and that's essentially it we can immediately return it it's as simple as that should we give it a try i'm definitely curious whether it works so let's go into the program file and we do exactly what we did before so extend our dependency injection builder.services.ed scoped and this time it's the offer offer search what was it search why isn't it here maybe i forgot the public yes here sorry i did a mistake let's make the class public and then i can say offer search that will make the offering search logic available through dependency injection and with that we can define an app map get let's say offers search async offer search searcher and string filter and i think if i am not wrong this is a one-liner we just say searcher dot single word search link filter essentially i don't need the async here because we don't have another way and we don't have an await the only thing we have to specify the http code that we want to return so let's put a result.ok around this stuff results.ok that adds the http 200 response code to our result of the query to make it really good we can say the filter is optional and we can do a so-called coalesc operator and specify that if the user didn't specify a filter let's take an empty string as the filter and now i'm happy get http localhost 7070 offering did i call it offerings offers okay offers search filter chair maybe we have some chairs let's see do we get some chairs yes i get chairs and i see immediately that we have chairs looks good and if you take a look at the query that was generated here is the query see you have the query with the select statement and offerings and we have a sub select from the tags it really looks nice it looks okayish so we were successful we didn't write very efficient code but we wrote code that works and that's always a good idea let's start with something that works and from there optimize it you see link is really useful for such cases link is really great it works perfectly fine i have one big complaint about this solution let's take a look here at what we get back we get the id the title the description the condition the last successful availability verification the images the subcategory the tags and so on and so on we don't need all that when you showed us your solution you made it perfectly right you said i don't need everything i just need title description and id that should be okay so why not do exactly that why not return exactly an object that contains only the things that we really need because currently we are loading the entire data set from the database server to the application server and then we send everything down the wire to angular and angular will pick only three fields from all this data and present it to the user why do we send around so much data that nobody needs we shouldn't do that never remember that it's important so what we need to practice next is how can we build a result object a result object that we can use to return a kind of offering summary to the end user you have a question exactly select is the correct thing so what we could do is we could say dot select o o goes to let's create a new object here something like this and say we want to have the title we want to have the description not the sharings but the description and we also want to have the id looks good okay but now we get a mistake now we get an error because the anonymous type is not compatible with offering we could create a record for instance we can also create a class for that we essentially have to create a new class you can choose whether you want to have a record or a class we have to create a class that represents the result and this is exactly what we need to do please follow along please go into the offerings file and here somewhere after offerings we will create a new class i will call it offering summary you can call it whatever you want and this offering summary has essentially the id the title and the description i will copy it from above so i don't have to type so much so here just the id the title and the description that's it see it let's create as we did it before a new entity type configuration class we discussed that when we created this data model i hope you can remember it and let's change it from offering entity type to offering summary entity type let's here change it to offering summary that's here change it to offering summary that chuck that looks good and there is essentially only one thing that i would like to specify i would like to say builder dot has no key has no key means in this case that it's a so-called keyless entity [Applause] that means that entity framework should ignore the id because what we get back here is not a table it doesn't have to have a unique index or something like this it's just another property this id thing here with this i can suppress that entity framework thinks let's this that this id represents a primary key then we can take the offering summary and use it as the resulting entity and it should if we add id equals and title equals and description equals quickly should go ah offering summary of course go away nice now if you want to give it a try just run it go into your request and if the demo votes are smiling yeah we see id title description great exactly what we wanted to have now we are not sending unnecessarily large results over the wire we are downloading only those record those columns from the database that we really need and we are sending only those results to the angular client that the angular client really needs i have one last thing for you and this is the advanced part of this exercise i would like to return listen closely a comma separated value list of all the tags because we were looking in the title we were looking in the description and from a marketing perspective it makes sense to display the user all the tags of the offering right so how can we generate a comma separated list of all the tags and it turns out that this is surprisingly simple let me show you how that works first we add a new property here a property let's call it string tags and give it a default value this is just string empty so now inside of the offering search we can simply say tags equal the question is how do we get it and luckily c-sharp and link support an operator which is called string.join so what i can say is i can say string.join take all the offering um take all the first i have to specify the the separator and then i simply specify that i would like to go for the tags and take select t goes to t dot tag select the tag and make all this stuff no i think it's done i think that's it we don't need to turn it into an array manually you see we just say look for the tags take the string tag and join all the string tags with a comma isn't that nice this is now iterating over an end to n relationship something which is really not simple to do and it works like a charm hopefully let's give it a try let's run it and end and and and come on yes see it now this is just a single word search compare it to your solution your solution was really good but your solution downloaded a lot of data to the web server and then from the web server to the to the angular client and this solution we are really handing over the entire search logic to the database server the database server does the heavy lifting and we only get back those data items that we really need to process understood it's the simple thing with a single word it's getting more complex when we do the multi-word search but we still have a little bit of time today in the lesson so we can take a look for it breathe in breathe out now it's getting interesting what we will do is we will now take a look at something which is really useful sometimes because before we go into the multi-word search i would like to show you how we could if we want optimize this query by using a custom sql statement if you take a very close look at the sql query that is generated by entity framework you could find some things that you might not like in this case so i want to be smarter than entity framework i want to write my own query let's do that okay let's try that let's go into our azure data studio open up a new query window here and then let's try to write the query by hand now this is not a database course you have probably learned about databases before so what i did is i came up with some sample sql statements so what you can do is you can go into github and look for this file here tag filter queries and we need to have the first lines here the first 14 lines you can copy them paste them here and if you run it you should get a result similar to the one that we just had let's quickly analyze this sql statement we haven't taken a very close look at sql statements in this course so i really don't know uh whether you are rather beginners in terms of sql or whether you already have a black belt in terms of sequel i have to make uh i i have to tell you that i love sql i've spent i think 15 years of my life mostly writing sql queries really spending days and days and days sickening over pages long sql queries it's such a great technology it's so perfect i love it it's really really powerful so no it's really good no it's really good i really like it i really really like it now let's quickly analyze what we do here and you will see that is not really complicated try to follow please we start with the offerings because this is what we are looking for the next two lines should be pretty obvious because they are very very similar to what we had before we are using the sequel like clause the like clause does exactly what we want if we add a percentage character at the beginning and at the end understand what i mean there would also be a sql operator which is called char index you could also use it and then i'm doing a sub query here sub query means that for every offering i'm checking if there is at least one offering tag where the tag is like the filter so this works perfectly fine if we want to return only those columns that we really need well that's simple just say o dot id next line o dot title next line o dot description but now we have a problem because now we really want to have a comma separated list of tags and how can we generate the comma separated list of tags well with another subquery isn't that awesome so what we can do is we can say select whatever i don't know it yet let's say star from and now we simply copy what we have down here see because we already have the join to offering tags so i can copy this stuff and that will give me all the offering tags for my current offering and then let me tell you a secret there is a string aggregation function in sql where you can just say give me the tags and separated them separate them with a comma that's it so sql can do the comma separated value on its own you don't need link for that you don't need c sharp for that sql can do that maybe let's call it as tags and if you run this guy we should see a nice little result with the id title description and the text don't worry i have created the sql statement for you the point is not to show you how great sql is the point is that sometimes in real world you need to write your own sql statements and i want to demonstrate to you how you can do that with entity framework that's the point that is what i want to show you okay so 99 out of 100 queries will work perfectly fine with entity framework but then you will have this one little query which is simply too slow or where entity framework simply doesn't support something that your underlying database could do for you and then you want to go directly to the database and this is what we are practicing here right good so don't worry this will i will not make this a database course so i created the necessary function for you go into the offering search in github and there you will find a method which says single word search sql we will copy it to our code in a second but the point that i want you to understand and please look at the video beamer now is that it is super simple to use something like this in entity framework if you know the function from sql raw so what you can do is you can paste in your entire select statement with all the greatness of sql and entity framework will allow you to pass this thing through to the database the only thing that you have to do is you have to specify the parameters inside of sql using a class which is called sql parameter understood this gives you the entire the complete freedom of sql from within entity framework good let's try that so please copy the single word sql this is not a database course so we will not spend an endless amount of time discussing the sql statement you can take a look at it on your own and we paste it here into our code yes i know we get a mistake we get an error syntax error we will fix that in a second it's uh it should be a method in offering search question is is it okay to use the link query instead of this one because you like the link clearly more yes of course but guess what in the homework you had a second part it was called multi-word search and there i consciously designed it like that you simply can't come up with the solution link link doesn't support it so there we will need it so it makes sense to practice it first in this simple example and then in the next hour we will go deeper because then we have a use case where we can't use link anymore understand what i want to do practice it simple and then try to use it in a more complex use case where we really benefit from that now the filtered offers here is an extension that we need to do in the database context and that is really simple you already know how to do that i will show it to you go into the share for future database context and simply add another db set it will return an offer oops sorry it will return an offering summary this one and simply put it just like always offering summary like that let me make it larger here that's all you have to do special i would like to see whether it works so all i do is i copy this map get the entire map get and maybe call it now search sql something like this and this time i will not search link but i will call search sql so that we have both options for us to try come on here we shall see the code exactly let's copy this one and this time we'll go for search sql and crossing fingers waiting waiting waiting and here you are see works perfectly fine and this time if you take a look at the output you see that entity framework will execute exactly the sql statement that you specified it will not try to come up with anything else it will use what you told it to do so you have the possibility to optimize if you need it if link generates a query which is not not optimized enough try to optimize your linq query if you still can't up can't come up with a proper link solution then go towards sql the problem with sql is that it is database specific if you write a complex sql query it might work on sql server but maybe it doesn't work on postgres or doesn't work on oracle with with link it's relatively simple to stay database independent if you go for uh for a handwritten sql it's harder to stay database independent but in many in many companies you have one clear choice of database many companies are either a sql server company or an oracle company or a mysql company or a postgres company they generally use one kind of database in such situations you frequently see handcrafted sql queries in order to achieve proper performance 140 so in 10 minutes we are going to continue and in 10 minutes we are going to build a multi-word search and there you will see some new features when it comes to migrations okay nice

Channel: Rainer Stropek

Views: 2,907

Keywords: C#, Entity Framework, Entity Framework Core, Database, DB, SQL, HTL Leonding

Id: b6mLJFhv_io

Length: 30min 46sec (1846 seconds)

Published: Tue Oct 19 2021