LINQ by MindTree Campus

Current Scenario for data access strategies : Almost every business application exposes business functionality that requires data and manipulate the same. Today, data managed by a program can belong to different wide variety of data sources like an array, an object graph, an XML document, a database, a text file, a registry key, an e-mail message, Simple Object Access Protocol (SOAP) message content, a Microsoft Office Excel file etc. Each data domain has its own specific access model like: •To navigate XML data with Document Object Model (DOM) or XQuery. •To query a database, use SQL •Use Integrators to iterate an array and build algorithms to navigate an object graph. •Use specific APIs to access other data domains, such as an Office Excel file, an e-mail message, or the Microsoft Windows registry. Hence there are different programming models to access different data models or data sources. The problem is that there are many sources of data, and there are just as many query languages to query those data sources. Moreover if the data model is not tied to the language, developers have to manage different type systems. All these differences create an “impedance mismatch” between data and code. What is impedance mismatch? As Databases and programming languages have their own proprietary instruction set, communication between these two entities becomes difficult. This is also known as impedance mismatch between the source code and data. Most developers understand the concept of object-oriented (OO) programming and its related technologies and features, such as classes, methods, and objects. Object-oriented programming has evolved tremendously over the past 10 years or so, but even in its current state, there’s still a gap when using and integrating OO technology with information that is not natively defined or inherent to it. For example, suppose to execute a T-SQL query from within your C# application. It would look something like this: private void Form1_Load(object sender, EventArgs e) { string ConnectionString = @"Data Source=(local); Initial Catalog=BookStore;UID=sa;PWD=“p@ss”; using (SqlConnection conn = new SqlConnection(ConnectionString)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT LastName, FirstName FROM Customers"; using (SqlDataReader rdr = cmd.ExecuteReader()) { / / do something }}} But the code written as shown above may not be ‘‘friendly’’ on several levels. First, it combines two languages into one. The above code is written in a CLR language (in this case C#), there is the SQL language in quotation marks, which is not understood in the context of .NET. With the .NET language VS. Net provides intellisense, but there is no intellisense support in the embedded SQL syntax. More importantly, however, there is no compile-time type checking, which means if something is broken cannot be discovered until run time. Every line of code has to be QA’d just to see if it even begins to work. Microsoft also packed a lot of features into the .NET Framework that enable developers to work with XML. The .NET Framework contains the System.Xml namespace and other supporting namespaces, such as System. Xml.XPath, System.Xml.Xsl, and System.Xml.Schema, which provide a plethora of functionality for working with XML. The namespaces contain many classes and methods that make up the XML .NET API architecture. The main classes are the XmlDocument, XmlReader, and XmlWriter. To add to the complexity of working with different technologies, parsing an XML document isn’t the easiest thing to do, either. Your tools of choice to work with XML are the Document Object Model (DOM), XQuery, or Extensible Style sheet Language Transformations (XSLT). For example, to read an XML document using C#. Net technology, a developer needs to do something like the following: XmlTextReader rdr = new XmlTextReader("C:\Customers.Xml"); while (rdr.Read()) { XmlNodeType nt = rdr.NodeType; Switch (nt) { case XmlNodeType.Element: break; case XmlNodeType.Attribute: break; case XmlNodeType.Comment: break; case XmlNodeType.Whitespace: break; }} The code above read the element, attributes, comments from the Customers.xml file. The XML Api’s also help create XML files XmlTextWriter wrt = new XmlTextWriter("C:\Employees.Xml"); wrt.WriteStartDocument; wrt.WriteComment("This is an example"); wrt.WriteStartElement("Employees"); wrt.WriteStartElement("Employee"); wrt.WriteStartElement("FirstName"); wrt.WriteString("Scott"); wrt.WriteEndElement(); wrt.WriteEndElement(); wrt.WriteEndElement(); The developer would not know if code will work until the project is compiled. Likewise, it is hard to visualize the XML tree the code would generate. XML is great and its use continues to grow. Though it is a popular standard, XML is still hard to work with. Microsoft considered two ways to deal with these situations .The first option, would be to build specific XML or relational data features into each programming language and run-time. That would be a major change and an even more complex to maintain. The second option would be to add more general-purpose query capabilities into the .NET CLR languages as the native syntax. Hence Microsoft introduced a general purpose query facilities to the .NET Framework that apply to all sources of information, not just relational or XML data. This query facility is called .NET Language Integrated Query (LINQ). LINQ tries to solve these issues, offering a uniform way to access and manage data independent of data sources. LINQ leverages commonalities between the operations in these data models instead of flattening the different structures between them.

