Home > SKIP > Visual Studio .Net > Using LINQ

Using LINQ (Language Integrated Query)

» Click here to download sample code

What is LINQ

  • Language Integrated Query (LINQ) is Microsoft's new technology for powerful, general purpose data access. This technology provides a fully-integrated query language, available in both C# 3.0 and VB 9.0, for high-level data access against objects, relational databases, and XML documents.
  • (LINQ), which unifies the way data can be retrieved in .NET. LINQ provides a uniform way to retrieve data from any object that implements the IEnumerable<T> interface.
    With LINQ, arrays, collections, relational data, and XML are all potential data sources.

Why LINQ?

With LINQ, we can use the same syntax to retrieve data from any data source:

E.g.: var query = from e in employees
where e.id == 1
select e.name;

What We Need to Use LINQ

LINQ is a combination of extensions to .NET languages and class libraries that support them. To use it, we’ll need the following:

Obviously LINQ, which is available from the new Microsoft .NET
Framework 3.5 that you can download at http://go.microsoft.com/?linkid=7755937.

We can speed up our application development time with LINQ using. Visual Studio 2008, which offers visual tools such as LINQ to SQL designer and the Intellisense support with LINQ’s syntax. We can obtain a 90-day trial version of Visual Studio 2008 at http://msdn2.microsoft.com/enus/vstudio/products/aa700831.aspx.

Optionally, we can download the Visual C# 2008 Expression Edition tool at

www.microsoft.com/vstudio/express/download.

It is the free edition of Visual Studio 2008 and offers a lot of LINQ support such as Intellisense and LINQ to SQL designer. To use LINQ to ADO.NET, you need SQL Server 2005, SQL Server 2005 Express Edition, or SQL Server 2000.

The LINQ Architecture

The LINQ Architecture
The LINQ Project

The middle level represents the three main parts of the LINQ project:

LINQ to Objects is an API that provides methods that represent a set of standard query operators (SQOs) to retrieve data from any object whose class implements the IEnumerable<T> interface. These queries are performed against in-memory data.

LINQ to ADO.NET augments SQOs to work against relational data. It is composed of three parts (which appear at the bottom level of Figure 1):

LINQ to SQL (formerly DLinq) is use to query relational databases such as Microsoft SQL Server. LINQ to DataSet supports queries by using ADO.NET data sets and data tables. LINQ to Entities is a Microsoft ORM solution, allowing developers to use Entities (an ADO.NET 3.0 feature) to declaratively specify the structure of business objects and use LINQ to query them.

LINQ to XML (formerly XLinq) not only augments SQOs but also includes a host of XML-specific features for XML document creation and queries.

Supporting LINQ in C# 3.0

  • Generics
  • Delegates
  • Anonymous Methods
  • Lambda Expressions

Lambda Expressions

lambda expressions in C# 3.0 are a straightforward simplification of anonymous methods. Whereas an anonymous method is an unnamed block of code, a lambda expression is an unnamed expression that evaluates to a single value. Given a value x and an expression f(x) to evaluate, the corresponding lambda expression is written

x => f(x)

For example,

List<Doctor> inchicago = doctors.FindAll(x => x.City == "Chicago");

In this case, x is a doctor, and f(x) is the expression
x.City == "Chicago", which evaluates to true or false.

Lambda expressions are equivalent to anonymous methods that return a value when invoked, and are thus interchangeable. In fact, the compiler translates lambda expressions into delegate-base code, exactly as it does for anonymous methods.

Type Inference

Notice that the lambda expression
d => d.City == "Chicago"

does not specify a type for d. Without a type, the compiler cannot translate the lambda expression into the equivalent anonymous method:

delegate(?????? d) // what type is the argument d?

{
return d.City == "Chicago";
}

C# 3.0 is actually inferring the type of d in the lambda expression, based on contextual information.

For example, since doctors is of type List<Doctor>, the compiler can prove that in the context of calling FindAll
doctors.FindAll(d => d.City == "Chicago") d must be of type Doctor.

While developers will find type inference useful in isolation, the real motivation is LINQ. Type inference is critical to the success of LINQ since queries can yield complex results. LINQ would be far less attractive if developers had to explicitly type all aspects of their queries. In fact, specifying a type is sometimes impossible, e.g., when projections select new patterns of data:

var query = from d in doctors
where d.City == "Chicago"
select new { d.GivenFirstName, d.FamilyLastName }; // type?

foreach (var r in query)
System.Console.WriteLine("{0}, {1}", r.FamilyLastName, r.GivenFirstName);

In these cases, typing is better left to the compiler.

Anonymous Types and Object Initializers

var query = from d in doctors
where d.City == "Chicago"
select new { d.GivenFirstName, d.FamilyLastName };

Query Expressions

A LINQ query is called a query expression. Query expressions start with the keyword from, and are written using SQL-like query operators such as Select, Where, and OrderBy:

