Module 14 Using LINQ to Query Data

Module 14 Using LINQ to Query Data

Module Overview • Using the LINQ Extension Methods and Query Operators • Building Dynamic LINQ Queries and Expressions

Lesson 1: Using the LINQ Extension Methods and Query Operators • What Is the Purpose of LINQ? • Querying Data and Building a Result Set • Filtering Data • Ordering Data • Grouping Data and Performing Aggregate Calculations • Joining Data from Different Data Sets • Using C# LINQ Query Operators • Deferred and Early Evaluation of Queries

What Is the Purpose of LINQ? LINQ architecture C# Other. NET Framework language LINQ-enabled data source LINQ-enabled ADO. NET LINQ to Objects LINQ to Data. Sets LINQ to SQL LINQ to Entities LINQ to XML

Querying Data and Building a Result Set Ability to select rows of data is a fundamental requirement IEnumerable customers = new[] { new Customer{ First. Name = “…", Last. Name=“…", Age = 41}, . . . }; List customer. Last. Names = new List(); foreach (Customer customer in customers) { Traditional approach customer. Last. Names. Add(customer. Last. Name); that uses the } foreach statement . . . IEnumerable customer. Last. Names = customers. Select(cust => cust. Last. Name); LINQ approach that uses the Select extension method The Select extension method is available on any class that implements the generic IQueryable or IEnumerable interfaces

Filtering Data Use the Where extension method to restrict the items returned var customer. Last. Names = customers. Where(cust => cust. Age > 25). Select(cust => cust. Last. Name); Get all customer last names for customers over 25 Use the Where extension method to filter data, and use the Select extension method to project data Important: Use the methods in the correct order, otherwise you may get unexpected query results or compilation errors

Ordering Data Order data with the Order. By, Order. By. Descending, Then. By, and Then. By. Descending extension methods Use the Order. By and Order. By. Descending extension methods to apply a simple sort var sorted. Customers = customers. Order. By(cust => cust. First. Name); Order customers by their first name Use the Then. By and Then. By. Descending extension methods to sort by multiple keys var sorted. Customers = customers. Order. By(cust => cust. First. Name). Order customers by Then. By(cust => cust. Age); their first name and their age

Grouping Data and Performing Aggregate Calculations Calculate an aggregated result across an enumerable collection Console. Write. Line( "Count: {0}tt. Average age: {1}tt. Lowest: {2}tt. Highest: {3}", customers. Count(), customers. Average(cust => cust. Age), customers. Min(cust => cust. Age), customers. Max(cust => cust. Age)); Group data by using the Group. By extension method var customers. Grouped. By. Age. Range = customers. Group. By(. . . ); Eliminate duplicate values Console. Write. Line("{0}", customers. Select(cust => cust. Age). Distinct(). Count());

Joining Data from Different Data Sets LINQ provides the Join extension method to join the data held in different sources together to perform composite queries Customer Company First. Name Company. Name Last. Name Country Age Company. Name var customers. And. Companies = customers. Join( companies, custs => custs. Company. Name, comps => comps. Company. Name, (custs, comps) => new { custs. First. Name, custs. Last. Name, comps. Country});

Using C# LINQ Query Operators The LINQ query operators provide simple shorthand syntax reminiscent of SQL clauses IEnumerable customer. Last. Names = customers. Select(cust => cust. Last. Name); Extension method approach IEnumerable customer. Last. Names = from cust in customers select cust. Last. Name; Query operator approach There are equivalent query operators for each LINQ extension method var customers. Over 25 = from cust in customers where cust. Age > 25 select cust; . . . var sorted. Customers = from cust in customers orderby cust. First. Name select cust; . . . var customers. Grouped. By. Age = from cust in customers group cust by cust. Age;

Deferred and Early Evaluation of Queries Your applications do not build the collection at the time that the LINQ query is executed This strategy is referred to as deferred evaluation. var us. Companies = from a in companies where String. Equals(a. Country, "United States") select a. Company. Name; Data not retrieved yet foreach (string name in us. Companies) { Console. Write. Line(name); } Data retrieved now You can force early evaluation of queries by using extension methods such as To. List and To. Array Early evaluation creates a static copy of the data

Lesson 2: Building Dynamic LINQ Queries and Expressions • What Is a Dynamic LINQ Query? • What Is an Expression Tree? • The Expression Types • Obtaining Type Information at Run Time • Compiling and Running a Dynamic LINQ Query

What Is a Dynamic LINQ Query? Static queries are very powerful, but require you to know the query at compile time Static queries: - Always force you to write all possible queries - Sometimes require lots of unnecessary code that you may never execute - Sometimes hide bugs by using complicated conditional statements Dynamic queries are even more powerful than static queries, because they enable you to build a query at run time Dynamic queries enable you to: - Build a single query based on supplied parameters - Produce a query according to the user’s requirements - Write only necessary code - Reduce the amount of potential bugs

What Is an Expression Tree? Expression trees typically contain several expressions, which when evaluated form a dynamic query Expression trees provide a flexible syntax for developing lambda expressions When building an expression tree, each expression must be broken into individual items x > 3 && y > 6 && / ----- ----- / > / / > / Member: o. x Constant: 3 / / Member: o. y Constant: 6

The Expression Types The Expression class Provides static methods to create other expression objects The Binary. Expression class Represents an expression with a binary operator The Constant. Expression class Represents a constant reference in an expression The Member. Expression class Represents a reference to members, such as a property in a type The Unary. Expression class Represents unary operations in expressions The Expression class Represents a lambda expression

Obtaining Type Information at Run Time When you develop an expression tree, you cannot use properties of C# objects directly You must use reflection to access object types and members - Use the Type class to represent the type of an object - Use the Member. Info class to represent a member Type string. Type = typeof(string); Represents the string type Member. Info string. Length = string. Type. Get. Property("Length"); Represents the Length member

Compiling and Running a Dynamic LINQ Query You can use the Compile instance method that the Expression class exposes to compile the lambda expression tree into an executable lambda expression Expression> expression. Tree = Expression. Lambda>(expression, parameter); Func my. Delegate = null; my. Delegate += expression. Tree. Compile(); You can use the Compile method as the argument in a LINQ extension method var bank. Accounts = accounts. Where(expression. Tree. Compile()); You can use the Dynamic. Invoke method of the TDelegate object that the Compile method returns to invoke the lambda expression bool response = expression. Tree. Compile(). Dynamic. Invoke(1);

Lab: Using LINQ to Query Data • Exercise 1: Using the LINQ Query Operators • Exercise 2: Building Dynamic LINQ Queries Logon information Virtual machine 10266 A-GEN-DEV User name Student Password Pa$$w 0 rd Estimated time: 60 minutes

Lab Scenario

Lab Review Questions • In Exercise 1 of the lab, did the application perform deferred or early evaluation of the LINQ query? • In Exercise 2, which static method did you use to construct an expression tree that represented a complete lambda expression?

Module Review and Takeaways • Review Questions • Best Practices