LINQ LINQ is a set of standard query operators that brings powerful query facilities right into the CLR language such as C# and VB.NET. The LINQ Framework is not just about data access but it also allows to manipulate data. The term language integrated query indicates that query is an integrated feature of the developer’s primary programming languages (e.g., C#, Visual Basic). Language integrated query allows query expressions to benefit from the rich metadata, compile-time syntax checking, static typing and IntelliSense that was previously available only to imperative code. Language integrated query also allows a single general purpose declarative query facility to be applied to all in-memory information, not just information from external sources. Advantages of LINQ 1)Language Integration It is the most fundamental aspect of LINQ.LINQ- language integrated query is a query language that is an integrated feature of the developer’s primary programming languages e.g., C#, Visual Basic. Writing queries is a first –class language construct. 2)Type Checking: Language integrated query allows query expressions to benefit from the rich metadata, compile-time syntax checking, static typing and IntelliSense that was previously available only to imperative code. 3)Standard Query Operators: .NET Language Integrated Query defines a set of general purpose standard query operators that allow traversal, filter, and projection operations to be expressed in a direct yet declarative way in any .NET-based programming language. 4)Can access any data –Collections, Relational and XML LINQ architecture is extensible. The extensibility of the query architecture is used in the LINQ project itself to provide implementations that work over both XML and SQL data and non-relational data. The query operators over XML (XLinq) use an efficient, easy-to-use in-memory XML facility to provide XPath/XQuery functionality in the host programming language. The query operators over relational data (DLinq) build on the integration of SQL-based schema definitions into the CLR type system. This integration provides strong typing over relational data while retaining the expressive power of the relational model and the performance of query evaluation directly in the underlying store. How to access data from variety of data sources using LINQ? The System.Linq namespace provides classes and interfaces that support queries that use Language-Integrated Query (LINQ). The System.Linq namespace is in the System.Core assembly (in System.Core.dll). The Enumerable class contains LINQ standard query operators that operate on objects that implement IEnumerable<T>. The Queryable class contains LINQ standard query operators that operate on objects that implement IQueryable<T>. System.Data.Linq:The System.Data.Linq namespace contains classes that support interaction with relational databases in LINQ to SQL applications. LINQ to SQL is a technology that provides a run-time infrastructure for managing relational data as objects. System.Xml.Linq:Contains the classes for LINQ to XML. LINQ to XML is an in-memory XML programming interface that enables you to modify XML documents efficiently and easily.

LINQ provides different form of LINQ to access data from different data sources LINQ to Objects The existing programming languages do have any facility to query against in-memory collections .But .Net framework 3.0 introduced LINQ as first –class language construct for querying in-memory generic and no –generic collections, string arrays , using LINQ to Objects. LINQ to ADO.NET It includes different LINQ implementations that share the need to manipulate relational data. It includes other technologies that are specific to each particular persistence layer: It includes 1. LINQ to SQL -LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects. 2. LINQ to Dataset – For querying in-memory cached datasets for example datatables. 3. LINQ to Entities -LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. LINQ to XML It allows Querying and manipulating XML data.

Every syntactic query expression in C# begins with a from clause and ends with either a select or group clause. The initial from clause can be followed by zero or more from, or where clauses. Additionally, any number of join clauses can follow immediately after a from clause. Each from clause is a generator that introduces an iteration variable ranging over a sequence, each let clause gives name to the result of an expression and each where clause is a filter that excludes items from the result. Every join clause correlates a new data source with the results of a previous from or join. The final select or group clause may be preceded by an orderby clause that specifies an ordering for the result: The above example read all customers belonging to the city Hove. The variable ‘contacts’ is an implicitly typed variable. ‘Where’ clause is a filtration expression. Select reads name and phone columns

The code on the presentation is as below, var contacts = customers .Where(c => c.City == "Hove") .Select(c => new { c.Name, c.Phone }); Here the query expression returns Customers Name and Phone for all the customers from city ‘Hove’. The above query expression is called as Comprehension syntax. The query expressions can be written using Comprehension syntax or Lambda syntax. Now let us look at example that makes use of comprehension syntax to return the names in uppercase that are five characters long, looks like the code below in Main(). var mResult= from s in names where s.Length == 5 orderby s select s.ToUpper(); foreach (string item in mResult) Console.WriteLine(item); The code above would print ‘CAROL’ ,‘FRANK’ and ‘ROBIN’. Note , here the string array names is queries to find out the names that match the criteria. The result of the query is stored in a var variable. This was impossible in previous version of C#.Net. (Note:- Var – It allows to declare a variable without explicitly declaring its data type. Such variable do not compromise type-safety.) How the query is resolved? The local variable mResult is initialized with a query expression. A query expression operates on one or more information sources by applying one or more query operators from either the standard query operators or domain-specific operators. This expression uses three of the standard query operators: Where, OrderBy, and Select. Where is used for filtering names that are five characters long , followed by sorting the manes using Orderby an finally converting them to uppercase by using s.ToUpper() method. Now let us learn more about another syntax for writing query expressions. As discussed in the previous section, The LINQ query expressions can be also written using Lambda Expressions. What are Lambda Expressions ? A lambda expression is an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types. All lambda expressions use a symbol called lambda operator. It is denoted by => and read as "goes to". The left side of the lambda operator specifies the input parameters (if any) and the right side holds the expression or statement block. Here’s a simple C# example of a lambda expression: x => x * 5 This reads as ‘x goes to x times 5’. The body of a lambda expression may contain multiple statements. It is not necessary to specify a type for input parameters because the compiler will infer the type based on several factors, such as the body of the lambda expression and the underlying delegate type. This expression can be assigned to a delegate type as follows: delegate int del(int i); //declares delegate that takes int parameter and returns an integer static void Main(string[] args) { del myDelegate = x => x * x; // lambda expression int j = myDelegate(5); //Prints j = 25 } In the above code del myDelegate = x => x * x; is a lambda expression that is read as x goes to x*x . The compiler infers that there is a single input from the body of the expression x*x . The result is then assigned to a delegate variable myDelegate . The signature of this delegate matches to the lambda expression . That is Lambda expression takes one input and return an integer values Example 2: int [] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 }; int oddNumbers = numbers.Count(n => n % 2 == 1); The code above is another example of writing query using Lambda’s to print the count of all the even numbers in the numbers integer array.