using System.Query;
var chicago = from d in doctors
where d.City == "Chicago"
orderby d.FamilyLastName, d.GivenFirstName
select d;

foreach (var r in chicago)
System.Console.WriteLine("{0}, {1}", r.FamilyLastName, r.GivenFirstName);

What exactly is a query expression?

  • In SQL, a select query is a declarative statement that operates on one or more tables, producing a table.
  • In LINQ, a query expression is a declarative expression operating on one or more IEnumerable objects, returning an IEnumerable object.
  • Thus, a query expression is an expression of iteration across one or more objects, producing an object over which you iterate to collect the result.

LINQ to DataSets

  • LINQ supports the querying of both typed and untyped DataSets.
LINQ to DataSets
  • Let's assume an instance of SchedulingDocs has been created and filled:
    DataSets.SchedulingDocs ds = new DataSets.SchedulingDocs();
    // create dataset . . // open connection to a database and fill each table?

  • To find all the doctors living within Chicago, the query is exactly as we've seen before:

    var chicago = from d in ds.Doctors
    where d.City == "Chicago"
    select d;

LINQ supports the notion of joins, including inner and outer joins. For example, let's join the Doctors and Calls tables to see which doctors are scheduled in the month of October 2006:

var oct2006 = (
from d in ds.Doctors
join c in ds.Calls on d.Initials equals c.Initials
where c.DateOfCall >= new DateTime(2006, 10, 1) && c.DateOfCall <= new DateTime(2006, 10, 31)
orderby d.Initials select d.Initials
) .Distinct();

Now grouping the results:

var oct2006 = from d in ds.Doctors
join c in ds.Calls on d.Initials equals c.Initials
where c.DateOfCall >= new DateTime(2006, 10, 1) && c.DateOfCall <= new DateTime(2006, 10, 31)
group c by d.Initials into g
select g;

LINQ to SQL

  • Instead of executing our queries against a DataSet, suppose we want to execute against the database directly?
  • No problem.
  • With LINQ to SQL, we change only the target of our query.

Databases.SchedulingDocs db = new Databases.SchedulingDocs(connectionInfo);

var oct2006 = (
from d in db.Doctors
join c in db.Calls on d.Initials equals c.Initials where c.DateOfCall >= new DateTime(2006, 10, 1) && c.DateOfCall <= new DateTime(2006, 10, 31) orderby d.Initials
select d.Initials
) .Distinct();

Let's look at a more complex query that computes the number of calls for every doctor in the month of October 2006:

var oct2006 = from d in db.Doctors
join c in db.Calls on d.Initials equals c.Initials where c.DateOfCall >= new DateTime(2006, 10, 1) && c.DateOfCall <= new DateTime(2006, 10, 31) group c by d.Initials into g
select new { Initials = g.Key, Count = g.Count() };

An outer join is needed to capture the results for all doctors, whether scheduled or not. Outer joins are based on LINQ's join ... into syntax:

var allOct2006 = from d1 in db.Doctors
join d2 in oct2006
on d1.Initials equals d2.Initials into j
from r in j.DefaultIfEmpty()
select new { Initials = d1.Initials, Count = (r == null ? 0 : r.Count) };

Create, Read, Update, and Delete with LINQ

LINQ provides full support for read/write data access, commonly referred to as CRUD.

While data is read using an SQL-like query language, data modification is approached using more traditional, object-oriented mechanisms.

For example, to schedule the doctor mbl for call on November 30, 2006 in our SchedulingDocs database, first we add a new row to the object representing the Calls table:

db.Calls.Add( new Databases.Calls{Initials="mbl", DateOfCall=new DateTime(2006, 11, 30} );

Second, we flush the change back to the database:

db.SubmitChanges();

To delete a call, we find the corresponding object, remove it from the table, and update the database:

var del = from c in db.Calls
where c.Initials == "mbl" && c.DateOfCall == new DateTime(2006, 11, 30)
select c;

foreach (var c in del)
db.Calls.Remove(c);

db.SubmitChanges();

Finally, to update existing data, the approach is (1) query to find the corresponding objects, (2) update those objects, and (3) flush the changes. For example, if the doctor ay's pager number changes to 53301, we update the database as follows:

var ay = db.Doctors.Single( d => d.Initials == "ay" );
ay.PagerNumber = 53301;
db.SubmitChanges();

The same logic applies to other LINQ scenarios, such as XML documents and DataSets. For example, with the typed DataSet SchedulingDocs, scheduling a doctor on call is simply a matter of adding a new row to the Calls table:

ds.Calls.AddCallsRow( "mbl", new DateTime(2006, 11, 30) );

Much like the database objects, DataSets are a local, in-memory collection of objects. To persist your changes, an updated DataSet must be written to some durable medium, such as the file system or a database:

dbConn.Open();
callsAdapter.Update( ds.Calls );
dbConn.Close();

Here we re-open the connection, update the database to match, and close the connection the equivalent of db.SubmitChanges().

The key difference is that in the case of LINQ to SQL, the SQLMetal tool generates the necessary code to update the underlying database. In the case of DataSets and XML documents (and other flavors of LINQ), it's typically our responsibility to load the data, and consequently to persist it back.

LINQ to XML

LINQ was designed to manipulate XML data as easily as it manipulates relational data. LINQ to XML represents a new API for XML-based development, equivalent in power to XPath and XQuery yet far simpler for most developers to use.

Let's assume the data source for our hospital scheduling application is an XML document stored in the file SchedulingDocs.xml.

<?xml version="1.0" standalone="yes"?>
<SchedulingDocs>
<Calls>
<Call>
<Initials>mbl</Initials>
<DateOfCall>2006-10-01T00:00:00-05:00</DateOfCall>
</Call>
.. .
</Calls>
<Doctors>
<Doctor>
<Initials>ay</Initials>
<GivenFirstName>Amy</GivenFirstName>
<FamilyLastName>Yang</FamilyLastName>
<PagerNumber>53300</PagerNumber>
<EmailAddress>ayang@uhospital.edu</EmailAddress>
<StreetAddress>1400 Ridge Ave.</StreetAddress>
<City>Evanston</City>
</Doctor>
. . .
</Doctors>
<Vacations>
<Vacation>
<Initials>jl</Initials>
<DateOfDayOff>2006-10-03T00:00:00-05:00</DateOfDayOff>
</Vacation>
. . .
</Vacations>
</SchedulingDocs>

Using LINQ, we load this document as follows:

import System.Xml.XLinq;
// LINQ to XML XElement root, calls, doctors, vacations;
root = XElement.Load("SchedulingDocs.xml");
calls = root.Element("Calls");
doctors = root.Element("Doctors");
vacations = root.Element("Vacations");

To select all the doctors, it's a simple query expression:

var docs = from doc in doctors.Elements()
select doc;

And to find just those doctors living in Chicago:

var chicago = from doc in doctors.Elements()
where doc.Element("City").Value == "Chicago"
orderby doc.Element("Initials").Value
select doc;

LINQ to IEnumerable

One of the elegant design aspects of LINQ is that queries can be executed against any enumerable data source. If an object implements IEnumerable, then LINQ can access the data behind that object.

For example, suppose we need to search the current user's My Documents folder (and sub-folders) for all non-system files modified in the last hour. Using LINQ we do this as follows:

using SIO = System.IO;
string[] files;
string mydocs; mydocs = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
files = SIO.Directory.GetFiles(mydocs, "*.*", SIO.SearchOption.AllDirectories);
var query = from file in files
let lasthour = DateTime.Now.Subtract(new TimeSpan(0, 1, 0, 0)) where SIO.File.GetLastAccessTime(file) >= lasthour && (SIO.File.GetAttributes(file) & SIO.FileAttributes.System) == 0
select file;

Notice the presence of the let statement, which allows for the definition of values local to the query; let is used to improve readability and efficiency by factoring out common operations.

This example should not be very surprising, since what we are really doing is iterating across an array of filenames ("files"), not the file system itself. But this is a design artifact of the .NET Framework, not a limitation of LINQ. A similar example is the searching of a file, which is easily done in LINQ by iterating across the lines of the file:

string filename = ...; // file to search
var lines = from line in SIO.File.ReadAllLines(filename)
where line.Contains("class")
select line;

Standard LINQ Query Operators

LINQ provides a wide-range of query operators, many of which have been demonstrated in the previous sections.

The purpose of this section is to succinctly summarize the complete set of LINQ query operators.
We must import the

System.Query

namespace to use these operators.

Standard LINQ Query Operators

  • Aggregate
  • The Aggregate operator applies a function over a sequence, with or without an initial seed value. The result can be post-processed by another function is desired.
    int[] ints = { 1, 2, 3, 4, 5, 6 };
    var query = from ...;
    int sum = ints.Aggregate( (a, b) => a + b );
    Console.WriteLine(sum); // 21

» Click here to see Standard LINQ Query Operators

References

The entire tutorial and examples has been developed with help of following articles:

http://weblogs.asp.net/scottgu/archive/2006/05/14/Using-LINQ-with-ASP.NET-_2800_Part-1_2900_.aspx
http://www.asp.net/LEARN/linq-videos/
http://www.dotnetbips.com/articles/56f8f29d-2617-4f99-a8b4-977703ebf780.aspx
http://community.bartdesmet.net/blogs/bart/archive/2007/04/05/the-iqueryable-tales-linq-to-ldap-part-0.aspx
http://en.csharp-online.net/Introducing_LINQ%E2%80%94What_Is_LINQ

» Click here to download sample code