Advanced C# – LINQ Tutorial

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 and the join operators perform the same type of join operation it is because currently each of the records in the employee list collection match with a department record in our department list collection let's change our data to highlight a key difference in the join operation and the group join operation let's do this by going to the code where we have hard-coded made up employee and Department Records and added these records to their respective collections let's ensure that the technology department which has an ID of three does not match with a department ID property of any of the employee objects in our employee list collection we only currently have one employee that is in the technology department according to our data let's change the relevant Department ID so that there are no employees that are members of the technology department so let's change this department ID property now no employees are members of the technology department according to our data let's run the code so you can see our technology department record is being returned along with all the other department records in the department list collection even though its ID property doesn't match the department ID property of any employee records stored in the employee list collection so simply put Department information for departments that contain employees are included in the query results and departments that don't contain any employees are also included in the query results in this example the technology department has returned even though it contains no matching employee records so here we have performed a left outer join query using links group join operator so let's rewrite this link query using query syntax you can see here that the group join extension method does not get explicitly called when we Implement our query using query syntax the into keyword is used within the query to create a group of employees for each department so as discussed link query syntax does not match exactly when comparing link query syntax to familiar query languages like transact SQL but in many ways it does resemble a language like transact SQL in this sense link query syntax is more intuitive to implement than method syntax when implementing link queries and just to labor the point as discussed it is also better to implement link query Syntax for our link queries then implementing method Syntax for the same purpose because of better readability let's run the code great and we get the exact same result as when we ran the same query that was implemented using method syntax now that we have changed the data so that the technology department does not match with any employee records let's run our join operation that we created earlier notice that the results of the join operation doesn't include the technology department this is because the join operation performs an inner join and with the group join operation we are able to perform a left outer join operation so let's comment out our join query and uncomment our group join query and for good measure let's run the code again and as you can see the technology department is now included in the results displayed to the screen even though the technology department does not have any matching employee records great so the left outer join operation returns all records from the outer left collection in the query but does not return records from the inner collection where the inner collection records do not match any of the outer collection records so if there were employee records in the employee list collection that did not match any of the Department Records in the department list collection these employees would not appear in the results outputted to the screen we can prove this let's change the department ID of this employee record where the first name property is Bob and the last name properties Jones we have changed the department ID property for this record to six and there's no matching Department record I.E no Department record has an ID of six let's run the code and you can see that the employee record with first name Bob and last name Jones does not appear within the results however even though the human resources department has no matching employee records it is still included in the results output to the screen great let's add Bob Jones back into the human resources department like this and let's run the code excellent so hopefully this made it clear how we are able through link to use the join operator to perform inner join queries between two object Collections and how we are also able to perform left outer join queries using the group join operator [Music] foreign [Music] to the third part of a video series on the link technology using c-sharp this video is also part of the seventh tutorial in an advanced c-sharp course link was introduced with c-sharp version 3 which was released on the 19th of November 2007. link stands for language integrated query in this video we are going to focus on code examples that make use of several link operators the code examples demonstrated in this video will hopefully help us gain an understanding of a broad array of operators that are made available to us through link we'll learn how we can use these operators to query and transform data this quote from the Microsoft docs provides a basic definition of standard query operators and can be found at this URL the standard query operators are the methods that form the link pattern most of these methods operate on sequences whereas sequence is an object whose type implements the generic I.E numerable interface or the generic I queryable interface a lot of the more frequently used query operators have dedicated c-sharp keyword syntax that enables them to be called as part of a query expression as discussed in the previous video in this video series it is advantageous to use Query syntax rather than method syntax where possible because query syntax is more readable note that query expression Clauses are translated into calls to query Methods at compile time for a useful tabular representation of standard query operators that have equivalent query expression Clauses please navigate to this URL we discussed what is meant by deferred execution and immediate execution in the previous part of this video series for a useful tabular classification of operators in terms of deferred execution or immediate execution please navigate to this URL for additional information on deferred execution versus immediate execution and Method syntax versus query syntax please View the second part of this video series so to gain an understanding of several useful link operators let's create a few code examples as with the previous video in this series I'll be demonstrating these code examples on my Mac platform using visual studio for Mac the code creation process for these code examples is almost exactly the same process as creating the equivalent code examples on a Windows platform using visual Studio for Windows the fact that I'm using visual studio for Mac should not be a barrier for a learner to follow along with the creation of these code examples start by creating a.net core console project we created a project in the previous video named link examples underscore one let's name this project link examples underscore 2. in order to save time let's copy data related classes that have already been created in the previous videos of this series and incorporate these classes into our new project these classes contain class definitions for an employee and a department record as well as code that adds several made up objects of type employee and Department to their respective collections as in the previous parts of this video series we can then use these collections of strongly typed objects in our link query examples so to copy the relevant code let's navigate to this URL I.E to a GitHub repository that was created in the previous video let's click on the directory icon labeled link examples underscore one then let's click on the file icon labeled program.cs let's copy the employee class to our clipboards and paste the contents of our clipboards within the new project that we have just created let's copy the department class to our clipboards and paste the contents of our clipboards below the employee class within our new project lastly let's copy the data class to our clipboards and paste the contents of our clipboards below the department class within our project so in the previous video we looked at the following operators the select projection operator the where filter operator the join and group join join operators in this video we'll create code examples that implement the following operators we'll start by looking at the following sorting operators order by order by descending then by and then by descending we'll then look at these grouping operators Group by and to lookup we'll then look at these quantifier operators or any and contains we'll then look at the following filtering operator of type note that another example of a filtering operator is the where operator we have looked at the where filter operator in the previous parts of this video series lastly we'll look at the following element operators element at element at or default first first or default last last or default single and single or default we'll look at even more link operators and create code examples in the next video the next video will be the final video in this video series on link right let's get started so let's start by looking at the data related classes that we copied from GitHub we have the employee class which provides a definition for an employee record we have the department class which provides a definition for a department record we have the data class that contains code where lists of hard-coded made up employee and Department objects are added to their respective collections within their respective methods and returned from their respective methods in the previous video in this series we looked at join operators namely the join operator and the group join operator so in the context of join operations the department collection has a one-to-many relationship with the employee records in the employee collection this simply means that one or many employees can be members of a specific Department a specific employee can be a member of only one department at a point in time so let's go to our main method we can use the data dot get employees method to return a collection of made up employee records let's use this method to assign the relevant collection of employee records to a local variable named employee list the employee list variable is defined as a generic list that is strongly typed as employee so here we have red squiggly lines because we need to include a using directive to the system.collections.generic namespace let's use the data dot get departments method to return a collection of made up Department Records that have a one-to-many relationship with the relevant collection of employee records and let's assign the relevant returned collection of Department Records to a variable named Department list the department list variable is defined as a generic list that is strongly typed as Department so let's get into the creation of our examples sorting operators order by so let's create an example for the order by operator in this example we want to return all employee records from the employee list collection and order by Department ID to make things a little bit more interesting let's say we want to include the department name within our outputted results the problem here is that the department name does not exist in the employee type so we need to join the employee list collection to the department list collection so that we can include the department name in our results we can do this by using the join operator so let's write this query using method syntax so that we can see all the link extension methods used in the relevant query so we first need to make sure that we include a using directive to the system.link namespace this director will give us access to the relevant link operators that we will be implementing in our code examples we know that the system.link namespace contains two static classes namely innumerable and queryable and these static classes mostly contain extension methods our link operators which are implemented in the form of extension methods are implemented within the enumerable and queryable static classes so this link query joins the employee list collection to the department list collection on the department ID property of each of the relevant employee records with the ID property of relevant Department Records foreign before we run this query let's alter the data a bit let's make the employee record pertaining to Sarah Jameson a member of the technology department by changing the relevance Department ID property from two to three let's also change the department for the employee record pertaining to Jane Stevens to the technology department by changing the relevance Department ID from 2 to 3. this change to the data will make the effects of our sorting operators more apparent when we view our results so if we run the query first without the use of any order by operators note that the data appears to us in the same order as it was added to the employee list but now let's say there is a requirement to order our results by the department ID property in ascending order we can do this by chaining the order by extension method to the end of the query like this for more information on method chaining please view the previous video in this series so we can pass in a Lambda expression to the order by extension method to indicate that we wish to order the results by the department ID property let's run the code and you can see that the results are now ordered in ascending order by Department ID if we look at the code that added each department to the department collection we can see that the human resources department has an ID of one the finance department has an ID of two and the technology department has an ID of three our results have been ordered in ascending order based on the department ID property of the employee record so we can clearly see the effects of the order by operator on our results great if the requirement changed such that we needed to order our results by the department ID property in descending order we can chain the order by descending extension method instead of the order by extension method for this purpose our results are now ordered by Department ID in the reverse order when compared to the previous output great so let's say that we want to order our results by the department ID property in ascending order and then by the annual salary property also in ascending order so let's replace the order by descending extension method with the order by extension method in our results like this then let's chain the then buy extension method to the query like this we can then pass a Lambda expression to indicate that we wish to further order our results by the annual salary property the then by extension method will order the results produced by the order by sorting operator in ascending order great and if we want to order by the annual salary property in descending order we can replace the then buy extension method with the then by descending extension method for this purpose excellent we can represent sorting operations within link queries that are represented using query syntax like this note the order by and descending keywords in the query syntax version of the relevant query we use the order by keyword within our query to order by our first specified property if we want to subsequently sort on other properties instead of chaining the then by extension method to our query and query syntax subsequent Properties by which we wish to sort are separated by commas the descending keyword can follow any of the properties that we wish to sort in descending order the default order is ascending order thank you so let's look at the group by and to lookup grouping operators let's start with the group by operator so this operator is akin to the group by Clause we would apply in a transact SQL query so we can implement the group by operator using the group by extension method or the group and the buy keywords within query syntax to perform the same operation so let's say we wish to query the employee list collection and group our results by the department ID property the query using query syntax would look like this this query is simply grouping our results by the department ID property using method syntax this query would look like this in all of the link query examples using query syntax that we have looked at up till this point in this video series we have ended all our queries with a select operator notice that this query does not end with a select operator it is important to note that a link query implemented in query syntax must either end with a select operator or a grouping operator notice how this query does not end with a select operator but a grouping operation is being performed at the end of this query so we can Traverse the results of this grouping operation using nested for each Loops the outer loop traverses each group that has been created based on the department ID property the inner loop traverses the item members within each of the groups that have been created in our query based on the department ID property great we could also include the order by operator within our query like this let's run the code great we can implement the same query using methods syntax using the to lookup extension method like this great we can also include sorting functionality through the order by operator through the implementation of method chaining like this great note that the two lookup method performs the same operation as the group by operator the only difference is the execution of the group by operation is deferred whereas the execution of the two lookup operation is executed immediately we discussed deferred execution and immediate execution in the previous part of this video series for more information on deferred execution and immediate execution please View the second part of this video series the link to this video has been included Below in the description note that the results returned from a group by operation and a two lookup operation return a group contained in a special type of collection that implements an eye grouping generic interface so let's look at the all any and contains quantifier operators let's first look at the all and any operators let's create a variable named annual salary compare and assign it with a value of twenty thousand let's Implement a block of code related to the all operator let's Implement a similar block of code related to the any operator so the all and any operators return a Boolean value the return value will equal true if the elements in the relevant collection satisfy a condition passed to the relevant method this condition can be passed in the form of a Lambda expression this query will return true if all employee records have annual salary property values that are greater than twenty thousand this query will return true if one or more employee records have an annual salary property value that is greater than twenty thousand let's run the code foreign this is an expected result let's change the annual salary compare variable to a hundred thousand great and let's change the annual salary compare variable to 40 000. so let's move on to the contains operator so we want to be able to assess whether an employee record exists within our collection of employee records the contains operator will return true if we pass in an object that is equal in value to an object within the relevant collection to understand how the contains operator Works let's create an employee record that we know matches an employee record within the employee list collection let's copy this record for the employee named Douglas Roberts that is a member of the relevant employee collection and paste it in our main method let's name the variable that stores a reference to the employee record we wish to find within the employee list collection search employee so how can we assess whether the employee record pertaining to Douglas Roberts exists within the employee list collection we can use the contains extension method we can simply pass the variable we named search employee to the contains extension method which we have invoked on our employee list collection object so the contains method returns true if the search employee object matches any of the objects within the employee list collection and false if the search employee object does not match with any of the objects stored within the employee list collection so let's write code to write an appropriate narrative to the screen based on the Boolean value returned by our query let's run the code and this is not the result we expected because we literally copied and pasted the code representing one of the employee objects that has been added to the employee list collection from our data class to the main method as our search object so why is our output indicating that the search employee object does not match any of the objects stored within our employee list collection this result is unexpected the answer is that the compiler knows how to compare built-in C sharp types like for example integer or bull the employee type is a user-defined type or custom type we need to tell the compiler how we wish to compare two objects of type employee to establish where the two employee objects are equal so we can do this by creating a class that implements the generic I equality comparer interface the eye equality compare interface is a member of the system.collections.generic namespace we have already included a using directive to this namespace within our code so let's create a class named employee comparer and let's indicate that we want the employee comparer class to implement the I equality compare a generic class that is strongly typed with the employee user-defined type so let's hover our Mouse pointers over the red squiggly line and create the stubs for the methods that we need to implement like this so two method stubs have been generated one named equals and the other named get hash code so the get hash code method is used for the purpose of uniquely identifying an object we can implement the code for the get hashed code method by returning the hash code of the ID property of the relevant employee object like this the equals method must be implemented in a way that establishes how we wish employee objects to be compared when determining whether two employee objects are of equal value so let's say that we are satisfied with comparing the relevant employee objects based on their ID first name and last name properties to establish equality between the relevant employee objects so we can achieve this with the following code if the relevant properties are equal return true else return false so we are already passing in the search employee objects to the contains method let's also pass a newly instantiated object of type employee comparer to an overload version of the contains method like this and now we are telling the compiler how we want to compare our employees search object with the objects stored within the employee list collection let's run the code and this is an expected result the contains method now returns true as expected because our search record matches the employee record for Douglas Roberts within the collection on which we are performing our search great so now let's look at the of type filter operator so before we use the of type filter operator in our code examples let's create a method named get heterogeneous data collection heterogeneous in this context of course is referring to the storage of objects of multiple different data types within the same collection so we can store objects of multiple different data types in an arraylist note that Microsoft does not recommend using arraylists for new development it is far better to use strongly typed lists to store collections of data for more detail as to why strongly typed lists are recommended please view a video on generics created by this channel a link to this video has been included Below in the description so a use case where we could apply a link query on an arraylist could be perhaps we have a legacy piece of code that includes an arraylist containing objects of multiple types and we need to filter the relevant arraylist based on specific types we can achieve this using the of type link operator we'll look at how we can do this in a bit let's first add some made up data to an arraylist so the arraylist stores integers strings objects of the employee user defined type and objects of the department user-defined type foreign let's go to our main method and write code to use the of type operator to query this arraylist based on specified data types let's query for all string values in our arraylist great so let's query all integer values in our array list foreign great let's query for all objects of the employee user defines type great let's query for all objects of the department user defines type excellent so you can see how we can query an arraylist for objects of a specified type using generics and the of type Operator by passing in the relevant type between angle brackets like this this will return those objects within the arraylist that are of the specified type let's move on to the element operators let's look at the element at element at or default first first or default last last or default single and single or default element operators so let's first look at the element at operator so the element at operator is used for querying for an item in a collection that resides at a specified ordinal location within the relevant collection so we can pass an integer value representing the relevant element's location in the relevant collection into the element add method to return the desired items value from the relevant collection like this great and our query has returned the value for the relevant element foreign but what happens if we pass in a value representing a position in the relevant collection where an element does not exist so let's try this let's pass in a value of 8. right so it is important to note that no item exists within the relevant collection at ordinal position 8. so in this case an argument out of range exception is thrown this brings us to an important point about the difference between the element at and the element at or default method if we replace the element at method with the element at or default method and we query the relevant collection for an item at a position where the desired item does not exist within the relevant collection the element at or default method does not throw an exception but rather Returns the default value for the desired items relevant data type let's briefly discuss what we mean by default value for a particular data type the c-sharp language Associates default values for c-sharp data types so if for example we are querying a collection of integers using the element at or default method for a value that did not exist at the specified location in the relevant collection the element at or default method would return the default value for the integer data type which is a value of zero the integer value of 0 is the default value for the integer data type in C sharp if however we are querying a collection containing a reference type object like an object of our user-defined type employee the default value returned by the element at or default method would be null null is the default value for reference type data types like user-defined types or the string data type for more detail regarding default values in c-sharp please navigate to this URL so let's use the element at or default method to query the relevant collection of employees for a value at position 8. I.E where no employee object is present within the relevant collection we can then write code to check if the returned value is null and you can see that an exception is not thrown and no results are outputted to the screen which is an expected result because the query returned a null value let's pass in a value of 2 thank you and then one and in both cases the appropriate result is outputted to the screen so let's write code to Output an appropriate narrative to the user based on the value returned from the element at or default method so let's pass in a value of 12. the output indicating that the return value is null I.E the employee object does not exist in the collection as output to the screen this is an expected result great so the first operator Returns the first element in a collection if no condition is specified we have the option of passing a condition to the first method or we can leave the brackets following the first method empty if we do not specify a condition the query will return the first element in the relevant collection if a condition is specified the first operator will return the first element in the collection that satisfies the specified condition we can specify a condition in the form of a Lambda expression right to prove this let's first create a strongly typed list of integer values foreign this collection using the first operator and don't provide a condition passed as an argument to the first method the query Returns the first item in the collection as expected so let's pass a condition in the form of a Lambda expression to the first extension method so this condition is simply querying the collection for the first even number in the collection let's run the code great but now what happens if we alter the collection of values so that no value satisfies the condition I.E no even numbers exist within the relevant collection an invalid operation exception is thrown so if we don't want an exception to be thrown we can replace the first operator with the first or default operator as you may have guessed where no items in the relevant collection satisfy the relevant condition this method will not throw an exception but will rather return the default value of the relevant data type we know that the default value for the integer dates type is zero let's write code to Output an appropriate narrative to the screen if the relevant condition is not satisfied let's run the code and this is an expected result so let's include an element in the relevant list that does satisfy the condition excellent so the last and last or default operators work in much the same way as the first and the first or default operators the only difference is that the last item in the collection is returned where no condition is specified the last item that satisfies a specified condition and the collection is returned when a condition is specified the last method will throw an exception if an appropriate element is not present within the relevant list we can use the last or default method rather than the last method to return the default value for the relevant data type in the case where an appropriate element in the relevant collection is not present great so let's move on to the single and single or default element operators the single operator Returns the only element in a collection or one element that satisfies a specified condition from a collection we have the option of passing a condition into the single method through the use of a method overload or we can leave the brackets following the single method empty which means we have chosen not to pass in a condition into the single method if we choose not to pass in a condition into the single method and there is more than one element or zero elements in the collection and invalid operation exception will be thrown if however there's only a single element in the collection the value for this element will be returned by the query if we do choose to pass in a condition and zero or more than one element satisfies the condition an invalid operation exception will be thrown if we pass in a condition to the single method and only a single element satisfies the condition the value for this single element will be returned from the relevant single operation let's apply the single operator to a collection of employees let's first modify the data for our list of employees so that only one employee element exists within the relevant employee collection foreign let's write code using the single operator to query the relevant employee list foreign this is an expected result let's now include the employees that we just commented out back within the relevant employee list let's run the code and an invalid operation exception is thrown as expected this is because more than one employee resides in the list if there is only one item in the relevant collection the value for this item is returned if there are zero items or more than one item in the collection and the condition is not specified for the single operator and invalid operation exception is thrown so let's test the single method and this time specify a condition where one item within the relevant collection satisfies the specified condition great let's specify a condition where more than one item in the relevant collection satisfies the specified condition as you can see an invalid operation exception is thrown so let's test the same scenario I.E more than one element in the relevant collection satisfies the specified condition but this time let's use the single or default method that's interesting so note where more than one element satisfies the specified condition when the single or default method is applied in the relevant query an invalid operation exception is thrown just like when the single method is applied to perform the same operation so we can use the single or default method instead of the single method if we don't want an exception to be thrown under the scenario where no elements exist within the collection the default value for the data type will instead be returned by the query if a condition is specified for the single or default method and no elements satisfy the condition the same result will be produced I.E the default value for the data type of the elements stored in the collection will be returned by the query if a single item is present within the relevant collection and the single or default method is applied to perform a query against the relevant collection the single elements value that exists within the relevant collection will be returned by the query [Music] hi and welcome to the fourth and final part in this video series on the link technology using c-sharp this video is also part of the seventh tutorial in an advanced c-sharp course this video is a continuation of the previous three videos in this video series on link so I recommend viewing the previous three videos in this video series before viewing this video in the previous video in this series we looked at code examples where certain link operators were implemented to query and transform data this video is a continuation of the previous video in this series where we'll create code examples using some of the other link operators to gain a broader understanding of what can be accomplished using the Link Technology in c-sharp so in the previous videos in this series we have created code examples using the following link operators the Sorting operators order by order by descending then buy and then by descending the grouping operators Group by and to lookup The quantifier Operators all any and contains the filter operators of type and where the element operators element at element at or default first first or default last last or default single and single or default the join operators join and group join we have already looked at the select projection operator and we'll also look at the select operator in this video and in addition to this we'll also look at the select many projection operator so in this video we'll look at the following link operators the equality operator sequence equal the concatenation operator concat the set operators distinct except intersect and Union the generation operators default of empty empty range and repeat operators the aggregate operators aggregate average count sum and Max the petitioning operators skip skip while take and take while the conversion operators to list two dictionary and two array and lastly the projection operators select and select many we'll also look at the following keywords that are useful when used in queries implemented in query syntax namely the let and into keywords we have looked at the select projection operator already in previous videos but we'll look at this operator again in this video as well as look at the select mini projection operator we'll then be able to gain an understanding of the difference between the select and the select many projection operators as in the previous videos I'll be demonstrating the code examples on my Mac platform if you are using a Windows platform to follow along the experience of creating these code examples will be almost exactly the same so the fact that I'm using visual studio for Mac should not be a barrier for a learner using visual Studio for Windows to follow along with the creation of these code examples okay so let's create a.net core console project foreign so the official release of dotnet 5 has now been released so I'll be running this project using the new.net5.net runtime for more information on the significance of the.net 5 runtime please View the second part of this Advanced c-sharp course I've included a link to this video Below in the description the project we created in the last video was named link examples underscore 2. let's name this project link examples underscore 3. so as we did in the previous video let's navigate to this URL where we can access code on GitHub for the project that was created in the previous video of this series here we can copy the data related code that we created in our previous project so that we can reuse these data related classes in this project let's click the directory icon labeled link examples underscore 2. let's then click the file icon labeled program.cs let's copy the class named employee and paste the contents of our clipboards to our new project let's do the same for the Department class and the data class okay so we won't be using the get heterogeneous data collection method that resides in the data class in this video so let's delete this method let's refresh our memories about the classes we have just copied and pasted from GitHub the employee class contains a definition for an employee record the department class contains a definition for a department record the data class contains hard-coded made up data for employee records and Department Records the employee records are stored in a generic list strongly typed as employee the Department Records are stored in a generic list that is strongly typed as Department we can access the data stored in the employee list collection from calling client code in this case this will be the main method by calling the static method named get employees we can access the data stored in the department list collection by calling the static method named get departments note that the data class in which these methods reside is a static class let's navigate to the main method and before we create our example code let's add the appropriate using directives first let's add a using directive to the system.collections.generic namespace the generic list type is a member of this namespace let's also add a using directive to the system.link namespace once we have added our system.link using directive we can access our link operators on collection types that implement the I enumerable interface so for example list types implement the innumerable generic interface so we are able to access extension methods stored within the innumerable static class which is a member of the system.link namespace by including a using directive to the system dot link namespace the queryable static class also includes link extension methods and is a member of the system.link namespace for more information on how extension methods work in c-sharp please view the first video in this video series a link to this video has been included Below in the description before we get started creating our code examples I want to draw your attention to what might be an issue for visual studio for Mac users when we run our code interactively through Visual Studio for Mac by default the output may appear in the terminal console window within visual studio for Mac like this this is absolutely fine for testing code but you may prefer to present the output externally I.E in a terminal console window outside of visual studio for Mac if you'd prefer to present your output externally I.E outside of visual studio for Mac please navigate to visual studio format preferences then Under The Heading marked other select the item labeled terminal in the right pane of the relevant dialog box ensure that the enable integrated terminal checkbox is unchecked this is obviously not a functional issue it really comes down to personal preference let's define a generic list variable named employee list that is strongly typed as employee and let's assign the collection of employee records returned by the data dot get employees static method to the employee list variable let's define a generic list variable named Department list that is strongly typed as department and let's assign the collection of Department Records returned by the data.get department static method to the department list variable okay so we are now ready to query and transform data using link so let's start with the equality operator sequence equal so the use case here is we want to compare two lists of data and we want to know if these two lists contain elements of equal value and each element in each of the relevant lists are stored in the same order if each item in each list matches in terms of value and order the sequence equal method will return true if any of the items in each of the relevant lists does not match in terms of value and order the sequence equal method returns false let's create a generic list named integer list one that are strongly typed as integer let's copy and paste this line of code to the next line let's change the name of the second list to integer list 2. let's run the code and the sequence equal method returns true because the values and sequence of elements in integer list 1 is identical to the values and sequence of elements in integer list 2. let's change the order of the items in integer list 2 so this sequence of values is not equal to the sequence of values stored in the list named integer list one let's run the code and this is an expected result let's change integer list to once again for good measure and this is an expected result great so let's change the data in instillus 2 so that the order and values in the lists are equal and now the result is true which is an expected result that was pretty straightforward I.E comparing two sequences of values of a primitive data type IE the integer data tab but what if we want to test this type of equality between two lists that are strongly typed with a complex type like for example our employee uses defined type so let's Define a variable named employee list compare as a generic list type that is strongly typed as employee and let's assign The Collection returned from the data dot get employee's static method to our employee list compare variable let's run a sequence equal operation and compare the values stored within our employee list compare collection with the values stored within the employee list collection so one would expect the result of the sequence equal operation to return true because both collections contain exactly the same data let's see what happens oh and false is returned the reason why false is returned is because we need to tell the compiler how to establish equality between objects of the relevant complex type IE in this case the employee type so we achieved this in the previous video in this series by implementing the built-in I equality comparer generic interface so let's copy the relevant code for this that we implemented in the previous video in the series let's navigate to the GitHub repository at this URL that contains the code we created in the previous video in this series let's copy the class named employee comparer to our clipboards and paste the contents of our clipboards to our projects like this we have red squiggly lines so let's fix this by bringing in the appropriate using directive so here we have implemented a class named employee comparer that implements the generic I equality compare interface that is strongly typed as employee this gives us a way of telling the compiler how we want objects in our lists of type employee to be compared when determining equality between the relevant objects so we are explicitly doing this here in the equals method where we are comparing the ID first name and last name properties between two employee objects we are returning true if the relevant properties of the relevant two objects are equal and false is returned if any of the relevant properties of our two objects are not equal within the get hash code method we are returning the hash code of the ID property of the relevant employee objects for more information on hash codes please navigate to this URL so now let's go to our main method and we can pass in a new instance of the employee comparer class to an overloaded version of the sequence equal method this object will tell the compiler explicitly how we want our employee objects to be compared let's run the code and now the sequence SQL operator works as expected so note that we need to tell the compiler how we want objects that are defined as complex types to be compared when determining equality between the relevant objects we can do this by creating a class that appropriately implements the I equality comparer generic interface great let's move on to the concatenation operator named concat we can use the concat operator to append one collection of data to another collection of data so let's see the concat operator in action let's create a collection of integer values let's then create another collection of integer values let's append the second collection of integer values with the first collection of integer values using the concat operator let's write code to Output the results of this concatenation operation to the screen let's run the code great let's do another example but this time let's append a collection that contains a collection of objects of a complex type to another collection of objects of the same complex type the complex type we'll use in this example will be our employee user defined type once again we can use the concat operator to achieve what we want here let's run the code great let's move on to the aggregate operators let's first look at the aggregate operator using the aggregate operator we can perform a custom operation on values within a collection let's say we have a requirement where we need to provide a total for the annual salaries of our employee records we also need to include the addition of an appropriate annual salary bonus in the final result let's say a manager gets a bonus of four percent and employees that aren't managers get a bonus of two percent so let's say we have a use case where we need a result that is a total of all employee annual salaries which must include each employee's bonus we can take this added complexity into account when totaling up our employee annual salaries through the use of the aggregate operator so the first argument is the seed value so let's provide zero for this argument using this argument we are initializing the value that will be returned by our aggregate operator the next argument is of a delegate type where we can include the logic for our aggregation functionality in the form of a Lambda expression so as you can see in our Logic for the relevant Lambda expression argument we are checking to see if the relevant employee is a manager whereby we need to add a bonus of four percent to the relevant employees annual salary if the employee is not a manager we need to add a bonus of two percent to the relevant employees annual salary we can then include the appropriate bonus amount in our calculation so we are adding up all employee annual salaries which includes each employee's appropriate bonus let's run the code great say we want to Output a comma delimited string from our employee list collection each delimited item containing the employee full name a dash then the employee annual salary each annual salary must include the appropriate bonus we also want to include a label preceding the comma delimited string we can achieve this using the aggregate operator like this the first argument is the label where an appropriate value could be something like employee annual salaries including bonus then the logic of our aggregation functionality can be expressed in a Lambda expression for the second argument like this we can apply the same logic to include appropriate bonuses from our previous example let's run the code great but we have a slight issue where a comma is presented at the end of our string we don't want this comma in our final result we can express this by passing a Lambda expression as our third argument to an overloaded version of the aggregate operator this argument allows us to perform a final operation on the results produced by the Lambda expression passed in as the second argument we first need to include another data type argument with an angle brackets following the aggregate method to declare the data type of the final output I.E provided by the Lambda expression we are passing in to the last parameter of this overloaded version of the aggregate method in the Lambda expression passed in as our final argument we are using the substring method to remove two characters from the end of the result which means a comma and a space will be removed this result is what we want excellent so let's move on to the average aggregate operator so to find the average annual salary for a list of employees we can apply this code great if we want to find the average salary for all employees in the technology department we can use method chaining and the where operator to achieve this like this great for more information on method chaining please View the second part of this video series let's move on to the count aggregate operator so if we want to know how many employee records we have in our collection of employee records we can apply the count operator like this great if we want to know how many employees or members of the technology department we can pass in a condition as an argument to the count operator the condition that is passed in as an argument to the count method can be expressed in the form of a Lambda expression great let's move on to the sum aggregate operator so let's keep this simple we just want the sum total of all annual salaries of all employee records stored in our employee list collection we can apply the sum operator to achieve this great let's move on to the max aggregate operator so if we want to return the highest annual salary property of all employee records stored in our employee list collection we can apply the max aggregate operator in a link query like this great let's move on to the generation operators namely default of empty empty range and repeat let's first look at the default fmt operator so we can use the default if empty operator to return a new I enumerable collection that when for example we apply the element at method on the new collection to check if the new collection is empty the operation will return the default value for the relevant type used to strongly type the relevant collection a query using for example the element at method against a collection returned from a default of empty operation will not throw an exception if the collection is empty the default value of the type with which the relevant collection is strongly typed will instead be returned for more information on default values for C sharp dates types please navigate to this URL we'll see later how we can pass in a value to the default of empty method to specify our own default value that we'd prefer to be returned when for example the element at method is used to check if the relevant collection is empty rather than the default c-sharp value for the relevant data type let's look at an example using the default fmt method so let's create an empty list of integer values let's perform a default fmt operation on this collection of integer values and assign The Returned collection to a new collection of integer values let's use the element at operator and pass in 0 to the element at method to check if the collection is empty you can see that the element at operator Returns the default value for the integer data type which is zero this is an expected result let's create another example but this time let's use the employee data type to strongly type our collections as discussed the default value for a reference type in c-sharp is null but as discussed we can pass in a default value that we would prefer to be returned by for example the element at operator when the relevant collection is empty so let's person an employee object where the ID property of the relevant employee object is set to zero null is not returned as would be the case if we hadn't passed in a value to the default fmt method because we have specified our own default value where the ID property of the default employee object is zero great similarly we could have set the default value for our integer collection example by for example passing in 999999 to the default if empty method instead of returning the default c-sharp value for an integer which is zero nine nine nine nine nine nine is returned this is an expected result excellent right let's look at the empty method so we can use the empty method to generate a new empty collection so for example we want to create a new innumerable generic collection of employee records we can do this by using the empty operator like this note the empty method is not an extension method of a numerable or I queryable like other link methods it is a static method included in the enumerable static class so for example we could instantiate a new empty generic list strongly typed with the employee user defined type through the use of the empty method we can then chain the two list conversion operator to this operation and return an empty generic list we are using the two list operator to convert an i enumerable generic collection that is strongly typed with the employee dates type to a generic list that is strongly typed as employee so for good measure let's add a new employee item to our new list that we have generated through the use of the empty method and the use of the two list conversion operator let's write code to write the items or in this case one item stored in our new list to the screen great let's move on to the range generation operator we can use the range operator to return a collection of values that are within a specified range so let's clarify this with an example we want to return a range of integer values in a collection where the first item in the range has a value of 25 and each subsequent item is incremented by a value of 1 until there are a total of 20 values in the relevant collection we can achieve this by using the range method like this in this example the first argument specified is 25. this is the value of the first element and the second argument specifies the number of elements to include in the relevant generated collection when each item is added to the collection It Is incremented by a value of one so the second item will be 26 the third item will be 27 the fourth item will be 28 and so on let's run the code great so using the range operator we have generated a collection of integer values that fall within a specified range excellent let's say we want to generate a collection of a specified amount of elements where a value for each element in the collection is repeated we can use the repeat operator for this purpose like this great let's move on to the set operators namely distinct except intersect and Union let's start with distinct so let's say we have a strongly typed generic list that contains a list of integer values some of which are repeating values we want to perform a query against this list of integer values where we only want distinct values returned we can achieve this by applying the distinct operator to our query like this great let's say that we have two collection of a specific type and we want to return elements with values in our first collection that are not equal to any of the values of the elements in our second collection we can achieve this by using the accept operator let's look at an example of using the accept operator applied to two collections of integer values so one and two are returned in our results collection because these values exist in our first collection and don't exist in the second collection the values 3 and 4 exist in our first collection and also exist in our second collection the values five and six exist in our second collection and even though these items don't exist in our first collection the accept operator does not return these items as part of the results collection because in this example the accept operator returns values that exist in the first collection and don't exist in the second collection let's try an example using complex types let's use our employee type let's create a generic list strongly typed as employee let's copy data that we know will exist within our employee list collection and add these items to the list we have named employee list 2. let's add some additional items to the employee list to collection let's then call the accept method on the employee list collection and pass in the employee list 2 collection as an argument to the accept method then let's assign the results of this operation to an implicitly typed variable named results we have of course used the VAR keyword to implicitly type our results variable let's write code to Traverse the results and output the results to the screen let's run the code and see what happens and this result is not expected the output contains all items within the employee list collection I would have expected that the items that were copied from the data class and added to the employee list 2 collection as well as the other items that we added to the employee list collection would be excluded from the results the reason for the unexpected results is because we have not told the compiler how to compare employee objects when determining if one employee object is equal to another employee object so how can we let the compiler know how we want one employee to be compared to another employee within our collections when determining equality between the two relevant objects you may have guessed already how we can achieve this we first need to create a class that implements the IE quality comparer generic interface we can then write code to establish how one employee object should be compared to another employee object when determining equality between the relevant objects we have already included the code for this which we applied when we created code to perform a sequence equal operation on two lists that were strongly typed as an employee so we have our class named employee comparer that implements the IE quality comparer generic interface so we can reuse this class for our example that performs an accept operation on two generic lists that are strongly typed as employee so all we need to do is create a new object of type employee comparer and pass this object into an overloaded version of the accept method like this let's run the code great this is now an expected result let's move on to the intersect operator so we can use the intersect set operator to return a collection containing only those items that exist in the employee list collection that are equal to the items that exist within the employee list 2 collection and now that we know we are comparing elements in two collections that are strongly typed with the employee complex type we need to pass in a new object of the employee comparer type I.E a type that appropriately implements the generic IE quality comparer interface this tells the compiler how to compare two objects of type employee when determining if the relevant employee objects are equal let's run the code and this is an expected result great let's move on to the union operator the union extension method requires two Collections and returns a new collection that includes distinct elements from both the collections we can again use the same data we have used in the previous two examples to test the union operator right let's write the code for this you will notice we are also passing in an object of type employee comparer to the union method Let's test the code great so let's see what happens in this example when we don't pass in an object of type employee comparer to the union method you can see that duplicate records are included in the results as a consequence of not passing in an object derived from a class that appropriately implements the generic I equality comparer interface and of course we know we have already created such a class so let's ensure that we pass in our object of type employee comparer to the union method so that only distinct employee objects are included within our results excellent so let's move on to the partitioning operators these partitioning operators include the skip skip while take and take while operators let's first look at the skip operator use the skip operator to skip over a given number of elements in a sequence and then return the remainder so let's query our employee list collection using the skip operator let's skip over two of the elements stored in our employee list collection and return the remainder of the elements using the skip operator great this is an expected result let's look at the skip while operator using the skip while operator bypasses elements in a sequence as long as a specified condition is true and then Returns the remaining elements let's see this operator in action so the condition here is Skip elements in our sequence of employee objects in the employee list collection while the relevant employee object's annual salaries are above 50 000. great this is an expected result just to make things a little bit more clear on how this operator Works let's add a new employee record to the employee list collection this employee record has an annual salary of 100 000 which of course is above fifty thousand let's run the code and you can see that while the condition is true the items in the relevant collection are skipped I.E will not be returned from the query however all subsequent elements in the sequence will be returned once the condition returns false even though the last element in the relevant sequence has an annual salary property greater than fifty thousand it is still returned because the third element in the sequence caused the condition to return false the fact that the last element has an annual salary above 50 000 and therefore causes the condition to be true doesn't mean that the last item will be returned by our query note that once the condition returns false all items from that point will be returned from our query right let's move on to the take partition operator the take operator is used to return a specified number of contiguous elements from the start of a sequence let's see this operator in action this is an expected result great the first two elements in the relevant sequence of elements in our employee list collection are returned by our query so let's move on to the take while partitioning operator the take while operator returns elements from a sequence as long as a specified condition is true and then skips the remaining elements so let's use the same condition that we used in the skip while operator example so here we are using the take while operator to return elements from the relevant sequence that have an annual salary above 50 000. so the first two elements in the sequence are returned by our query as soon as the third element is evaluated and the condition returns false the elements from that point onwards in the relevant sequence will not be returned by our query so for example the fact that the fifth element in the employee list collection has an annual salary property that is greater than 50 000 is irrelevant because when the third element causes the condition to return false and in terms of the take while operator this means that from that point onwards the rest of the elements and the relevant sequence will not be returned by our query great let's look at the conversion operators to list two dictionary and two array so let's look at the two list conversion operator a query written in query syntax will return an i enumerable collection containing the results of the query what if we need our results to be returned as a generic list we can achieve this using the two list conversion operator let's create an example to clarify this point so for this example let's query our employee list collection for employee records that have an annual salary property greater than fifty thousand using query syntax note the red squiggly lines here this is because we are attempting to assign an inumerable collection to a variable of type generic list the compiler is telling us that we have created a type mismatch so we can use the two list operator to convert the I enumerable collection returned by our query to a generic list by wrapping the relevant query in Brackets adding a DOT after the last bracket and applying the two list operator like this so now you can see the red squiggly lines disappear because we have removed the type mismatch let's run the code excellent as discussed earlier in this video series wherever a two conversion operator is applied to a query as we have done here this causes the query to execute immediately I.E query execution is not deferred it is executed immediately so if we want to convert an innumerable collection returned from a query to a dictionary object we can apply the two dictionary operator to the query let's create an example to clarify what is meant by this so as in the previous example here we have a standard query written in query syntax to query our employee list collection for all elements where the relevant elements have annual salary properties that have values greater than fifty thousand so this is a basic query that will return an i enumerable collection but what if we want to return a collection where each element has a unique key associated with the relevant element we can do this by converting the query result to a dictionary generic collection type so let's alter our code so that the innumerable collection returned by our query is converted into a generic dictionary collection So within angle brackets after two dictionary we need to pass in two data types the first data type declares the data type of the values stored in the dictionary the second data type declares the data type of the key that will be used to uniquely identify each element in the collection so the First Dates type is of type employee and the second data type is of type int note that we want each element to be keyed on the employee ID property we can express this by passing in an appropriate Lambda expression to the two dictionary method like this so let's Traverse the key values stored in our dictionary object using a for each Loop note how we are able to use the key values to retrieve the values of each element stored in the dictionary we are doing this by passing in the relevant key within square brackets to the dictionary object here we are outputting the key as well as each employee's first name and last name to the screen and as you can see we have each employee's ID which we have designated as our key value followed by the corresponding employees first name and last name properties excellent we have successfully converted a query result from an innumerable collection to a dictionary object as discussed when we call any of the two conversion operators on a query result this causes our query to execute immediately for more information on deferred execution versus immediate execution please View the second part of this video series for good measure let's also create a result using the two array conversion method so if we try to assign the results of this query to a variable defined as an array of employees the compiler complains of a type mismatch the query result is returned as an innumerable collection and not an array of employee objects to remove the type mismatch we can apply the two array method like this great so let's discuss the let and the into Clauses that can be applied when using query syntax to represent a link query let's look at the Microsoft docs for an overview of the let Clause this overview of the let Clause can be found at this URL in a query expression it is sometimes useful to store the result of a sub-expression in order to use it in subsequent clauses you can do this with the let keyword which creates a new Range variable and initializes it with the result of the expression you supply once initialized with a value the range variable cannot be used to store another value however if the range variable holds a queryable type it can't be queried so let's look at a basic example using the let keyword in a link query let's say we wish to query our employees based on employee initials now we don't have an initials property for an employee record but within our query using the let keyword we can extract the initials from each employee records first name and last name properties and assign the result of this extraction process to a variable named initials directly within our query using the let keyword this allows us to use the initials variable in our where Clause as well as reuse the initials variable within our select clause through the use of the let Clause we can also apply a condition and based on the evaluation of that condition store a value in a variable that can be used later in the query like for example within aware clause here we have a condition where we need to add an appropriate bonus to the annual salary property based on whether the employee is a manager or not a manager if the employee is a manager we need to add a bonus of four percent to the relevant employee records annual salary property if the employee is not a manager we need to add a bonus of two percent to the relevant employee records annual salary property we can store the relevant annual salary plus bonus value for each employee record by assigning the value returned by a ternary operator to a variable we have named this variable annual salary plus bonus the let clause in this example enables us to reuse the annual salary plus bonus variable both within the where clause and the select Clause later in the query thank you foreign let's run the code excellent let's look at the into keyword we can use the into keyword in a link query to collate data within a group and then after this operation we are able to perform a filter operation within the same query if required on the grouped data through the use of aware clause so for example let's use a link query to First create a group of high earning employees based on a condition let's say that the employee must earn above 50 000 to fit into the relevant group we can use the where Clause at this point in the query to appropriately filter the relevant employee records and the into keyword to put the filtered data into the relevant group of high earning employees so we can use the into keyword to group The High earning employees into a group named High earners let's say we then want to further filter this group of high earning employees by management status so we only want these employee records where the is manager property is set to true return from our query we want to know which of those High earning employees are managers so we can use another where Clause within our query to filter the high earner group based on the relevant condition we have used a where Clause earlier in our query to create the group of high earning employees then once the group is created through the use of the into keyword we can further filter the group of high earning employees based on each employee records is manager property so we are able to include another where clause in our query to filter the grouped data let's run the code great so lastly let's look at the select and the select many projection operators in this video series we have already looked at the select projection operator so the focus in this example is on the select many operator we want to know how it differs from the select operator and in what circumstances we may wish to apply the select many operator in a link query let's navigate to a Microsoft docs webpage to see what the Microsoft docs say about these two projection operators projection refers to the operation of transforming an object into a new form that often consists only of those properties that will be subsequently used by using projection we can construct a new type that is built from each object you can project a property and perform a mathematical function on it you can also project the original object without changing it select projects values that are based on a transform function select many projects sequences or values that are based on a transform function and then flattens them into one sequence to understand the difference between the select many operator and the select operator let's modify our department class so that it includes a property that stores a collection of employees we have already discussed that Department Records have a one-to-many relationship with employee records so each department object will now store a collection of related employee records let's also alter the get Department's static method that resides in the data static class so that the get Department's static method accepts an argument containing a collection of employee objects then for each department object added to the department list collection within the get Department static method let's include a query to return an appropriate collection of employees I.E those employee records that have a department ID property matching the ID property of the relevant Department object so let's go to the main method and pass in the collection of employees to the get Department method so now the get departments method will return a collection where each department contains a related collection of employee records so now what if we want to query the department list collection for all employees we can achieve this using the select operator but when traversing the results of the select operation we will need to use an outer and an inner for each Loop like this in this scenario the select many operator is better to use for this purpose the select many operator is better in this case because we can use one for each Loop to Traverse the results of our query unlike where we needed to use an additional nested for each Loop to Traverse the results of our query where we used the select operator behind the scenes as it were the select many operator appropriately flattens the results and this means we don't need to use a nested for each Loop to Traverse the results of our query we are able to use one for each Loop for this purpose excellent so we have covered a lot of ground in this video series on link operators I would encourage you to practice using these operators to become proficient in writing link queries this is a very powerful technology and it's used with Entity framework to perform database queries in modern.net applications Entity framework is now the recommended technology to use a.net for implementing database related functionality in.net applications if we navigate to this Microsoft docs webpage we can see this paragraph that states Entity framework core is a modern object-based mapper for.net it supports link queries change tracking updates and schema migrations EF core works with many databases including SQL database on-premises and Azure sqlite MySQL postgres and Azure Cosmos DB this makes link a very important technology to learn for net Developers link enables developers to write one set of code that can be applied to handle data related functionality for multiple different data sources in link expression trees are used to represent structured queries that Target sources of data that implement the generic I queryable interface if you would like to delve deeper into expression trees please navigate to this URL so let's summarize what we have learned in our four part series on using the Link Technology in C sharp in the first part of this video series we created our own extension method to filter data we also demonstrated how a Lambda expression can be used to express a condition that can be passed into our extension method to filter the data and return the filtered results from a query the reason we created the filter extension method is because link is mostly made up of extension methods stored in two static classes namely a static class named enumerable and a static class named queryable we are able to access the extension methods Within These static classes by including a using directive to the system.link namespace the innumerable and queryable classes are both members of the system.link namespace in the second part of this series we demonstrated examples using the select where join and group join operators we also discussed the difference between when a query is executed with deferred execution or immediate execution we discussed how a link query is executed with deferred execution but we can make the link query execute immediately by applying a two conversion operator to the query for example two list two dictionary or two array in the third video in this series we demonstrated code examples using the following link operators sorting operators order by order by descending then buy and then by descending grouping operators group buy and to lookup quantifier operators or any and contains filter operators of type and where element operators element at element at or default first first or default last last or default single and single or default in the fourth and final video in this series we demonstrated code examples using the following link operators the equality operator sequence equal the concatenation operator concat the set operators distinct accept intersect and Union the generation operators default if empty empty range and repeat operators the aggregate operators aggregate average count sum and Max the partitioning operators skip skip while take and take while the conversion operators to list two dictionary and two array projection operators select and select many in this video we also looked at code examples using the let and the N2 keywords I hope you've enjoyed this video series on using the Link Technology in c-sharp I hope you enjoyed this part of the advanced c-sharp course where we looked in a fair amount of detail at a set of Technologies named link please find the full Advanced c-sharp course on the gavinlan YouTube channel you'll see the video series on the home page the playlist for the entire Advanced c-sharp course can be found at this location I hope to see you soon thank you and take care

Channel: freeCodeCamp.org

Views: 92,829

Keywords:

Id: 5l2qA3Pc83M

Length: 187min 24sec (11244 seconds)

Published: Thu Apr 06 2023