Like SQL LINQ also provides a set of Operators to query data from data sources–Query Operators. These operators are called as Standard Query Operators. LINQ also provides a set of Operators called as Standard Query Operators that simplifying data access. The standard query operators are defined using static Enumerable and Queryable classes from the System.Linq namespace. Using these operators; we could Filter, Project, Join, Order, Group data in LINQ query. Standard Query Operators are classified into Restriction Operators, Partitioning operators, Concatenation Operators, Join operators, Ordering operators, Set Operators, Grouping operators, Quantifiers, Conversion operators, Element operators, Aggregate operators. The term Language Integrated Query signifies that the standard query facilities are architected directly into the developer’s .NET-supported programming language of choice. These query facilities, known as the standard query operators, expose general-purpose query mechanisms that can be applied to many sources of information, such as inmemory object, collections as well as information retrieved from external sources such as relational data or XML. These operators provide the capability to express query operations directly and declaratively within any .NET-based programming language. Few examples of the Standard query operators: Projection with Restriction Operator : The Select operator projects values from a single sequence or collection and restriction operator helps to filter the values. These operator returns an enumerable object. When the object is enumerated, it produces each element in the selected results. Example :Write a query to return all those with price larger than 20. Comprehension Syntax: IEnumerable<string> mGamelist = from g in games where g.Price < 20 select g; foreach (string s in gamelist) Console.WriteLine(“Games: ,0-", s); The above query is equivalent to Lambda Expression as shown below var mResult= games.Select(g => g). Where(g => g.Price < 20); Another example of Projection Operator : Return FirstName, LastName and Contact of all those customers who LastName start with the character ‘A’. IEnumerable<string> query = from c in contact where c.FirstName.StartsWith(“A") select new {c.FirstName, c.LastName, c.Contact} This example could also be written using Lambda syntax as follows: var query =contact.Select(c => new {c.FirstName, c.Lastname, c.Contact}).Where(c => c.FirstName.StartsWith(“A")); Order By Operator: The Order By operator helps sorting the values in an ascending order. The sorting operators—OrderBy, OrderByDescending, ThenBy, ThenByDescending, and Reverse—provide the capability to sort the results in an ascending or descending manner. Example : To find out games with size larger than 7 and sort them as well. string*+ games = , “Car Race", “San Adrias", “V City", "The Darkness", “Mission I", “Rescue Operations" -; var subset = from g in games where g.Length > 7 orderby g select g; foreach (var game in subset) Console.WriteLine(game); Console.WriteLine(); The Lambda equivalent of the above query is : var subset = games.Where(game => game.Length > 7). OrderBy(game => game).Select(game => game); OrderByDescending The OrderByDescending operator sorts the resulting values of the sequence in descending order. The following shows how to sort a sequence in descending order: IEnumerable<string> query =from c in contact where c.FirstName.StartsWith("S") orderby c.LastName descending select new {c.FirstName, c.LastName, c.EmailAddress} This example could also be written using method syntax as follows: var query = contact.Select(c => {c.FirstName, c.LastName, c.EmailAddress} ).Where(c => c.FirstName.StartsWith("S")).OrderByDescending(c => c.FirstName); Grouping Operator: Grouping is the concept of grouping the values or elements of a sequence according to a specified value (selector). LINQ contains a single grouping operator, GroupBy. It groups elements that share a common attribute. Each group is represented by an IGrouping<TKey, TElement> object. GroupBy : To group all the even elements in the List<> collection as shown below, Example I: List<int> numbers = new List<int>() { 5, 22, 10, 44, 4340, 15, 991, 57, 68, 87 }; IEnumerable<IGrouping<int, int>> query = from number in numbers group number by number % 2; foreach (var group in query) {Console.WriteLine(group.Key == 0 ? "\nEven numbers:" : "\nOdd numbers:"); foreach (int i in group) Console.WriteLine(i); } Console.ReadLine(); Result : Odd numbers:5 15 991 57 87 Even numbers:22 10 44 4340 68 Example II: string[] words = { "Animal","Grapes","Pearl","Grain","Apple","Pineapple" }; var wordGroups =from w in words group w by w[0] into g select new { FirstLetter = g.Key, Words = g }; foreach (var g in wordGroups) { Console.WriteLine("Words that start with the letter '{0}':", g.FirstLetter); foreach (var w in g.Words) { Console.WriteLine(w); } }Console.ReadLine(); Result: Words that start with the letter 'A‘:Animal Apple Words that start with the letter 'G‘:Grapes Grain Words that start with the letter 'P‘:Pearl Pineapple Aggregating Operators Aggregate functions perform calculations on a set of values and return a single value, such as performing a sum or average of values of a given elements. There are seven LINQ aggregate query operators: Aggregate; Average, Count,Max, Min, and Sum. Max :The Max operator returns the maximum value within a sequence. Like the Average operator, Max supports many data types, including decimals, integers, and doubles. double[] temperatures = { 28.0, 19.5, 32.3, 33.6, 26.5, 29.7 }; double maxTemp = temperatures.Max(); Console.WriteLine(maxTemp); // The result is: 33.6 Or a lambda equivalent of the above query is var highTemp = (from p in temperatures select p).Max();

LINQ-to-Objects provides the developer a query facility to conduct queries against an in-memory collection of objects. The collection may be user-defined or may be returned by a .NET Framework API.The techniques used to query against such collections of objects are similar to but simpler than the approaches used to conduct queries against a relational database using SQL statements. It can be sued to query in-memory collections like arrays, queries with any IEnumerable or Ienumerable<T> collection (Arraylist, List, Dictionary etc.)directly . That is it does not require the use of an intermediate LINQ provider or API such as LINQ to SQL or LINQ to XML. Consider an example to find out and sort the numbers that are larger than 5 . The C3 code required to achieve the purpose is as below int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2 }; ///declare and initialize array int[] sorted=new int[4]; int i=0,j=0; for (i = 0; i < numbers.Length; i++) { if (numbers[i] > 5) { sorted[j] = numbers[i]; //Find out an element larger than 5 j++; } } for (i = 0; i < sorted.Length; i++) { int smaller = i; for (j = i + 1; j < sorted.Length; j++) { if (sorted[smaller] > sorted[j]) // Sort all larger elements smaller = j; } if (i != smaller) { int temp = sorted[i]; sorted[i] = sorted[smaller]; sorted[smaller] = temp; } } foreach (int no in sorted) Console.WriteLine(no); //print the result Console.ReadLine(); int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2 }; var lowNums = from n in numbers where n > 5 Order by n select n; Consider the case where the developer has to write the C# code to print the number names for each digit in the first array. ,using C# foreach iterator . Microsoft’s LINQ-to-Object is facility that simplifies a developers task and makes querying collection easy by reducing the amount of code to be written to filter, sort, traverse, concat etc., the elements in the collection In a basic sense LINQ-to-Objects represents a new approach to query collections. Using older approach, demands writing complex foreach loops that specified how to retrieve data from a collection. In the LINQ approach, a developer write declarative code that describes what you want to retrieve. In addition, LINQ queries offer three main advantages over traditional foreach loops: 1. They are more concise and readable, especially when filtering multiple conditions. 2. They provide powerful filtering, ordering, and grouping capabilities with a minimum of application code. 3. They can be ported to other data sources with sight or even no modifications at all. Few more Examples: Example I: string*+ names = , “Ryna", “Anna", “Maggie", “Amenda", “Angelina", “Andy" -; var result = from n in names where b.StartsWith(“A") Orderby n select b; foreach (string s in result ) { sb.Append(s + Environment.NewLine); } In the above example, the query sorts and returns the names that starts with the character ‘A’. Example II: Let us look at how to write query expressions for ArrayList. Consider the class below – public class Product { public string ProductCode{ get; set; } public string ProductName { get; set; } public int Price { get; set; } } ArrayList arrList = new ArrayList(); arrList.Add( new Product ,ProductCode= “A11", ProductName = “LCD Monitor“, Price=10000-); arrList.Add( new Product ,ProductCode= “A12", ProductName = “Printer“, Price=7000-); arrList.Add( new Product ,ProductCode= “A13", ProductName = “CPU“, Price=15000-); …………………….. To query the products whose price is above 4500, the expression can be written as shown below, var query = from Product product in arrList where product.Price > 4500 select student; In general, to perform the more complex operations on the data, LINQ queries outperform the traditional iteration techniques.

The code on the presentation shows

LINQ-to-Object query

expression to query the string array-names. It returns names with size larger than 5, . Additionally the names are sorted as well.

Querying Non generic Collections: LINQ to Objects can be used with any type that implements IEnumerable<T>. This means that LINQ to Objects will work with custom generic collection types or .Net built in generic collections. In fact, only strongly-typed collections implement this interface. Arrays, generic lists and dictionaries are strongly-typed: you can work with an array of integers, a list of strings or a dictionary of Book objects. The non-generic collections do not implement IEnumerable<T>, but implement IEnumerable. This implies that LINQ cannot be used with ArrayList or DataSet objects, for instance. Fortunately, solutions exist in the form of Cast Operator. The Cast operator casts the elements of a source sequence to a given type. Consider the class Student as shown below public class Student { public string FirstName { get; set; } public string LastName { get; set; } public int[] Scores { get; set; } } The preceding code snippet create a collection of type ArrayList to hold multiple student objects. ArrayList arrlist = new ArrayList(); arrlist.Add(new BookInformation {Title="ASP.Net 3.5",Author="ABC",Price=645 }); arrlist.Add(new BookInformation { Title = "LINQ Bible", Author = "Anna", Price = 450 }); arrlist.Add(new BookInformation { Title = "LINQ Guide", Author = "Mathews", Price = 300 }); arrlist.Add(new BookInformation { Title = "LINQ with ASP.Net", Author = "Fedrick", Price = 275 }); Now, that the collection is ready , How can it be queried? It can be done in two ways as shown below var titles = from book in arrlist.Cast<BookInformation>() where book.Title.Contains("LINQ") select book ; Or var t = from BookInformation bks in arrlist where bks.Title.Contains("LINQ") select bks; The code to print the query result obtained is : foreach (BookInformation item in titles ) { str += item.ToString(); } The above loop prints all the books with tile that contain the word â&#x20AC;&#x2DC;LINQâ&#x20AC;&#x2122;.

Querying Generic Collections Using LINQ In this demonstration , the Student class that was declared in previous section is used. The instance of generic collection List<T> is used. It holds the objects of Student class. The collection populated with the data as shown below List<Student> arrList = new List<Student>(); arrList.Add( new Student { FirstName = "Anna", LastName = "Houston", Scores = new int[] { 98, 92, 81, 60 } }); arrList.Add( new Student { FirstName = "Daniel", LastName = "Demello", Scores = new int[] { 75, 84, 91, 39 } }); arrList.Add( new Student { FirstName = "Steven", LastName = "Roberts", Scores = new int[] { 88, 94, 65, 91 } }); arrList.Add( new Student { FirstName = "Julia", LastName = "Gray", Scores = new int[] { 97, 89, 85, 82 } }); The query expression for listing the students with score higher than 95 is as follows, var query = from student in arrList where student.Scores[0] > 95 select student; The result obtained is used to populate ComboBox as follows, foreach (Student s in query) cmbStudents.Items.Add(s.FirstName + " " + s.LastName + ": " + s.Scores[0]);

LINQ to ADO. Net Technologies : Primary goal of the upcoming version of ADO.NET is to raise the level of abstraction for data programming, thus helping to eliminate the impedance mismatch between data models and between languages that application developers would otherwise have to deal with. Two innovations that make this move possible are LanguageIntegrated Query and the ADO.NET Entity Framework. The Entity Framework exists as a new part of the ADO.NET family of technologies. ADO.NET will LINQ-enable many data access components: LINQ to SQL, LINQ to DataSet and LINQ to Entities. This document describes the ADO.NET Entity Framework, what problem spaces it is targeting and how its various components address those problems. The ADO.NET Entity Framework enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits: Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships. Applications are freed from hard-coded dependencies on a particular data engine or storage schema. Mappings between the conceptual model and the storage-specific schema can change without changing the application code. Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems. Multiple conceptual models can be mapped to a single storage schema. Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model. LINQ to Entities Through the Entity Data Model, relational data is exposed as objects in the .NET environment. This makes the object layer an ideal target for LINQ support, allowing developers to formulate queries against the database from the language used to build the business logic. This capability is known as LINQ to Entities. (Note : Covering details on Entity Framework is beyond the scope of this session)

LINQ to SQL LINQ to SQL a form of LINQ, that maps relational data as objects. That is it provides Object relational Mapping(ORM). The data model of a relational database is mapped to an object model expressed in the programming language of the developer by creating Entity classes. These classes are publicand mapped by annotating the class with the LINQ [Table] Attribute, passing it the name of the SQL Server table to map to using the attribute’s Name parameter. After having mapped a data model to an object model, it uses another object called DataContext . It retrieves objects from the database and resubmits changes as well. The DataContext class is part of the System.Data.Linq namespace, and its purpose is to translate your requests from .NET objects to SQL queries, and then reassemble the query results back into objects. LINQ to SQL Object Model : 1) Entity class that maps to database table. 2) Members of Entity class map to database table columns 3) DataContext that maps to database LINQ to SQL map database objects to an object model defined in the user’s programming language. This is facilitated by The Object Relational Designer (ORD) tool of VS.NET. The Entity classes and DataContext is created the when the .dbml file is created using VS.NET. This file includes both the designer view and the code as well. Mapping Tables Database tables are represented by entity classes in LINQ to SQL. An entity class is a normal class except that it is annotated with a specific tag that maps, or associates, that class with a specific database table. The Table attribute is required by LINQ to SQL, and maps an entity class (a class that has been designated as an entity) to a table or view. The Table attribute also has a single property, Name, which specifies the name of the relational table or view. Mapping Columns Once the table is mapped to an entity class, table columns must be mapped to class properties. The Column attribute maps a column of a database table to a member of an entity class. Fields or properties are designated to represent database columns, and only those fields or properties that are mapped are retrieved from the database. Here’s an example of an entity class, [Table(Name="dbo.Book")] public partial class Book { [Column(Storage="_BookCode", DbType="VarChar(5) NOT NULL", CanBeNull=false, IsPrimaryKey=true)] private string _BookCode; [Column(Storage="_BookName", DbType="VarChar(250) NOT NULL", CanBeNull=false)] private string _BookName; [Column(Storage="_Price", DbType="Int NOT NULL")] private int _Price; [Column(Storage="_PublisherName", DbType="VarChar(80) NOT NULL", CanBeNull=false)] private string _PublisherName; } Why ? There is a huge divide between modern programming languages and databases in how they represent and manipulate information. That is, the ddatabases and programming languages have their own proprietary instruction set, communication between these two entities becomes difficult. This is also known as impedance mismatch between the Source Code and Data

