Are you working on Entity Framework and want to know how to write the query in Entity Framework? So this video is for you. In this video, I'm going to tell you about how the link query you can write.
So we have been the two methods to write the link query. And the most important is the people are not aware of how the link query is going to execute it. So when it is coming to optimize the query without understanding the execution process of link query, you cannot optimize it.
Then after that we start blaming the entity framework is slow. So the issue is not entity framework is slow. The issue is here we are not aware of how to create a dynamic query in link and how it is going to be executed. So after referring this video you will have a clear understanding how the link query is going to be executed and that definitely will help you to optimize your link query. If you are coming to this youtube channel first time I request you to please do subscribe to our YouTube channel and click the bell icon.
So let's start it with the video. A link query syntax. So when you're writing your link query, there are two ways which you can follow for writing your link queries. Now let's see what are these two ways. So the link provide us two ways like query based syntax and method based syntax.
So the query based syntax is not exactly similar to that is equal, but it looks like that way. So you can see here when you're willing to fetch all the customers from the database, we can write the query here let's say from C. So C is the alias name for the customers here. Then we are selecting here all the customers here.
So in the T SQL also we write the same style, let's say select asterisk from customer, we write the syntax. The second one here we are having method based syntax. So in the method based syntax, there are a set of extension methods you can use.
to write the same query. So we are having select method, we are having where method, we are having others methods also, which you can use to fetch the data. And when this code is going to be executed by the C sharp, so they are going to be compiled internally into the plain SQL.
Other plain SQL will be executed at the database side and it and the list of customers. So now let's see how we can use both these syntaxes with the help of linkpad. So this is my linkpad where we return the last query.
So just do one thing just add one more query window. I'm just saving it here let's say context of a name it's a let's say SQL query. Let's say links equal query. Now here I can add a connection here with the SQL server.
So this is a link to SQL optimize for SQL Server. Then next, then you define the server details. I'm using a SQL Server.
Even you can make a connection with your SQL Azure also. If you're having a database available on Microsoft Azure, you can use that one. Even you can use here, SQL compact edition also. So the link also providing us the SQL compact edition database by default.
So I'm just using a SQL Server one. So let me mention my server detail. So let me find out the server details.
So this is the server name. Let me copy here and mention here it is a SQL server authentication. Username is as a and whatever the password you have mentioned in your local database then mention it. Then test the connection. It's successfully tested.
Now specify the database which you are willing to connect. So here I'm already having a database. link demo DB this database SQL script I will share with you so that you can generate the same database on a local machine to practice the similar script. So I'm just connecting it here this database script I will share with you so that you can try on your machine also with the same queries.
So now here I'm going to write a query to fetch the list of customers. from my customer table here so i'm just using a c sharp expression such as c sharp expression so i'm just using let's say from first we need here from so it would be let's say from then i'm using a c in customers then here you can define the wear condition here if you want to apply a filter so right now i'm not putting any filter i'm just going to select all the customers here so select c now just execute it okay it is giving the error here the reason is here i have not selected any connection So make sure you have selected the connection here link demo DB. Now execute it. So now you can see here I am getting the data here for my customer. So properly it is giving the data.
If you want to apply a filter so filter I can apply let's say where c dot c dot city equal to then mention the city name here. Let's say city name is here Verlin. This I'm defining and I'll execute it. Now it is returning only one customer which belongs to the city Berlin. So this is the way how we have applied the filter.
And here if you will see I'm getting all the columns in my customer table. If you're willing to get only few columns from the customer, so the same query can be changed here. So let's see how can change the same query.
I can write here new then use here on the query c dot let's say company name. So if you are having the link pad free edition so here intelligence will not work. If you will have the paid edition so you will get the intelligence also. Let's say I am getting here company name.
I am getting here let's say customer id also. Customer id. So these are the two columns I'm willing to get. Now execute it.
Now you can see I'm getting here only these two columns. And the linkpad also provided you one more benefit. So this query we have written using the C sharp and the link syntax.
But the same query the SQL Server you can see the RDBMS cannot understand because the RDBMS can understand only T SQL statement. So what is happening here behind the scene, behind the scene the SQL statement is generated here, this is the way. So this SQL statement is generated here and ultimately this statement is going to be executed in the SQL server and it's returning the data to us.
So this is the way what the query you write in the link internally that is compiled into the plain SQL, that plain SQL is executed in the SQL server side and it returns the output. put to us. So this is happening here.
And the same query can be written here by using the method based syntax also. So this is the query based syntax we have written here. It is query based syntax.
Let me comment this code and the same code I'm just going to write here using the method based syntax. So in method there is a where method already. So And the way the same condition we can write here with the alpha lambda expression. So this is the lambda operator I'm using here. And C is representing to this customer.
So C dot city equal to Merlin. I'm just mentioning a city name as Merlin. And here you don't need to mention any select statement if you are willing to fetch all the columns from the database. So I'm just selecting this line and executing it. You can see I'm getting here all the columns here.
But if you want to select few columns, so there is option here, select, use the select method and what you are selecting here, I'm selecting here let's say new and the same way c.customerId, I'm selecting here c.companyName. I'm going to select here link pad is very smart it will show the error in red color if name is not matching so this way we have written here two method where and select not is more clear so this is for filter purpose and this is for selection purpose now let's execute it to check how it is working so you can see I'm getting here only customer id I'm getting here only company name So both the syntaxes are fine. Both are giving the same result. But this is a method based syntax.
So what are the syntax you like to follow, you can follow it doesn't mean like query based syntax is better and method based syntax is better. There is no difference in terms of performance. One of the syntax you can follow.
So usually people follow the query based When they are writing the complex query, let's say when they are writing the query for joining that more than two tables. and when they are fetching the data only from one table they usually prefer the method bin syntax. A link query execution. So what are the link query you write? Now let's understand how that query is going to be executed by the link here.
So every query is executed here by two ways. Like here it is a default execution, it's a immediate execution. So, let's understand how the default execution works and how the immediate execution work here. So, when you're talking about the default execution, so default means later on, it means query would not be executed immediately, wherever you have written, the query would be executed whenever you will use the query variable. So, let's say if you have written a query, let's say from C in customers, and select C.
And now let's say this query you're assigning to a variable. Let's say where q equal to so in the q variable you will not get the result. The result you will get when actually you will use that q variable.
So that q variable you can use for displaying the data using a for loop or you can actually call here on the queue variable, let's say tool list method. So that time that query would be executed. And when we are talking about the immediate execution, so in immediate execution query would be executed immediately. So if this query you have assigned to a variable, let's say where queue equal to.
So the queue variable you will get all the customers available in the database. So it is going to be executed in the same statement. So, here the deferred execution query result set is stored as a in memory of the variable. But in case of deferred execution, the result is not stored in the variable. it always return the real time data from the database.
So whenever you will use that query variable that time whatever the data you would have in the database that will return that actual data. But in case of immediate execution, whatever the data is available that time, when this query is executed will return and store to that variable. after that variable you can use as a local like in memory data source for doing the further manipulation now let's see how the things are working here so we already having a connection with the sql database and we are we have queried here customer table last time so this is a deferred execution query because it's not going to execute it immediately So, right now it's expression based syntax.
Let me convert these two queries into the statement. So, what I'm doing here, I'm just going to copy this query and just putting here and this time using here the statement and make sure you have selected the connection also. I'm just assigning this value to a query variable q this way.
and save it here let's say link query execution so here in this queue you will get only the generated SQL layer so it will compile to this line but it will not be executed it would be executed when I will use this queue variable let's say I'm using this queue variable using the for each statement Let's say where item in queue. Now I can console its output. Console dot write line. I'm using here item this way. To see the result let's execute it but make sure we are just getting an object so object value need to return here we need to print here let's say company name so object value i'm just consoling here let's say this way now execute I'm getting here this is the name of the company where this customer belongs.
So this is the way here at this point the query is going to be executed. So it is a default execution query if you want to understand here it's a default execution set will not contain the result set in the line number two, the result set it will contain when this line number five would be executed. So this is the point where the query execution would be done.
So you should use the default execution set syntax whenever you want to get always real time data from your query. And even whenever you are willing to reuse the same query again and again, because it always give the real time data, it is not storing anything in the queue variable. So always whenever you will use this queue variable, it gives you the actual data available that time in the database.
So it also increasing the reusability of the query. to provide the real time data. So this is the use case where you should use default execution.
But sometimes it happens, we don't want to reuse the query because we are not willing to get the real time data because our data is not going to be changed. So we just need to get the data one time. And after that, we need to use that variable again and again.
So that time, what we need to do here, we need to explicitly execute this query by calling the method. So you can call here method, let's say dot to list, we can call it a method dot to array. So after calling these two methods, what we are doing, we are forcing this query expression to execute immediately to be converted into the list type so whenever you're calling the method dot to list dot to array the query would be executed immediately so this is the immediate execution of the same query so now query is already executed so here you will get only the stored data and this q variable Now let's see, I am getting the same output.
So output would be same, the difference would be here only how you are using this Q variable. So for query reusability default execution syntax should follow. For storing the result set, you should use immediate execution.
Now the question is here, how to know whether the query is going to execute it immediately or not. So this can be understand with Delphob. Link to query operators.
So if you are using the conversion operators like aggregate operators, then your query would be executed immediately. So these are the conversion operator, let's say cast, to list, to array, to dictionary as enumerable. And the aggregate operators are aggregate, average, count, sum, min, max. If one of the operator you are using, while writing your link query, so it would be the immediate execution of your query if you are not using these operators then your link query would be executed in different way so query execution is very important when you are going to apply so many condition while writing the query so my suggestion is here when you are putting so many filters on your query so make sure you have written your query in default execution way Don't follow the immediate execution in that case. Otherwise, the filtration would be done in your application program memory.
So filtration should be on the server side, not in the application program memory. So that time the default execution query will help you. So this is about the default execution and immediate execution you should know.