Advanced C# – LINQ Tutorial

Key Takeaways

learn about Link in this Advanced c-sharp course Gavin long teaches this course he is an experienced developer and course creator and also a free code Camp team member link provides a set of operators that enable developers to query protect and filter data in arrays Collections and other data sources it can be used to query and manipulate data from databases XML documents and net objects hi and welcome I'm Gavin long I've been a full stack software engineer for over 20 years about three years ago I created an advanced c-sharp course on the gavinlan YouTube channel you'll find the gavinlan YouTube channel at this location and you'll see a playlist for the advanced c-sharp course on the homepage of the gavinlan YouTube channel here is the URL for the advanced c-sharp course playlist the idea behind the course was to explain the more advanced c-sharp features of the language in an easy to understand way to help c-sharp developers who are familiar with the basics of the language Advance their skills to the next level the course may also help those developers who may use the advanced features of c-sharp like for example Link in their day-to-day tasks but perhaps don't fully understand the Technologies at a deeper level free code Camp has kindly allowed me to share a subset of the advanced c-sharp course it's the seventh part of the advanced c-sharp course with you on the free code Camp YouTube channel so in this video we'll look in detail at a set of Technologies named Link Link stands for language integrated query and is very powerful where sophisticated queries can be made against collections of objects and for example you can perform filtering ordering and grouping operations on data sources with a minimum of code I hope you enjoy this course and benefit from it [Music] hi and welcome to the first part of a video series on link this video is also the first part of the seventh tutorial in an advanced c-sharp course later in this tutorial we're looking at a code example to help with our understanding of Link but let's first answer the question what is link link stands for language integrated query link is defined in the Microsoft docs as the name for a set of Technologies based on the integration of query capabilities directly into the c-sharp language link was originally released as a major part of NET Framework 3.5 in 2007 here is a paragraph from the Microsoft docs which provides an excellent overview of what link is language integrated query link is the name for a set of Technologies based on the integration of query capabilities directly into the c-sharp language traditionally queries against data are expressed as simple strings without type checking at compile time or intellisense support furthermore you have to learn a different query language for each type of data source SQL databases XML documents various web services and so on with link a query is a first class language construct just like classes methods events you write queries against strongly typed collections of objects by using language keywords and familiar operators the link family of Technologies provides a consistent query experience for objects link to objects relational databases linked to SQL and XML link to XML note that you can write link queries in c-sharp for SQL Server databases XML documents ado.net datasets and any collection of objects that support the I enumerable interface or the generic I enumerable interface so for example you can perform link queries against collection types like an array generic list generic dictionary generic Q generic stack Etc basically any collection type that implements the I enumerable interface or the generic I enumerable interface link support is also provided by Third parties for many web services and other database implementations you can create custom providers using I queryable custom providers will not be discussed in detail in this video series if you would like to learn more about implementing your own custom provider please navigate to this URL in this video series we'll primarily focus on linked to objects and link to entities linked to entities provides language integrated query link support that enables developers to write queries Against The Entity framework conceptual model using either Visual Basic or visual c-sharp so Entity framework core is a modern object database mapper for net it supports link queries change tracking updates and schema migrations Entity framework core or EF core can be used to work with many databases including SQL databases on-premises and Azure sqlite MySQL postgres and Azure Cosmos DB in this video series we won't look at link to data set or link to XML please navigate to this URL for more information on linked dataset and please navigate to this URL for more information on linked to XML to get started with link in this video we'll focus on link to objects so that we can first get a basic idea of what the implementation of Link looks like in code and how it can be used for querying c-sharp collections so in the spirit of cross-platform Development I've decided to demonstrate this code example on my Mac platform using visual studio 2019 for Mac Community Edition if you have a Mac platform and wish to follow along on your Mac and you don't yet have Visual Studio installed you are able to download and install Visual Studio Community Edition 2019 free of charge from this location all links mentioned in this video are available Below in the description if you are using a Windows OS please don't let the fact that I'm using a Mac platform put you off following along with this tutorial in terms of the code example that we'll create in this video the coding experience using visual studio for Mac is almost exactly the same as the coding experience using visual Studio for Windows so this should not be a barrier for a learner to follow along with this demonstration if you haven't yet installed Visual Studio 2019 on your windows platform please view a video created by this channel which provides a guide to installing Visual Studio 2019 Community Edition free of charge so firstly let's create a.net core console application let's name this project the pretend company application before we write calling client code in the main method we are going to add two standard Library projects let's first add a standard Library project named TCP data let's then add another standard Library project named TCP extensions foreign so let's now go to our project that we named TCP data let's delete the class that has been automatically generated for the TCP data project let's add a new class named employee let's add six autoimplemented properties to the employee class ID as int first name as string last name as string annual salary as decimal is manager as Boolean and lastly Department ID as int okay let's add another class to the TCP data project let's name this class Department let's add three Auto implemented properties to our department class ID as int short name as string and long name as string let's add a class named data to our TCP data project let's make this class a static class so let's add two public static methods to the data class let's first create a static method named get employees which returns a generic list strongly typed as employee let's ensure that we include the appropriate using directive for the use of generic list objects at the top of our code editor so let's include a using directive to the system.collections.generic namespace like this let's write the code to add some made up employee records to a generic list that is strongly typed with the user-defined type employee foreign let's write code to return the list of employee records to the calling client code foreign let's add another static method and let's name this method get departments this method also returns a generic list object but this generic list object is strongly typed as Department let's write the codes to add some made up Department Records to a generic list that is strongly typed with the user-defined type Department foreign let's write code to return the list of Department Records to the calling client code foreign so as you can see the get employees method returns a list of employee records and the get departments method returns a list of Department Records so in order to gain an understanding for how Link Works let's ensure that we properly understand two fundamental concepts I.E the concept of extension methods and the concept of Lambda expressions so let's go to our TCP extensions project let's delete the class that was auto-generated by Visual Studio when we created the project let's add a class named extension so in order for our extension method to work we must make the extension class a static class so we are going to add a method named filter the idea here is we want a way where we can extend the functionality of any generic list that is strongly typed with any c-sharp data type including user-defined types we can achieve this by creating our own extension method so for example if we want to filter employee records by criteria that we are able to specify through a Lambda expression on a generic list that is strongly typed as the employee we can do this through our filter extension method this will be demonstrated in the calling client code in a bit so how do we create our own custom extension method so I'll first create the method signature for the filter extension method first notice that our method like the class in which it resides must be static which is achieved by including the static keyword in the relevant extension methods method signature so if we look at the first parameter in the filter methods method signature we can see that we are using for this keyword preceding the data type of the first parameter which is a generic list of type t this keyword in this context indicates to the compiler that the relevant method can be called on an object of the data type of the relevant parameter in this example that is a generic list which contains a type T placeholder as we know from the video series on c-sharp generics a developer is able to pass a data type argument to the parameter of type T which can be any c-sharp data type including user-defined types the data type specified will be chosen depending on the requirement that needs to be met by The Calling client code we'll demonstrate the consumption of client-side code for the filter extension method in a bit so let's look at the second parameter which is of the delegate type func for more information about the func delegate type please view a video created by this channel on this topic a link to this video is available Below in the description the first data type parameter passed to the funk delegate type represents the first data type of the first parameter of the method referenced by the relevant argument passed into this parameter the last parameter in the funcdelegate type definition is the return type of the method referenced by the argument passed into this parameter so the first parameter passed into the funk delegate is of type T and the second parameter is of type bull note that a predicate delegates type can be used to define method definitions for a method that returns a Boolean value so we could replace the funk delegate type with a predicate delegate type let's use the funk delegate type but please note that we could also achieve the same end using the predicate delegate type and as you can see this method will return a list of objects of type t to the calling client code so let's complete the logic for this method let's implement the code for the filter method so basically we are looping through the list on which the filter extension method is invoked we are then adding its elements to a new generic list also of type T whether or not we add this list item is based on the Boolean value returned by a method reference passed into the second parameter of the filter method so the relevant method reference is stored in the argument passed to the parameter named funk as you can see the method that the variable named Funk references accepts an argument of type t so we are passing in each item stored in the calling clients list which is the generic list on which calling client code will invoke the filter extension method if the method that the func variable references returns true the items stored in the iterated list is added to a new list we then return the new list containing the filtered items to the calling client code so this effectively means that the calling client code is able to use our filter method to filter any generic list based on a method created in the calling client code this method created in the calling client code will contain the logic for filtering the relevant generic list a reference to this method will be passed into the second parameter of our filter extension method and must adhere to the method definition represented in the second parameter of the filter method I.E the definition represented in the func delegate type so we are now going to implement the calling client code and you'll see that the method passed into the second parameter of our filter method will be an anonymous method I.E a method that is not named this method will be expressed as a Lambda expression we'll now see how we are able to filter a generic list using our filter extension method through calling client code in the main method of our application right so let's go to the.net core console project and write the calling client code in our main method firstly let's reference our two standard Library projects from our.net core console project so let's right click the dependencies node in the.net core console project and tick TCP data as well as TCP extensions so let's test our filter extension method in a bit I'll further elucidate why it is important to understand extension methods and Lambda expressions with regards to link through the magic of how extension methods work in c-sharp we should be able to call the extension method on any generic list object so let's define a generic list that is strongly typed as the employee user defines type so we have a red squiggly line under our generic list type definition so to fix this we need to include a directive at the top of our code to the system.collections.generic namespace we can see that there is a red squiggly line under the code that strongly types our generic list as the user's defined type employee so to fix this let's include a directive at the top of our code to the TCP data namespace great so through the magic of extension methods we should see the filter method appear in the intellisense drop-down list when we add a DOT after the employee list variable okay so why are we not seeing the filter method appearing in our intellisense drop-down list the reason for this is because we haven't included a using directive to the appropriate namespace so let's include a using directive at the top of our code to the appropriate namespace IE TCP extensions so now if we add a DOT immediately after the employee list variable the magic of extension methods is revealed to us and we can see our filter method in the intellisense drop down list so note that when we Open brackets to pass in appropriate arguments to the filter method we see that only one parameter is required this parameter was the second parameter we defined for this method so why is the first parameter in the filter method not listed when we open the brackets in this way well the answer to that question is this is how extension methods work the consumer of the code IE the calling client code does not need to pass in the first parameter this is automatically handled behind the scenes as it were by default the first parameter is the object on which the relevant extension method is invoked so in this case the list of employees stored in the employee list variable is passed into the filter method into this parameter automatically so if we look at the filter extension method we can see that this keyword precedes the definition of the first parameter that this keyword in this context is telling the c-sharp compiler that the argument passed into this parameter is the object on which the relevant extension method is invoked so we have access to the argument passed into the first parameter automatically okay so the second parameter accepts a delegate object that points to a method that needs to be created on the calling client so this method that we'll create essentially tells the filter method how we want our list filtered so we are able to Define this method and the calling client code through the use of a Lambda expression a Lambda expression is a concise way to represent an anonymous method if we look at the definition of the relevant delegate parameter we need to define a method that accepts an argument of type T and returns a Boolean value so the c-sharp compiler has enough information to infer that the intended type for type T is employee in this context we are calling the filter extension method on a strongly typed generic list the generic list is strongly typed with the employee user defined type so say we want to filter our employee list based on the value of each relevant employee items is manager property we want to filter the list where we only want to return those employee records where is manager is true so we want to return all the records from our list where the relevant employees are managers so to achieve this we can pass in this Lambda expression here we are telling the filter method how we want our list filtered let's write code to print the results to the console screen foreign let's run the code okay so we have no data ah we of course need to populate our employee list with data no so let's assign the value returned from data.get employees to the employees list variable right let's run the code great and you can see that only the employee records whether relevant is manager property value is set to true or returned to the calling client code great and we can of course inject the converse of this logic into our filter method through the relevant Lambda expression like this so we are now saying we only want to bring back employees who are not managers let's run the code great and for good measure let's modify our Lambda expression so that the filter method only returns employees that earn less than 50 000 a year great and because we have used generics for our filter method we can call the filter method on a list of various c-sharp types including user-defined types so let's prove this and call our filter extension method on a list of departments foreign let's filter the Departments based on the Department's short name so let's bring back the finance department and the human resources department foreign let's bring back only those departments with IDs greater than one so the point of this is to show you the flexibility that the combination of extension methods and Lambda Expressions create when writing the calling client code the combination of extension methods and Lambda Expressions not only provide this flexibility but at the same time ensure that the calling client code is clean and concise once we understand how extension methods and Lambda Expressions work they are incredibly powerful tools so how do extension methods and Lambda Expressions work in relation to the topic of this video link language integrated query extension methods and Lambda expressions are fundamental to how link is implemented in c-sharp the enumerable class and the queryable class are two static classes that contain extension methods the innumerable class and the queryable class are both members of the system.link namespace so to gain access to the extension methods provided through link we can simply include a using directive to the system.link namespace without including a using directive to the system.link namespace you can see that we are not able to access the where extension method provided by link to perform our filtering operation on a collection that implements the I enumerable interface so let's include the system.link namespace we now have access to Links where extension method so we can now replace our custom filter extension method with the where link extension method like this we don't need to create our own filter extension methods to perform such queries on our data link has got us covered for this you know link also provides us with a SQL like query syntax so for example if we wanted to produce a query result by joining selected fields from data selected from our collection of employee objects with selected fields or field in this case from data in our collection of Department objects we can use Link query syntax to perform our inner join operation on these two collections of data like this and the link query we want to include the department long name field so that we can have a descriptive name for our department in our results set rather than displaying a result that only contains the department ID which is available in the employee type so we can achieve this result by using the join clause in a link query to perform our inner join on our collection of employee objects with our collection of Department objects we have the department ID property in the employee type so we can join using this department ID property in the employee type to the ID property which is a member of the department type so say we were to write the equivalent query using transact SQL against the SQL Server database that contained an employee table with the same data schema as our employee type and the department table that contains the same schema as our department type the transact SQL query syntax would look something like this we are using an inner join to join data in an employee table with data in a department table we are able to make the join because the employee table contains the department ID which can be joined in our query the ID of the department table so you can see the similarities and syntax between the transact SQL syntax and the link query syntax the link query is dealing with objects encapsulating the employee and Department Records the employee list collection is like the employee table and the department list is like the department table in this particular context we have a select Clause a from clause and a join clause in the Syntax for both the link query and the transact SQL query however the from clause in the link query appears at the beginning of the query and the select clause in the link query appears at the end of the query so there are differences in The Ordering of the Clauses When comparing the syntax of the transact SQL query with the syntax of the relevant link query an advantage of link is it can be leveraged in our c-sharp code to query data in a standardized way that is independent from the format of the underlying data source the data source could be a mongodb database a sqlite database a SQL Server database Etc this will not affect the syntax of our link queries so the object layer provided by link can be used for abstracting our queries of the data from the underlying data source and by using link we also have the added advantage of compile time strong typing and the type checking provided by the c-sharp compiler let me draw your attention to the select clause we are able to return the data as an i enumerable collection of anonymous types we are assigning The Collection returned by the link query to a variable preceded by the VAR keyword beginning with c-sharp version 3 variables that are declared at method scope can have an implicit type VAR through the use of the VAR keyword the c-sharp compiler is able to infer the types included in the collection returned from the query so we are able to return a collection of objects that are of an anonymous type that we have defined directly within the link query Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to explicitly Define a type first you can see how the use of anonymous types can provide the developer with flexibility and convenience when constructing relatively complex queries okay note that Microsoft recommends using the query syntax where possible as opposed to using relevant extension methods for the same purpose this is because of the readability Advantage provided in the relevant query syntax there are however times where you'll need to use appropriate extension methods included within the link namespace because some functionality in link does not have a query syntax counterpart for example if we wanted to calculate the average salary for all employees we would need to use the average extension method like this the same applies if we wish to query for the highest salary from a list of employee records we would need to use the max extension method like this this functionality cannot be performed through the use of query syntax but where possible query syntax is preferable due to the code readability advantage that this syntax provides I hope you've enjoyed this video which provides an introduction to link this video was created to help with the understanding of the fundamentals of Link the advantages that link provides and how we are able to implement basic link queries in code link provides us with an integration of query capabilities directly into the c-sharp language it is a very powerful technology that decouples query logic from relevant types of underlying data formats link provides a layer of abstraction that provides the developer with easy to implement Syntax for querying collections of objects the relevant collection types must implement the I queryable or generic I queryable interface or the innumerable or generic I enumerable interface note that the I queryable interface inherits from the I enumerable interface and the generic I queryable interface inherits from both the I enumerable interface and the generic I enumerable interface a suite of extension methods and query syntax are made available to c-sharp developers which provide flexibility when constructing powerful readable queries against data encapsulated with an appropriate c-sharp collections this video is a basic introduction to leveraging link in the c-sharp language and the next tutorial we'll delve deeper into the subject matter [Music] hi and welcome to the second part of a video series on link this video is also the second part of the seventh tutorial in an advanced c-sharp course link stands for language integrated query in the first part of this video series we looked at an overview of Link we discussed that link is the name for a set of Technologies based on the integration of query capabilities directly into the c-sharp language in this video we'll demonstrate examples where we'll create link queries using methods syntax and subsequently show how we can express the same link query using query syntax as discussed in the previous video in this series query syntax is recommended because the logic within the query syntax is easier to read in this video we'll look at code examples using the following link operators select where join and group join we'll discuss what is meant by Method chaining and demonstrate an example that makes use of method chaining in the context of a link query we'll look at what is meant by deferred execution of Link queries and what is meant by immediate execution of Link queries we'll discuss the inherent advantages of deferred execution in the context of Link to further clarify our understanding of deferred execution we'll create a code example where we'll Implement deferred execution for our own custom extension method for an I enumerable type we'll demonstrate how the c-sharp yield keyword is implemented in the relevant code example as part of code to implement deferred execution at the end of the tutorial we'll look at the join operator and the group join operator we'll create code examples to help clarify our understanding of these two link operators in the first part of this video series we discussed that extension methods and Lambda expressions are fundamental to how Link Works we created our own extension method for filtering a generic list that can be strongly typed with any c-sharp type including user-defined types we then demonstrated how a Lambda expression can be created and calling client code which enables the calling client code to determine how the relevant list of data must be filtered the reason we created our own filter extension method was to help us understand the fundamentals of Link Link functionality is encapsulated in two static classes that are members of the system.link namespace these two static classes are named queryable and enumerable these classes mostly contain extension methods as with the previous video in the series I'm going to demonstrate the code examples in this video on my Mac platform if you are using a Windows platform the fact that I'm using visual studio for Mac should not be a barrier to follow along with these code examples the code creation process is almost exactly the same experience when compared to implementing the same code using visual Studio for Windows so let's create a net core console project let's name this project link examples underscore one so to save time let's copy data related code that we created in the previous video of this series from the appropriate GitHub repository so please navigate to this URL in your browser to access the relevant GitHub repository as always all urls referred to in these videos can be accessed through appropriate links included Below in the description so let's copy the data related functionality from this repository to the project we have just created so in your browser window click the directory icon labeled TCP data like this click the class icon labeled employee let's copy the code for the employee class from this page to our clipboards like this and simply paste the contents of the clipboard below the program class in our new project let's also copy the code for the Department class like this to our clipboards and paste the contents of our clipboards below the employee class within our new project let's then copy the code in the data class to our clipboards foreign then paste the contents of our clipboards below the department class in our new project like this so we have some red squiggly lines within our data class to fix this let's include a directive to the system.collections.generic namespace at the top of our code like this we are just creating throwaway code in this video to help us understand the relevant topics discussed in this video so we are not too concerned with code neatness in this video in our main method let's create a strongly typed generic list that is strongly typed with the employee user defined type let's name our generic list variable employee list let's assign the value returned from the data.get employees method to the variable we have just named employee list this line of code will populate the employee list collection with made up data that was hard-coded in the data class in the previous video of this series let's define a generic list that is strongly typed with our department user-defined type let's name our variable for this list Department list let's assign the value returned from the data.get Departments method to the variable we have just named Department list this will populate the department list collection with made up data that was hard-coded in the data class in the previous video of this series so let's create a link query that makes use of the select link operator in order to use a link operator we of course need to include a using directive to the system.link namespace for this query we want to select everything from the data stored in our employee list collection we can do this by simply invoking the select method on the employee list object like this the select method is an extension method provided to us through link we can shape our data through the use of a Lambda expression like this we discussed Anonymous types in the previous video of this video series so the select operator returns an i enumerable collection of objects the Lambda expression passed into the select extension method provides an instruction on how we wish to shape each item of data within the returned I enumerable collection this is specifically expressed through this Anonymous type Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to explicitly Define a type first you can see how the use of anonymous types can provide the developer with flexibility and convenience when constructing queries so here we are providing an instruction that we want each item of data in The Returned I enumerable collection to contain a full name property which is a concatenation of the first name and the last name properties of the relevant employee record and a property containing the employee's annual salary so let's write the code to Output the results of this query to the console screen let's see the code in action great so if we want to filter this data we can do this by chaining the where extension method to our select operator like this method chaining is a common Syntax for invoking multiple method calls in C sharp each method returns an object allowing calls to be changed together in a single statement without requiring variables to store the intermediate results so the select operation will return an innumerable collection object we can filter this returned innumerable collection within the same link query by chaining the where extension method to the select extension method the where extension method filters the data stored in the inumerable object returned from the select method through a Lambda expression passed to the where extension method the Lambda expression provides instructions to the where extension method on how the data must be filtered once the data has been filtered through the wear extension method and our innumerable collection of results is assigned to our results variable we are then traversing our results using a for each Loop and displaying each item in the I enumerable collection to the console screen we discussed the VAR keyword which allows for local implicit typing within methods in the previous video of this series please view the previous video in this series for more details so here we are implementing link explicitly using the extension methods provided by link we are using the select operator and the where operator in a link query implemented using method syntax let's run the code great we can execute the same functionality using query syntax so let's comment out the previous code and include a comment to label this code so that we are able to refer to this code later if need be so let's first implement the link query using query syntax without the where clause foreign let's run the code great let's implement the link query implemented using query syntax and this time include the where Clause within the link query using query syntax let's run the code excellent and we get exactly the same result when compared to running the query that we implemented using method syntax but we have constructed our link code using link query syntax as opposed to using relevant extension methods the query syntax is more readable and we don't need to chain our extension methods as we did in the previous example where we changed the where extension method to the select extension method let's take a closer look at our query where we used method syntax versus the same query where we used query syntax note that standard query operators in our query syntax are converted into extension Methods at compile time so standard query operators are essentially the same as their counterpart extension methods Microsoft recommends using query syntax as opposed to directly implementing the relevant extension methods where possible as discussed this is due to the readability Advantage inherent in the query syntax there are times when you will need to use extension methods directly where equivalent query syntax is not available we looked at a few examples of this in the previous video of this series for example using aggregate functionality like for example finding the average Min or Max annual salary for a collection of employee records we would need to use the respective average Min or Max extension methods to calculate and return the relevant values the developer is able to execute link queries with deferred execution or immediate execution so let's first understand what is meant by deferred execution deferred execution means that the evaluation of an expression is delayed until its value is required this improves performance because only necessary executions are carried out I.E at the point where the relevant value is required let's use our last query example to illustrate this point so when we first assign the value returned by our query to our results variable the query is not executed it is only when we Traverse the items in our results variable that the actual execution takes place I.E with each iteration of the for each Loop an execution is performed whereby the relevant item is evaluated and returned from the collection another advantage of deferred execution is that an update to the relevant data collection will immediately be reflected in the results so for example let's add a record to our employee collection note the salary for this new employee is above 50 000 so this employee should appear within our results let's run the code and you can see that this is proof that the results are evaluated within the for each Loop and the query itself is not executed at the line of code where the relevant query is located the execution of the query is deferred until the results are traversed within the relevant for each Loop to further understand the concept of deferred execution let's create a code example where we'll Implement deferred execution for our own custom extension method for an innumerable type that is strongly typed as the employee user-defined type let's create a static class named innumerable collection extension methods let's create a static method within the static class that we have just created and let's name this method get high salaried employees we discussed extension methods in the previous video for this video series for more information on extension methods please access a link to this video as discussed a link to this video is available Below in the description so let's implement the code for the get high salaried employees extension method like this note the use of the yield keyword you can use a yield return statement to return each element one at a time the sequence returned from an iterator method can be consumed by using a for each statement or link query each iteration of the for each Loop calls the iterator method when a yield return statement is reached in the iterator method the relevant value in the eye innumerable collection is returned and the current location in code is retained execution is restarted from that location the next time that the iterator function is called let's go to our main method and Implement a query that calls our extension method on the employee list collection let's copy and paste the code that we have already written to Traverse the results and output the relevant data and let's add another employee record to the employee list collection as we did before foreign and you can see that the new employee record is reflected in the results the results are kept up to date due to deferred execution note that deferred execution re-evaluates on each execution which is known as lazy evaluation the converse of deferred execution is immediate execution immediate execution means the relevant query is executed immediately to execute a query immediately we need to apply an appropriate two conversion method to the query so let's copy the previous code example let's uncomment the relevant code let's change this code so that the query executes immediately we can use the two list conversion method for this purpose we are using query syntax here so in order for us to use the toolless conversion method we first need to wrap the relevant query in Brackets add a DOT and then the to list conversion method we are explicitly typecasting the I enumerable value returned from the query to the list type through the implementation of the two list conversion method we are also executing the query immediately let's uncomment the rest of the relevant code for this example and let's run the code you can see that the execution of our query is immediate because this time after we've added a new employee the query has not been evaluated again to include the new employee record and the results this is of course why the new employee does not appear within the results output to the screen this proves that our query has executed immediately at the line of code where the query is located and remember that query syntax does not support two operators so we need to wrap our query in Brackets as we have done here and call whichever two method is appropriate for example to list two array or two dictionary by calling the relevant to method in this case the two list method we are causing our query to execute immediately without the two conversion method the relevant query execution would be deferred until the relevant results are iterated so let's move on to the join operator and then we'll look at the group join operator and highlight the difference between these two join operations in the previous video in this series we looked at the join operator and discussed how we can perform an inner join on two inumerable collections using a link query so here we have two collections that we wish to join in this specific use case we need to include a descriptive Department name in our results displayed to the user we only have a department ID field in our employee type so in order to merge the properties from our employee type with the relevant property from our department type we need to join records of type Department that reside within our department list collection with records of type employee that reside within our employee list collection we can do this by matching the ID field in the department records that reside in the department list collection with the Department ID property of employee records that reside in the employee list collection we can do this through link by using the join operator later we'll demonstrate a join operation but using the group join operator we'll also discuss the differences between the join operator and the group join operator let's look at the relationship between a collection of Department Records with the collection of employee Records Department Records have a one-to-many relationship with employee records a specific Department can have many employees as members a specific employee cannot belong to more than one department at a point in time data collections can also have one-to-one relationships with one another or many too many relationships with one another so in this example we are going to look at how we can use Link to implement a join operation between two collections of records that have a one-to-many relationship with one another I.E one Department to many employees so let's Implement a query that joins the department list collection with the employee list collection where the ID property of the relevant objects of type Department match the department ID property of the relevant objects of type employee so the Syntax for this link query would look like this when we implement the join operation using method syntax so to join Department Records to appropriate employee records we can use the join extension method and invoke the extension method on the department list collection the first argument we need to pass into the join method is the collection on which we want to join the department list collection so we pass in the employee collection object here the second argument is the key from the outer collection the department list collection so the outer join key is the ID property that resides in the department type we can express this argument as a Lambda expression like this the third argument is the inner join key which is the department ID property which is a member of the employee type we can express this argument as a Lambda expression like this so we are joining on the ID property which is a member of the department type with the Department ID property which is a member of the employee type we can define an anonymous type where we are able to shape the data for each item in an i enumerable collection that we want returned from the relevant query here we are defining an anonymous type within our link query that includes the employee full name which is a concatenation of the employee's first name and last name the employee's annual salary as well as the relevant Department objects long name property the long name property will give us a descriptive Department name in our results we are including the department types long name property and the relevant Anonymous type this is the whole point of joining the department list collection with the employee collection so that we are able to include a descriptive Department name within our results we only have the department ID as a member of our employee type so if we only return to results from the employee list and wanted to include Department information we would be restricted to the department ID property which is an integer value and is unlikely to be meaningful to a user foreign thank you let's run the code great but as discussed where it is possible to use Query syntax rather than method Syntax for our link queries we should use Query syntax because the logic expressed and the relevant query is far easier to read so let's write the same query using query syntax okay foreign as you can see it is far easier to read and it is also similar to query languages with which we may already be familiar for example transact SQL let's Express what a similar query and transact SQL might look like so let's imagine that we have a table in a SQL Server database that could map to our employee type let's say this table has the same name as our employee type let's also imagine that we have a table in the same SQL Server database that contains a table schema that could map to our department type let's say this table has the same name as our department type so the transact SQL would look something like this you can see how the from Clause the join clause and the select Clause are implemented in both the link query syntax and the transact SQL syntax even though the link query contains the same Clauses in a different order to the comparative Clauses in the transact SQL query for someone who is familiar with transact SQL and not necessarily familiar with link query syntax this link query syntax at a glance should be fairly easy to understand so the point here is that link query syntax is similar to query syntax with which many of us will already be familiar so this makes the implementation of Link queries using query syntax more intuitive as discussed the link query syntax is also more readable than the equivalent link query implemented using method syntax so the key takeaway here is to use Link query syntax rather than method syntax where possible because it is more intuitive to implement and easier to read let's run the code great so let's look at the group join link operator and while we might want to use the group join operator in our link queries we have just performed an inner join using the join operator this query will only return those records based on the ID property of relevant objects of the department type in the department list collection matching with the Department ID property of relevant objects of the employee type in the employee list collection diagrammatically an inner join query can be represented like this what if we wanted to return through a link query all the Department Records in our department list collection and only employee records where the department ID property of employee objects and the employee list collection match the ID property of Department Records in the department list collection so if we were to write to the transact SQL version of this query it would look something like this we would use a left outer join to achieve the desired result this query would bring back all Department Records but only those employee records where the relevant employee records department ID values match the ID property of the relevant Department Records so diagrammatically we can represent a left outer join query like this so let's look at the diagram representing an inner join operation and compare the diagram to the diagram representing the left outer join operation so let's use our group join operator in a link query to return all Department Records from the Department list collection but only the employee records from the employee list collection with a department ID property that matches an ID property of a department record in the department list collection so here we are implementing our query using method syntax you can see the implementation of the group join operation using method syntax is very similar to The Way We implemented the join operation in the previous example let's run the code note the way we have structured our output is clearly different when outputting the results of our group join query when compared to the way we structured our output of the results of our join query but note that at the moment the same join between Department Records and employee records has occurred the reason for this is not because the group joined a