Data Access using LINQ to SQL In LINQ to SQL, the relational data model is expressed as object model using CLR languages. The two main facets of LINQ to SQL are : DataContext and Entity class. The Entity class maps the database table. The DataContext class maps database. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that could be used from within programming language. Developers using Visual Studio typically use the Object Relational Designer, which provides a user interface for implementing many of the features of LINQ to SQL. This ORM designer in VS.NET generates Entity class and the necessary DataContext class. The code on the presentation is : mDataContext = new EbookStoreDataContext(); var mresult = from book in mDataContext.Books where book.PublisherName.StartsWith("T") orderby book.BookCode select new { book.BookCode, book.BookName, book.Price, book.PublisherName }; grdViewBooks.DataSource = mresult; The above creates an instance of Datacontext. It is used to query the entity collection book that maps to database tables Book. The query searches for all the books where the publisher name starts with “T” and at the same time they are sorted as well. The result obtained din the variable ‘mresult ‘m is bound to the DataGridView on the form as shown in the output on the presentation.

Methods For Data Modification Using LINQ to SQL DataContext represents the main entry point for the LINQ to SQL framework. Like the SqlConnection class, the DataContext instance accepts a connection string. Once the connection is made, data is read from, and changes are made to the database through the DataContext instance. However, the DataContext differs from the SqlConnection class as it does the work of converting the objects into SQL queries and vice-versa. It tracks changes that you made to all retrieved entities and maintains an "identity cache" that guarantees that entities retrieved more than one time are represented by using the same object instance. Essential methods for performing CRUD(Create, Read, Update and Delete operations) SubmitChanges(): It is a method of DataContext that transmits the changes back to the database. InserOnSubmit() : A method of Table<TEntity> class that adds an entity in a pending insert state to this Table<TEntity>. DeletOnSubmit() : A method of Table<TEntity> class that puts an entity from this table into a pending delete state.

