Introducing LINQ by LearnNowOnline

Introducing LINQ

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Objectives • Motivate the need for LINQ • Learn about the various LINQ providers • Investigate simple LINQ to Objects, LINQ to SQL, and LINQ to XML samples

Agenda • Language Integrated Query • Structure of a LINQ Query • Some LINQ Examples

Language Integrated Query • Querying and manipulating data has always been a fundamental part of our jobs as developers • Data formats change, but core needs are the same  Must create, retrieve, update, and delete data

Data Access

(DBASE, 1985 or so) USE empl REPLACE ALL salary WITH (salary * 1.1) FOR supervises > 0 LIST ALL fname, lname, salary FOR Supervises > 0

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Data Access APIs (C#) (late 1990s/early 2000s)

SqlCommand cmd = new SqlCommand( @"SELECT fname, lname, salary FROM Empl WHERE supervises > @p0" ); cmd.Parameters.AddWithValue("@po", 0); SqlConnection c = new SqlConnection(…); c.Open(); DataReader people = c.Execute(cmd); while (people.Read()) { string fname = (string) people["fname"]; string lname = (string) people["lname"]; double salary = (double) people["salary"]; } people.Close(); Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Object/Relational Mapping (C#) (last few years)

public class Employee { public string FirstName; public string LastName; public double Salary; } IList employees = session.CreateCriteria(typeof(Employee)) .Add(Expression.Gt("supervises", 0) .List(); foreach(Employee employee in employees) { string fname = employee.FirstName; string lname = employee.LastName; double salary = employee.Salary; } Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Data Access APIs (VB) (late 1990s/early 2000s)

Dim cmd As New SqlCommand( _ "SELECT fname, lname, salary" & _ " FROM Empl" & _ " WHERE supervises > @p0") cmd.Parameters.AddWithValue("@p0", 0) Dim cnn as New SqlConnection(…) cnn.Open() Dim people As DataReader = cnn.Execute(cmd) While people.Read() Dim fname As String = people("fname").ToString() Dim lname As String = people("lname").ToString() Dim salary As Double = CDbl(people("salary")) End While people.Close(); Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Object/Relational Mapping (VB) (last few years)

Public Class Employee Public FirstName As String Public LastName As String Public Salary As Double End Class Dim employees As IList = _ session.CreateCriteria(GetType(Employee). _ Add(Expression.Gt("supervises", 0).List() For Each employee As Employee In Employees Dim fname As String = people("fname").ToString() Dim lname As String = people("lname").ToString() Dim salary As Double = CDbl(people("salary")) Next employee Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company

Object/Relational Mapping • Provides:  Mapping between relational data to/from objects  Cleaner integration of business rules and validation

LINQ to the Rescue • How do you retrieve non-relational data?  XML, RSS, Web Services, REST, AD, Files, and so on

• How do you interact with plain old objects?  How do you interact and query custom domain models?

• How do you enable clean code in both a strongly typed and dynamic language world?

LINQ • Query, Set, and Transform Operations for .NET • Makes querying data a core programming concept • Works with all types and shapes of data  Relational databases  XML  Plain old objects

LINQ • Works with all .NET languages  VB and C# have integrated language support

• Works in any kind of project including Windows and Web projects • Also can find third party LINQ providers  Amazon.com for example

LINQ Providers • C# uses LINQ providers to map your LINQ queries to the data source that you’re querying • The LINQ provider takes the query that you create in code, and converts it into commands that the data source will be able to execute • On return from executing the commands, the provider also converts the data into objects that create your query results

LINQ Overview VB

Others…

C# .NET Language-Integrated Query LINQ enabled data sources

LINQ enabled ADO.NET LINQ To Objects

LINQ To Datasets

LINQ To SQL

LINQ To Entities

LINQ To XML

Objects

Learn More @ http://www.learnnowonline.com

XM L

LINQ Providers • LINQ to Objects  Allows you to query in-memory of sets of objects o o

Collections, arrays, and lists If a class implements IEnumerable (or generic version), you can use LINQ to query

LINQ Providers • LINQ to SQL  Allows you to query and modify data in SQL Server  You provide mapping between a modeling class and schema in data source  Use System.Data.Linq.DataContext class to provide the “plumbing”  Can use Visual Studio’s O/R designer to make it easy o

For now, will mark up entity class manually

 DataContext manages communication o

Stores state for optimistic concurrency checks

LINQ Providers • LINQ to XML  Easy to query, modify, and generate XML content  Can work with XML from file, or from stream  System.Linq.Xml.XObject, Xnode, XElement, XAttribute o

Make it easy to work with XML programmatically

 LINQ to XML provider works with these classes to create queryable collections of XML data  Visual Basic adds language-specific rich support for LINQ to XML

LINQ Providers • LINQ to DataSet  Allows you to query and update data in ADO.NET dataset  Can use querying engine against data already cached in client application  Strongly typed datasets easier to work with than standard datasets o

You already know the schema—it's just LINQ to Objects

 Language features make it possible to query standard datasets

LINQ Providers • LINQ to Entities  Allows developers to query data exposed using Entity Data Model (EDM) o

EDM: conceptual model, allows applications to interact with data as if it was a collection of objects, or entities

 Using EDM, ADO.NET exposes entities as objects in .NET Framework, allows LINQ queries to operate  Conceptually, same as LINQ to SQL, but works with any data source o

Doesn't assume one-to-one mapping between classes and schema

Agenda • Language Integrated Query • Structure of a LINQ Query • Some LINQ Examples

Structure of a LINQ Query • LINQ query contains clauses that specify data sources and iteration variables • Query expressions can also include:     

Filtering Sorting Grouping Joining Calculating

The Three Stages of a LINQ Query • Each LINQ query requires three stages:  Obtain the data source(s)  Create the query  Execute the query

The Data Source • In the sample, data source is an array of integers • Because array implements IEnumerable, can query against it  Treated as a "queryable" type

XML as Data Source (VB) • Could use LINQ to XML to load contents of an XML file into a queryable collection of XElement instances: ' VB Dim items As XElement = XElement.Load("C:\Grocery.xml")

XML as Data Source (C#) • Could use LINQ to XML to load contents of an XML file into a queryable collection of XElement instances: // C#: XElement items = XElement.Load(@"C:\Grocery.xml");

SQL as Data Source (VB) • Can use LINQ to SQL once you have a class that provides object-relational mapping: ' VB Dim dc As New DataContext( _ My.Settings.NorthwindConnectionString) Dim customers As Table(Of Customer) = _ dc.GetTable(Of Customer)

SQL as Data Source (C#) • Can use LINQ to SQL once you have a class that provides object-relational mapping: // C# DataContext dc = new DataContext( Properties.Settings.Default.NorthwindConnectionString); Table<Customer> customers = dc.GetTable<Customer>();

The Query • Given the data source, can specify the query  Defines the information you want to retrieve  Optionally, can specify how to sort, filter, and group

• LINQ queries are generally the same, no matter the data source

The Query Execution • Query definition doesn't execute the query • Instead, defines the query that will run when it's time to retrieve the data • To execute, need to run code that forces query reference to require the values  In this course, For Each loop generally forces

Returning Multiple Values • Want to return multiple values? • In general, two options:  Create class that exposes properties o

Specify new instance of class in Select

 Specify values in Select clause o

Compiler creates a new anonymous type containing just the properties you specify

Creating a Class for Return Values • See MyFileInfo class and MultipleReturnValues1

Using an Anonymous Type • In previous example, code created a simple type (MyFileInfo)  Type contained properties returned by query  Why create a class simply to return values?

• Anonymous types generally simpler • Compiler creates the type, based on values specified in query Select clause  You don't know the type's name  Implicit type definition makes it possible to work with the anonymous type Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Deferred Execution • Defining query doesn't cause query to execute • Must actually force execution by requesting data • In other words:  Creating query doesn't return data  Provides potential for returning data

• Can take advantage of deferred execution  Break query into small steps without fear of multiple trips to the server for data  Makes debugging simpler, as well Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Converting Results to Force Execution • Sometimes, might want to immediately execute query and cache its results • Can use method like ToList or ToArray • Each executes query, and returns results

Agenda • Language Integrated Query • Structure of a LINQ Query • Some LINQ Examples

Some LINQ Examples • Get a taste of the kinds of things you can do with LINQ • Introduce some of the new language features for LINQ • Examples of:  LINQ to Objects  LINQ to SQL  LINQ to XML

LINQ to Objects • Can use any collection that implements IEnumerable (or generic version) as data source • Note example uses multiple overloaded versions of DisplayResults procedure

Query Syntax/Extension Methods (C#) • How does language compiler turn keywords like select, where, and orderby into executable code?  Keywords need to be converted into methods

• Each LINQ keyword corresponds to a single method of the System.Linq.Enumerable

Query Syntax/Extension Methods (VB) • How does language compiler turn keywords like Select, Where, and Order By into executable code?  Keywords need to be converted into methods

• Each LINQ keyword corresponds to a single method of the System.Linq.Enumerable

Continuing the Investigation • So compiler converts keywords into methods • But IEnumerable is an old interface  It doesn't support Where, Select, and so on  Where do those methods come from?

• LINQ requires ability to extend existing interfaces/classes without changing their definitions  Available because of new extension methods

Extension Methods (VB) • Add new methods to existing classes • Not just for LINQ, but crucial for LINQ • Extension methods must:  Accept a parameter, the type of which defines the class that you’re extending  Exist within a module  Include the <Extension()> attribute

Extension Methods (C#) • Add new methods to existing classes • Not just for LINQ, but crucial for LINQ • Extension methods must:  Accept a parameter, the type of which defines the class that you’re extending  Exist within a static class  Be a static method  Include the this keyword in the parameter

Using LINQ to Group Output • System.Linq.Enumerable class provides large number of methods • Look for grouping methods

LINQ to SQL • Allows standard LINQ queries to work with data stored in relational databases  Extends ADO.NET  Adds support for mapping tables and rows to classes and properties

• To support LINQ to SQL, must add custom .NET attributes to class that represents the schema • Map a class to a table  Map properties within the class to columns  All by adding attributes

Linking a Class to SQL Server • Retrieve data from Northwind Customers table • Retrieve CustomerID, CompanyName, ContactName, Country, Region • Indicate primary key, columns, and rename column

Creating the Class • Start by creating a class with properties that correspond to columns in the database • See Customer1 class

Marking Up the Class • Use attributes from the System.Data.Linq.Mapping namespace  Table  Column  Many others

• See Customer class

Retrieving a Collection of Customers • Need some mechanism to manage connection between class and data  Can't simply fill some huge data structure with all the data  Need some class that converts requests for data into SQL strings, and executes/virtualizes

• System.Data.Linq.DataContext class  Includes members that provide access to data  Tracks changes to data for updates  O/R designer creates classes, and new class that inherits from DataContext Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Determining the Actual SQL String • Set Log property of DataContext to stream  Sends all information to the output stream

• To simply see the SQL string  Call ToString method of query variable

Working with Anonymous Types • Anonymous types work well with LINQ to SQL • Often need to retrieve just a subset of columns  Or perform a calculation

• See DemoLinqToSqlWithAnonymousTypes

LINQ to XML • Need to retrieve, create, query XML? • .NET Framework provides rich support  Steps can be complex  Can require XSLT and XPath

• What if you could accomplish these using a standard syntax? • That is, what about LINQ? • LINQ to XML makes it possible • Examples create XML content  Examine Course class, and CreateCourseList Learn More @ http://www.learnnowonline.com Copyright © by Application Developers Training Company

Life without LINQ to XML • Without LINQ to XML, creating XML content required multiple steps  XmlDocument creates new elements  Set properties  Append as child of new parent

• See DemoNoLinqToXml

Using System.Xml.Linq Classes • System.Xml.Linq namespace adds classes  XElement, XAttribute, XComment  All inherit from XObject

• Each provides overloaded constructors  You pass as much information as necessary to generate the XML  More readable than XmlDocument code

• Examine CreateXmlContent

Creating Data: From and Select • From clause sets up range variable iterating over data source • Select clause specifies "shape" of resulting data • Take advantage of these facts to use LINQ to generate XML content • Examine DemoLinqToXml

Visual Basic Only (VB) • Only Visual Basic adds new features that make it far easier to work with LINQ to XML  XML literals  XML replacement tokens

• See CreateXmlContentVBOnly

Learn More! â&#x20AC;˘ This is an excerpt from a larger course. Visit www.learnnowonline.com for the full details!

Learn More @ http://www.learnnowonline.com Copyright ÂŠ by Application Developers Training Company