Language Integrated Query
LINQ (short for Language Integrated Query, pronunciation Link ) is a programmatic method from Microsoft for accessing data. LINQ masterminded developed by Erik Meijer.
- 4.1 From
- 4.2 Where
- 4.3 Select
- 4.4 Group
- 4.5 Into
- 4.6 orderby and ThenBy
- 4.7 Reverse
- 4.8 Join
- 4.9 Let
- 4:10 Any
- 4:11 Contains
- 4:12 Skip and Take
- 4:13 Distinct
- 4:14 Union, Intersect and Except
- 4:15 aggregates
- 4:16 Zip
- 4:17 Concat
- 4:18 SequenceEqual
- 4:19 SelectMany
- 4:20 scalar selectors
- 5.1 Defining your own monads 5.1.1 identity
- Maybe 5.1.2
- 7.1 LINQ to DataSet
- 7.2 LINQ to XML
- 7.3 with LINQ Rx
Goal of LINQ
The data is accessed by the program have come from different sources. These include
- Internal sources such as fields as well as object lists and hierarchies;
- External sources such as XML documents, database tables, text files, Excel files, e- mail messages, SharePoint lists and many others.
Each of these data sources has its own access methods. This means that the programmer has to deal with the details of each method in each individual case. Furthermore, every time the program needs to be changed when data is moved from a source to a source of a different type.
LINQ tries to eliminate this problem by providing a uniform method for all data access available within the development platform. NET. The syntax of the queries in LINQ is similar to that of SQL. Unlike SQL, however, LINQ also provides language elements available that are suitable for accessing hierarchical and network structures by exploiting the existing relations there.
Operation
LINQ is a set of extension methods that operate on monads. In addition, there are some. NET languages like C #, VB.NET and F # your own keywords for a predetermined amount of LINQ methods. Monads are mapped. NET as generic classes or interfaces with single type argument (eg IEnumerable, IObservable Of T ).
LINQ statements are embedded directly in source code. NET programs. Thus, the code can be checked by the compiler errors. Other methods such as ADO and ODBC does use query strings. This can only be interpreted at runtime; then act serious errors and are more difficult to analyze.
Within the source program in C # or VB.NET LINQ presents the query results as strongly typed lists. Thus, it ensures type safety at compile time.
So-called LINQ provider ( engl: LINQ provider) translate the LINQ statements to the special access methods of the respective data source.. Within the NET platform, among others, the following providers are available:
- LINQ to Objects to access lists and object hierarchies in the main memory
- LINQ to SQL to query and manipulate data in MS SQL databases
- LINQ to Entities to query and manipulate data in the relational model of ADO.NET;
- LINQ to XML to access XML content
- LINQ to DataSet for accessing ADO.NET data collections and tables
- LINQ to SharePoint to access SharePoint data.
Important concepts
Deferred evaluation
LINQ expressions are not executed by their definition, but if the value is queried. This is called deferred evaluation (English: deferred evaluation or lazy evaluation ) refers. Thus, the query can be used several times.
List
If the LINQ query calls a function that requires exceptional treatment, the try-catch block must embrace the use of the query and not their creation.
Resolution of extension methods
LINQ functions as extension methods (English: extension method ) implemented. Consider for example, a class that represents a list of employees:
Public sealed class Employees: List
Public static class EmployeeExtension
{
public static IEnumerable Where
Var query = from e in Employees where e.DepartmentId == 5 select e; These keywords are resolved by the compiler in the appropriate method calls:
Var query = employees.Where (e = > e.DepartmentId == 5 ) Select ( e = > e).; Since they are extension methods, the compiler needs to resolve method calls in a call to the methods of the appropriate extension class:
Var query = Enumerable.Select ( EmployeeExtension.Where ( developers, e = > e.DepartmentId == 5 ), e = > e );
Important operators
From
From defines the data source of a query (query) or subquery (subquery ), and represented a range variable (range variable ) by a single element of the data source ( dataSource ).
From range variable in datasource / / ... Queries can have a plurality of operations from to allow joins of multiple data sources. This is important to note that the join condition with multiple from operations is defined by the data structure and is different from the concept of joins in relational databases.
Var query results = from c in customers from o in orders select new { c.Name, o.OderId, o.Price }; or shorter:
Var query results = from c in customers, o in orders select new { c.Name, o.OderId, o.Price }; Where
Where defines a filter to the data to be selected.
Var query results = from c in customers from o in orders where o.Date > DateTime.Now - TimeSpan ( 7,0,0,0 ) / / Only orders from last week select new { c.Name, o.OderId, o.Price }; Select
Select defines the shape of the result of the LINQ query.
Group
Group is used to group items by a specific key:
Var = groupedEmployees from e in Employees group e by e.Department / / group first by department group e by E.age / / then group by age select e; The key can also be used an anonymous type, which is composed of several keys:
Var = groupedEmployees from e in Employees group e by new { e.Department, E.age } / / group by department and age select e; into
Into can be used for the result of a select to save group or join operation in a temporary variable.
Var = groupedEmployees from e in Employees group e by e.Department into EmployeesByDepartment select new { Department = EmployeesByDepartment.Key, EmployeesByDepartment.Count () }; Orderby and ThenBy
Orderby and ThenBy is used to display a list of elements in ascending order sort.
Var = groupedEmployees from e in Employees orderby E.age / / order Employees by age; youngest first ThenBy e.Name / / order same- age Employees by name; sort A- to-Z select e; With the help of OrderByDescending and ThenByDescending the list is sorted in descending order:
Var = groupedEmployees from e in Employees orderby descending E.age / / oldest first ThenBy e.Name descending / / sort Z -to -A select e; Reverse
Reverse reverse the order of the elements.
Join
Join allows inner joins, group joins and left outer joins.
Var = ProductCategories from c in categories / / outer datasource join p in products / / inner datasource on c.CategoryId equals p.CategoryId / / categories without products are ignored select new { c.CategoryName, p.ProductName }; Group Join A group join produces a hierarchical result set. Here, the elements of the internal data source to the corresponding elements of the external data source are grouped together. Elements for which no corresponding element of the external data source, there can be connected to an empty array. var = ProductCategories from c in categories join p in products on c.CategoryId equals p.CategoryId into productsInCategory select new { c.CategoryName, Products = productsInCategory }; A group join type is not possible in SQL, because SQL does not allow hierarchical result set. VB.NET has with Group Join a private keyword. Left Outer Join A left outer join forms the external data source to the internal data source and provides a "flat" result. Elements of the external data source to which no suitable element of the internal data source exists, are provided with a default value. To define the default value of the DefaultIfEmpty () extension method. var = ProductCategories from c in categories join p in products on c.CategoryId equals p.CategoryId into productsInCategory from pic in productsInCategory.DefaultIfEmpty ( new Product ( CategoryId = 0, ProductId = 0, Product Name = String.Empty ) ) select new { c.CategoryName, p.ProductName }; Let
Let it possible to store the result of a partial query in a variable to be put in to use in the query later.
Var = ordersByProducts from c in categories join p in products on c.CategoryId equals p.CategoryId into productsByCategory let ProductCount = productsByCategory.Count () orderby ProductCount select new { c.CategoryName, Product Count }; Any
Any is used to determine if a sequence is empty or contains a certain predicate.
Bool containsAnyElements = Enumerable.Empty
Contains is used to determine whether a given value is contained in a sequence.
. bool containsSix = Enumerable.Range (1.10 ) Contains ( 6); / / True Skip and Take
Skip is used by a certain number of elements of a sequence to skip. Take is used to set a maximum number of elements of a sequence selected.
IEnumerable
Distinct
Distinct is used to select unique elements of a sequence.
IEnumerable
For a list of items set operators Union, Intersect and Except may be used:
Var NumberSet1 = { 1,5,6,9 }; var NumberSet2 = { 4,5,7,11 }; var union = NumerSet1.Union ( NumberSet2 ); / / 1,5,6,9,4,7,11 var intersect = NumerSet1.Intersect ( NumberSet2 ); / / 5 var = except NumerSet1.Except ( NumberSet2 ); / / 1,6,9 aggregates
Aggregate is used by an aggregate function applied to a data source.
Var nums = new [] { 1,2,3,4,5 }; var nums.Aggregate sum = ((a, b) = > a b); / / Sum = 1 2 3 4 5 = 15 In addition, important aggregate functions are predefined. Pre-defined aggregate functions are about Count (), Long Count (), Sum ( ), Min (), Max ( ) and Average ().
Zip
Zip combines two sequences to one another until a sequence to an end.
IEnumerable
Concat appends a sequence another sequence of the same type.
SequenceEqual
SequenceEqual checks whether the two sequences have the same length and that the elements at the respective position of the respective sequences are the same.
For comparison, either the IEqualityComparer interface
SelectMany
SelectMany is essentially used to flatten a hierarchy. SelectMany works here as the bind operator >> =, also called shovel ( shovel), in Haskell.
Class Book
{
public string Title {get;
set;
}
public List
IEnumerable
LINQ defines various selectors for Scalar results:
Extending LINQ
Defining your own monads
LINQ can be applied to arbitrary monads.
Monads are here Adapters (English: wrapper ) for a particular type.
Predefined monads are, for example, IEnumerable, IList Of T, Nullable
However, their own monads such as IRepository
Identity
The simplest monad is the identity, which is commonly referred to as NET Identity
Public class Identity
/ / Unit Method
/ / Converts any value into an identity
public static Identity
Var hello = "Hello" ToIdentity () Bind ( h => " Monad " ToIdentity () Bind ( m = > String.Format (, h, m)). . "{1 } { 2} " ). .; Console.WriteLine ( HELLO.value ); / / "Hello Monad! " To use the monad in LINQ, a SelectMany () extension method must be implemented. This is merely an alias for Bind ( ). It is therefore possible
/ / SelectMany = Bind
public static Identity SelectMany (this Identity m, Func < A, Identity > f)
{
return Bind ( m, f);
/ / Alternatively with dissolved Bind ( ):
/ / Return f ( m.Value );
}
/ / Bind function with composition
public static Identity
Var hello = from h in "Hello". ToIdentity () from m in " Monad ". ToIdentity () select String.Format ("{ 1} { 2} ", h, m); Console.WriteLine ( HELLO.value ); / / "Hello Monad! " Maybe
Another simple Monad is Maybe
Definition of the monad:
Maybe class
Public static Maybe
Private static Maybe Bind
/ / Null -propagation of nullables
var r = from x in 5.ToMaybe ()
from y in Maybe
Public interface Maybe
Public static Maybe
Private static Maybe Bind ( Maybe this m, Func > f)
{
var some = m as Something ;
return ( some == null )?
Nothing new (): f ( some.Value );
}
public static Maybe SelectMany ( Maybe this m, Func > f)
{
return Bind (m, f);
}
public static Maybe
Var r = from x in 5.ToMaybe () / / Something
Operators in LINQ can be extended by an appropriate extension method is provided. This also standard operators can be overwritten.
Public static class Extensions person
{
public static IEnumerable
The write your own LINQ provider offers, if a service is to be invoked, which requires a specific syntax (SQL, XML, etc. ). Must be implemented to make this possible the IEnumerable Interface. Via this interface, the LINQ expression tree can be analyzed and converted into the appropriate target format.
Reactive Extensions
Reactive Extensions (short: Rx ) is an extension of LINQ, which works rather than on IEnumerable to IObservable
Examples
LINQ to DataSet
The following example shows the query a table with Linq. It is assumed an existing Access database under the path: C: \ database.mdb with a Products table that contains the fields ID, Name and EANCode.
The table is modeled as a class and provided by attributes with metadata that describes the mapping to the database.
These must be added in the solution under References A reference to the System.Data.Linq.dll.
Using System.Data.Linq.Mapping; [Table ( Name = " Products ")] class Product { [ Column ( Name = " id", IsPrimaryKey = true)] public int ID; [ Column ( Name = "Name") ] public string Name; [ Column ( Name = " Ean ")] public string EANCode; } Now you can query the table. In the following example, all the products are listed, the product name starts with an A. The products are sorted by their ID.
Using System.Configuration;
/ / For Configuration Manager
using System.Data;
/ / For all interface types
using System.Data.Common;
/ / For DbProviderFactories
class Foo ()
{
public static void Main () {
/ / Get connection settings from app.config
var cs = ConfigurationManager.ConnectionStrings [" MyConnectionString "];
var factory = DbProviderFactories.GetFactory ( cs.ProviderName );
using ( IDbConnection connection = new factory.CreateConnection ( cs.ConnectionString ) )
{
connection.open ();
DataContext db = new DataContext ( connectionString );
Table table =
Var query = products . Where (p => p.Name.StartsWith ( "A") ) . OrderBy (p => p.ID ); foreach (var product in query) { Console.WriteLine ( product.Name ); } With the function of single a single record can be determined. The following example provides the record with ID 1
Console.WriteLine (. Products.Single (p => p.ID == 1) name); However, if the query returns multiple records, an InvalidOperationException is thrown.
LINQ to XML
Below is an example showing how LINQ can be used to read information from an XML file. As XML file, the following XML is sample file.
xml version = " 1.0"? >
<-! Purchase_order.xml ->
XElement purchaseOrder = XElement.Load ( " purchase_order.xml ");
XElement purchaseOrder = XElement.Load ( " purchase_order.xml ");
LINQ can simplify the use of the Reactive Extensions ( Rx) significantly. Consider the following program:
Class FizzBuzz: IObservable
Class Program { static string IntToFizzBuzz (int n ) { return new StringBuilder ( 8) . Append (? ( N% 2 == 0) " Fizz ": String.Empty ) . Append ( ( n% 5 == 0) " Buzz ": String.Empty ) . Append ( ( n% 2 = 0 && n% 5 = 0) n.ToString ():! ? String.Empty ) . ToString (); } static void Main ( string [ ] args) { var = System.Reactive.Linq.Observable.Range numbers (1, 100); var FizzBuzz = from n in numbers select IntToFizzBuzz (s ); fizzBuzz.Subscribe ( Console.WriteLine ); / / or: fizzBuzz.Subscribe ( result = > Console.WriteLine (result) ); } } literature
- Ozgur Aytekin: LINQ - theory and practice for beginners. Addison -Wesley, München 2008, ISBN 9,783,827,326,164th
- Andreas Kühnel: Visual C # 2010 Galileo Press, Bonn 2010, ISBN 9783836215527, LINQ to Objects, pp. 465-496. .
- Paolo Pialorsi, Marco Russo: Database Programming with Microsoft LINQ. Microsoft Press Germany, Unterschleissheim 2008, ISBN 9,783,866,454,286th
- Paolo Pialorsi, Marco Russo:. Programming Microsoft LINQ in Microsoft NET Framework 4 Microsoft Press, Sebastopol California, 2010 ISBN 9,780,735,640,573th
- Paolo Pialorsi: Developer book Microsoft SharePoint 2010 Microsoft Press Germany, Unterschleissheim 2011, ISBN 9783866455450, LINQ to SharePoint, pp. 113-188. .
- Jesse Liberty, Paul Betts: Programming Reactive Extensions and LINQ. Apress 19 October, 2011, ISBN 9781430237471, p 184