CRUD operations using LINQ to SQL Insert a row in the database table using LINQ to SQL : To insert a row in the table, first an instance of entity class is created and initialized with the necessary values . In the above example an instance of entity book is creates and initialized with the values like ‘S11’ for BookCode, ‘LINQ Programming Guide’ for BookName , 745 for Price and ‘SeedInfotech’ is assigned for PublisheName of an entity. Once all the properties are set , then call InsertOnsumit on the entity instance to add the new object to the Book collection of DataContext. Then to transmit the changes to the database call SubmitChanges () on the DataContext instance. This method translating the book object data into an insert SQL query that is executed by the database.

CRUD operations using LINQ to SQL Modifying a row using LINQ to SQL To modify a row in the table, first check whether the row exists by querying the object collection of DataContext. If the row is available make the necessary changes to the object and then call SubmitChanges (). This method translating the book object data into an update SQL query that is executed by the database. In the above example the exiting row with the BookCode ‘S11’ is modified. It’s Price and PublisherName is changed to 890 and ‘Wrox Publication’ respectively.

CRUD operations using LINQ to SQL Deleting a row using LINQ to SQL To delete a row in the table, first check whether the row exists by querying the object collection of DataContext. If the row is available call DeletOnSubmit() on the entity instance and then call SubmitChanges(). This method translating the book object data into an delete SQL query that is executed by the database. In the above code, The row with the BookCode â&#x20AC;&#x2DC;S11â&#x20AC;&#x2122; is identified and then deleted from the entity collection by calling DeleteOnSubmit(). The method DeleteOnSubmit() arks the entity for deletion and the SubmitChanges() when invoked on the DataContext deletes the row from the database.

