Language-Integrated Query
(LINQ) is a set of features introduced in Visual Studio 2012 that extends
powerful query capabilities to the language syntax of C# and Visual Basic. LINQ
introduces standard, easily-learned patterns for querying and updating data,
and the technology can be extended to support potentially any kind of data
store. Visual Studio includes LINQ provider assemblies that enable
the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET
Datasets, and XML documents.
Language-Integrated Query
(LINQ) is an innovation introduced in Visual Studio 2012 and .NET Framework
version 3.5 that bridges the gap between the world of objects and the world of
data.
In Visual Studio you can write LINQ queries in Visual Basic
or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any
collection of objects that supports IEnumerable or
the generic IEnumerable
All
LINQ query operations consist of three distinct actions:
- Obtain the data source.
- Create the query.
- Execute the query.
The following example shows how the three parts
of a query operation are expressed in source code.
C#
class
IntroToLINQ
{
static void Main()
{
// The Three Parts of a LINQ
Query:
//
1. Data source.
int[] numbers = new int[7] { 0, 1,
2, 3, 4, 5, 6 };
// 2. Query creation.
// numQuery is an
IEnumerable
var numQuery =
from num in numbers
where (num % 2) == 0
select num;
// 3. Query execution.
foreach (int num in numQuery)
{
Console.Write("{0,1} ",
num);
}
}
}
The
following illustration shows the complete query operation. In LINQ the
execution of the query is distinct from the query itself; in other words you
have not retrieved any data just by creating a query variable.
Then read the documentation for the LINQ technology in
which you are interested:
·
SQL Server databases: LINQ to SQL
·
XML documents: LINQ
to XML
·
ADO .NET
Datasets: LINQ to DataSet
·
.NET collections, files, strings and so on: LINQ to Objects
LINQ to SQL
·
LINQ to
SQL is considered to be one of Microsoft's new ORM products. So before we start
explaining LINQ to SQL, let us first understand what ORM is.
·
ORM
stands for Object-Relational Mapping. Sometimes it is called O/RM, or O/R
mapping. It is a programming technique that contains a set of classes that map
relational database entities to objects in a specific programming language.
·
The
following diagram shows the three different ways of accessing a database from
an application. There are some other mechanisms to access a database from an
application, such as JDBC and ADO.NET. However, to keep the diagram simple,
they have not been shown here.
LINQ to SQL is a component of the .NET Framework version 3.5
that provides a run-time infrastructure for managing relational data as
objects.
In LINQ to SQL, the data model of a relational database is
mapped to an object model expressed in the programming language of the
developer. When the application runs, LINQ to SQL translates the
language-integrated queries in the object model into SQL and sends them to the
database for execution. When the database returns the results, LINQ to SQL
translates them back to objects that you can work with in your own programming
language.
LINQ to SQL fully supports transactions, views, Stored
Procedures, and user-defined functions. It also provides an easy way to
integrate data validation and business logic rules into your data model, and
supports single table inheritance in the object model.
Modeling Databases Using LINQ
to SQL:
Visual Studio "Orcas" ships with a LINQ to SQL designer that
provides an easy way to model and visualize a database as a LINQ to SQL object
model.
LINQ to SQL Code Examples
Once we've modeled our database using the LINQ to SQL designer, we can
then easily write code to work against it. Below are a few code examples
that show off common data tasks:
1) Query
Products From the Database
The code below uses LINQ query syntax to retrieve an IEnumerable
sequence of Product objects. Note how the code is querying across the
Product/Category relationship to only retrieve those products in the
"Beverages" category:
C#:
2) Update a
Product in the Database
The code below demonstrates how to retrieve a single product from the
database, update its price, and then save the changes back to the database:
C#:
3) Insert a
New Category and Two New Products into the Database
The code below demonstrates how to create a new category, and then
create two new products and associate them with the category. All three
are then saved into the database.
Note below how I don't need to manually manage the primary key/foreign
key relationships. Instead, just by adding the Product objects into
the category's "Products" collection, and then by adding the Category
object into the DataContext's "Categories" collection, LINQ to
SQL will know to automatically persist the appropriate PK/FK relationships for
me.
C#
4) Delete
Products from the Database
The code below demonstrates how to delete all Toy products from the
database:
C#:
5) Call a
Stored Procedure
The code below demonstrates how to retrieve Product entities not using
LINQ query syntax, but rather by calling the
"GetProductsByCategory" stored procedure we added to our data model
above. Note that once I retrieve the Product results, I can update/delete
them and then call db.SubmitChanges() to persist the modifications back to the
database.
C#:
6) Retrieve
Products with Server Side Paging
The code below demonstrates how to implement efficient server-side
database paging as part of a LINQ query. By using the Skip() and Take()
operators below, we'll only return 10 rows from the database - starting with
row 200.
C#:
XML documents: LINQ to XML
The LINQ to XML provider converts an XML document
to a collection of
XElement objects, which are then queried against using the local
execution engine that is provided as a part of the implementation of the
standard query operator.
using
System;
using System.Collections.Generic;
using
System.Linq;
using
System.Xml.Linq;
namespace
UsingXML
{
//
We establish a generic Person Class, and define necessary methods
class
PersonObject
{
public
string fname { get; set; }
public
string lname { get; set; }
public
int age { get; set; }
public
char gender { get; set; }
public
PersonObject()
{
this.fname
= null;
this.lname
= null;
this.age
= 0;
this.gender
= '0';
}
public
PersonObject(string f, string l, int a, char g)
{
this.fname
= f;
this.lname
= l;
this.age
= a;
this.gender
= g;
}
}
//
Define a new List, to store the objects we pull out of the XML
List
PersonList = new List();
//
Build a LINQ query, and run through the XML building the PersonObjects
var
query = from xml in XDoc.Descendants("Person")
select
new PersonObject
{
fname
= (string)xml.Element("FirstName"),
lname
= (string)xml.Element("LastName"),
age
= (int)xml.Element("Age"),
gender
= ((string)xml.Element("Gender") == "M" ?
'M'
:
'F')
};
PersonList
= query.ToList();
LINQ
to DataSet
A query is an expression that
retrieves data from a data source. Queries are usually expressed in a
specialized query language, such as SQL for relational databases and XQuery for
XML. Therefore, developers have had to learn a new query language for each type
of data source or data format that they query. Language-Integrated Query (LINQ)
offers a simpler, consistent model for working with data across various kinds
of data sources and formats. In a LINQ query, you always work with programming
objects.
The following example uses Select to
return all the rows from Product table and display the product names.
C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable query =
from product in products.AsEnumerable()
select product;
Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field<string>("Name"));
}
LINQ to Objects
LINQ to Objects
allows .NET developers to write “queries” over collections of objects. Out of
the box there is a large set of query operators that provide a similar depth of
functionality to what we expect from any SQL language working with a relational
database, and if what we need isn’t present out-of-the-box, we can add our own.
Traditionally, working with collections of objects meant writing a lot of
looping code using for loops or foreach loops to iterate through a list
carrying out filtering using if statements, and some action like keeping a
running sum of a total property. LINQ frees you from having to write looping
code; it allows you
to write queries that filter a list or calculate aggregate functions on elements
in a collection as a set.
int[] nums = new int[] {0,4,2,6,3,8,3,1};
var result = from n in nums
where n < 5
orderby n
select n;
foreach(int
i in result)
Console.WriteLine(i);
Output:
0
1
2
3
3
4
Reach us At: - 0120-4029000 / 24 / 25 / 27 / 29 Mbl: 9953584548
Write us at: - Smruti@apextgi.com and pratap@apextgi.com
No comments:
Post a Comment