LINQ to DataSet The ADO.Net object Datset from System.Data namespace is an in-memory representation of a set of data. It is useful to get a disconnected copy of data that comes from an external data source. Regardless of the data source, the internal representation of a DataSet follows the relational model, including tables, constraints, and relationships among the tables. In other words, the DataSet can be considered as a sort of in-memory relational database. This makes it a good target for a LINQ implementation. In order to query the data within a DataSet, the DataSet API includes methods, such as DataTable.Select(), for searching for data in certain, somewhat predefined ways. However, there hasnâ&#x20AC;&#x2122;t been a general mechanism available for rich query over DataSet objects that provided the typical expressiveness required in many data-centric applications. LINQ provides a unique opportunity to introduce rich query capabilities on top of DataSet and to do it in a way that integrates with the environment. Traditional approach to Querying dataset : DataRow.GetChildRows() :The GetChildRows gets the child rows of this DataRow using the specified DataRelation. The following example uses the GetChildRows to return the child DataRow objects for every child DataRelation in a DataTable. The value of each column in the row is then printed. private void GetChildRowsDemo (DataTable table) { DataRow[] arrRows; foreach(DataRelation relation in table.ChildRelations) { foreach(DataRow row in table.Rows) { arrRows = row.GetChildRows(relation); // Print values of rows. for(int i = 0; i < arrRows.Length; i++) { foreach(DataColumn column in table.Columns) { Console.WriteLine(arrRows[i][column]); } }} }} But this method will work only if the DataRelation is set between the tables. With LINQ to dataset , the join queries can be written even without DataRelation set as shown below, DataTable orders = ds.Tables["SalesOrderHeader"]; DataTable orderLines = ds.Tables["SalesOrderDetail"]; var ordersQuery = orders.ToQueryable(); var orderLinesQuery = orderLines.ToQueryable(); var query = from o in ordersQuery join ol in orderLinesQuery on o.Field<int>("SalesOrderID") equals ol.Field<int>("SalesOrderID") where o.Field<bool>("OnlineOrderFlag") == true && o.Field<DateTime>("OrderDate").Month == 8 select new { SalesOrderID = o.Field<int>("SalesOrderID"), SalesOrderDetailID = ol.Field<int>("SalesOrderDetailID"), OrderDate = o.Field<DateTime>("OrderDate"), ProductID = ol.Field<int>("ProductID") }; The above query is a traditional join to obtain the order lines for the online orders for August. Select Method: This method gets an array of all DataRow objects that match the filter criteria, in the specified sort order. private void GetRowsBycriteria() { DataTable table = ds.Tables["Orders"]; // Presuming the DataTable has a column named Date. string expression; expression = "Date > #04/07/11#"; DataRow[] foundRows; // Use the Select method to find all rows matching the filter. foundRows = table.Select(expression); // Print column 0 of each returned row. for(int i = 0; i < foundRows.Length; i ++) { Console.WriteLine(foundRows[i][0]); } } The select() on DataTable does not support rich query capabilities like group by, joins, aggregates etc. Hence LINQ to Dataset is a better choice to write complex queries while querying datasets.

LINQ to Dataset DataSet exposes DataTable objects as enumerations of DataRow objects. The standard query operatorsâ&#x20AC;&#x2122; implementation actually executes the queries on enumerations of DataRow objects. The example on the presentation show demonstrates how to query the data in datatable-Book. The code listed on the presentation include a line IEnumerable<DataRow> query = from book in mBooks.AsEnumerable(); . It uses AsEnumerable() on datatable. LINQ queries work on data sources that implement the IEnumerable<T> interface or the IQueryable interface. The DataTable class does not implement either interface, so call the AsEnumerable method to use the DataTable as a source in the From clause of a LINQ query. The enumerable object returned by the AsEnumerable method is permanently bound to the DataTable that produced it. Multiple calls to the AsEnumerable method will return multiple, independent queryable objects that are all bound to the source DataTable. Field<T> :The Field<T> method provides a way to get the value of a column within a DataRow without having to worry about the different representations of null values in DataSet and LINQ and also provides a way have comparisons of values correctly evaluated. The DataSet class represents null values with the Value instance of the DBNull class. At run time a LINQ query that accessed a column with a null value would generate a InvalidCastException. Also, DataSet does not support nullable types. The Field method provides support for accessing columns as nullable types. If the underlying value in the DataSet is Value, the returned nullable type will have a value of null. If the value of the specified DataColumn is null and T is a reference type or nullable type, the return type will be null. The Field method will not return Value. The Field method does not perform type conversions. If type conversion is required, first obtain the column value by using the Field method and then column value should then be converted to another type. So in the code above the line included is : str += row.Field<string>("BookName") + "\n"; Converts a column value to string.

LINQ to XML –XLINQ XLINQ was developed with Language Integrated Query over XML data . It takes advantage of the Standard Query Operators and adds query extensions specific to XML. Like XML , XLinq also provides the query and transformation power of XQuery and XPath integrated into .NET Framework languages support LINQ pattern (e.g., C#, VB, etc.). This provides a consistent query experience across LINQ enabled APIs and allows you to combine XML queries and transforms with queries from other data sources. XLINQ is designed to be a lightweight XML programming API. Advantages of XLINQ XLINQ is designed to be a lightweight XML programming API that supports following features •Load XML into memory in a variety of ways (file, XmlReader, etc.). •Create an XML tree from scratch. •Insert new XML Elements into an in-memory XML tree. •Delete XML Elements out of an in-memory XML tree. •Save XML to a variety of output types (file, XmlWriter, etc.). •Supports the Standard Query Operators and adds query extensions specific to XML. The classes from System.Xml.Linq namespace are used to take the advantage of LINQ features while reading and writing XML data.

Features of XLINQ Create XML data from scratch by using functional construction. Functional construction lets you create all or part of your XML tree in a single statement as shown in the example below XElement contacts = new XElement("contacts", new XElement("contact", new XElement("name", "Patrick Hines"), new XElement("phone", "206-555-0144"), new XElement("address", new XElement("street1", "123 Main St"), new XElement("city", "Mercer Island"), new XElement("state", "WA"), new XElement("postal", "68042") ))); Query using XML axis LINQ to XML provides this capability through axis methods, which are methods on the XElement class, each of which returns an IEnumerable collection. These methods can be used to return the structured, or complex, content of a node such as child and ancestor elements. LINQ to XML axis methods enable you to work with nodes instead of individual elements and attributes, providing the capability to return collections of elements and attributes. This lets developers work at a finer level of detail. This section explores a few of the main axis methods of the XElement class: Ancestors : The Ancestors method returns the ancestor elements of the specified node. Descendants Descendants are those elements below the specified element in an XML tree. The Descendants method returns a collection of elements that are descendants of the specified element. AncestorsAndSelf : The AncestorsAndSelf method is almost identical to the Ancestors method, but it varies in the fact that it returns the current element along with its ancestors. DescendantsAndSelf : The DescendantsAndSelf method is almost identical to the Descendants method; it varies only in that it returns the current element along with its descendants. ElementsAfterSelf and ElementsBeforeSelf The ElementsAfterSelf and ElementsBeforeSelf methods return the elements that come after the specified element and the elements that come before the specified element, respectively. Each method takes an overload that returns the elements after, or before, the current element that match the specified element name. Example: XElement ce = root.Element(“Products"); IEnumerable<XElement> des = from el in ce.AncestorsAndSelf() select el; foreach (XElement el in des) listBox1.Items.Add(el.ProductName); XLIQ can load XML from files or streams as shown in the example below XElement root = XElement.Load(@"c:\Products.xml"); Serialize XML to files or streams. Example : TextReader mreader = new StringReader(@" <Employees> <Employee id=’1’ phone=’555-555-55551> <Name>Anna Jones</Name> <Department>Gaming</Department> <Gender>Female</Gender> <MaritalStatus>M</MaritalStatus> </Employee> <Employee id=’2’ phone=’555-555-5552’> <Name>Scott white</Name> <Department>Administration</Department> <Gender>Male</Gender> <MaritalStatus>M</MaritalStatus> </Employee> <Employee id=’3’ phone=’555-555-55573> <Name>Joe Walsh</Name> <Department>Networking</Department> <Gender>Male</Gender> <MaritalStatus>M</MaritalStatus> </Employee> </Employees>"); XElement mElement = XElement.Load(tr); mreader .Close(); mElement .Save(@"C:\Employees.xml"); Manipulate the in-memory XML tree by using methods such as Add, Remove, ReplaceWith, and SetValue. Example I XElement mobilePhone = new XElement("phone", "206-555-0168"); contact.Add(mobilePhone); Deleting element XLINQ supports modifying XML tree structure by allowing to delete elements from tree structure. Example II contact.Element("phone").Remove();

Working with XLINQ XLinq is the fact that XLinq represents a new, modernized in-memory XML Programming API. Querying XML Data: Language Integrated Query provides a consistent query experience across different data models as well as the ability to mix and match data models within a single query. string str = " "; //Using XLINQ XElement root = XElement.Load(@"c:\Products.xml"); var productNames = from prod in root.Elements("Product") orderby (string)prod.Element("Name") select (string)prod.Element("Name"); foreach (var el in productNames) { str += el + "\n"; } lblData.Text = str; The code above load a Products.xml file and queries Now let us look at few more examples to query XML data. This query retrieves all of the contacts from Washington, orders them by name, and then returns them as string (the result of this query is IEnumerable<string>). var result= from c in contacts.Elements("contact") where (string) c.Element("address").Element("state") == "WA" orderby (string) c.Element("name") select (string) c.Element("name"); This query retrieves the contacts from Washington that have an area code of 206 ordered by name. The result of this query is IEnumerable<XElement>. Var result =from c in contacts.Elements("contact"), ph in c.Elements("phone") where (string) c.Element("address").Element("state") == "WA" && ph.Value.StartsWith("206") orderby (string) c.Element("name") select c; Here is another example retrieving the contacts that have a net worth greater than the average net worth. var x= from c in contacts.Elements("contact"), average = contacts.Elements("contact"). Average(x => (int) x.Element("netWorth")) where (int) c.Element("netWorth") > average select c; Creating XML In object oriented programming when you create object graphs, and correspondingly in W3C DOM, when creating an XML tree, you build up the XML tree in a bottom-up manner. XLinq provides a powerful approach called functional construction to create XML elements. For example, to create a contacts XElement, you could use the following code: XElement contacts =new XElement("contacts", new XElement("contact", new XElement("name", “Ryna Turner"), new XElement("phone", “111-222-3333"), new XElement("address", new XElement("street1", “456 main Street"), new XElement("city", “Mary Island"), new XElement("state", "WA"), new XElement("postal", "68042") ))); By indenting, the XElement constructor resembles the structure of the underlying XML.