Note the following about the LINQ to XML class hierarchy:
Currently there is a complexity of accessing and integrating
information that is not natively defined using OO technology. The two most
common sources of non-OO information are relational databases and XML. The
purpose of LINQ is therefore, to add general-purpose query facilities to
the .NET Framework that apply to all sources of information, not just relational
or XML data. This facility is called .NET Language-Integrated
Query (LINQ).
.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.
The .NET standard query operators
allow queries to be applied to any IEnumerable<T>-based
information source.
The .NET standard query operators are defined as extension methods in the type System.Linq.Enumerable. When examining the standard query operators, you'll notice that all but a few of them are defined in terms of the IEnumerable<T> interface. This means that every IEnumerable<T>-compatible information source gets the standard query operators simply by adding the following using statement in C#:
using System.Linq; // makes query operators visible
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. The following example illustrates basic LINQ functionalities:
public void Tests_Basics()
{
// Create an array of strings. Note use of collection initializer
string[] technologies = { "ADO.NET", "WCF", "WPF", "WWF", "LINQ" };
/* LINQ - declarative query */
// Initialize the local variable "query" with a query expression.
IEnumerable<string> query1 = from tech in technologies where tech.StartsWith("W") select tech;
// Iterate over results of query1
foreach (string technology in query1) Trace.WriteLine(technology);
/* LINQ - method-based query */
// Query expressions are convenient DECLARATIVE shorthand over code you could write manually.
// The query expression above is semantically identical to the statement below. This form of
// query is called a method-based query. The arguments to the Where, OrderBy, and Select
// operators are called lambda expressions, which are fragments of code much like delegates
IEnumerable<string> query2 = technologies.Where(tech => tech.StartsWith("W")).Select(tech => tech);
// Iterate over results of query2
foreach (string technology in query2) Trace.WriteLine(technology);
/* Other approaches */
Func<string, bool> filter = delegate(string s)
{
return s.StartsWith("W");
};
Func<string, string> select = delegate(string s)
{
return s;
};
IEnumerable<string> query3 = technologies.Where(filter).Select(select);
// Iterate over results of query3
foreach (string technology in query3) Trace.WriteLine(technology);
/* OBJECT INITIALIZERS */
// The following query creates a new Technology object for each value in the input sequence:
IEnumerable<Technology> query4 = technologies.Select(tech => new Technology { Description = tech, ReleaseDate = DateTime.MinValue });
foreach (Technology tech in query4) Trace.WriteLine("Desc: " + tech.Description + "/Release Date: " + tech.ReleaseDate);
}
// a helper class to illustrate how object initializers can be used to create new objects
internal class Technology
{
private DateTime _dtRelease;
private string _desc;
public string Description
{
get { return _desc; }
set { _desc = value; }
}
public DateTime ReleaseDate
{
get { return _dtRelease; }
set { _dtRelease = value; }
}
}
Expression trees are efficient in-memory data representations of lambda
expressions and make the structure of the expression transparent and explicit.
The namespace System.Linq.Expressions defines a
distinguished generic type, Expression<T>, which
indicates that an expression tree is desired for a given lambda expression
rather than a traditional IL-based method body.
The determination of whether the compiler will emit executable IL or an expression tree is determined by how the lambda expression is used. When a lambda expression is assigned to a variable, field, or parameter whose type is a delegate, the compiler emits IL that is identical to that of an anonymous method. When a lambda expression is assigned to a variable, field, or parameter whose type is Expression<T> for some delegate type T, the compiler emits an expression tree instead. The following example illustrates:
public void Test_ExpressionTrees()
{
// Create an executable lambda expression
System.Func<string, int> f = name => name.Length;
int nLength = f("Yazan"); // returns 5
// Create a non-executable lambda expression
System.Linq.Expressions.Expression<Func<string, int>> e = name => name.Length;
//int nLength2 = e("Yazan"); // error CS0118: 'e' is a 'variable' but is used like a 'method'
// Since 'e' is an expression tree, we can interact with it like any other data structure
Trace.WriteLine(e.Body); // name.Length
}
The standard query operators are defined as extension methods in the type System.Linq.Enumerable. Extension methods are given the lowest priority in terms of resolution and are only used if there is no suitable match on the target type and its base types. This allows user-defined types to provide their own query operators that take precedence over the standard operators. The following example illustrates:
public void Test_ExtensionMethods()
{
// Create and initialize the object
MySequence seq = new MySequence();
// Iterate over the object (illustrates the implemenation of an iterator with 'yield return')
// Prints all numbers from 1 to 10
foreach (int i in seq)
Trace.WriteLine(i);
// Now use our own implementation of the 'Where' standard query operator, and not the extension
// method, as instance methods take precedence over extension methods
// Prints all numbers from 6 to 10
foreach ( int number in seq.Where(item => item > 5))
Trace.WriteLine( number);
}
// Helper class that implements an iterator to show that standard-query operators
// are implemented as extension methods, and as such, have lower precedence than
// instance methods
internal class MySequence : IEnumerable
{
// Internal collection
private List<int> _lstData = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
// Implement an iterator. Recall that an iterator allows you to support foreach
// iteration without having to implement IEnumerable on the containing class.
// The iterator must be called GetEnumerator if you want to use MySequence directly
// in a foreach statement, and the iterator must use 'yield return' statement
// to return each successive element:
// MySequence se = new MySequence();
// foreach (int n in seq) // Calls seq.GetEnumerator for each pass
// Trace.WriteLine( n )
public IEnumerator<int> GetEnumerator()
{
for (int i = 0; i < _lstData.Count; i++)
yield return _lstData[i];
}
IEnumerator IEnumerable.GetEnumerator()
{
return this.GetEnumerator();
}
// Provide our own implementation of the Where standard query operator
public IEnumerable<int> Where(Func<int, bool> selector)
{
for (int i = 0; i < _lstData.Count; i++)
{
// Return this item if it passes the selector function
if (selector(_lstData[i] ))
yield return _lstData[i];
}
}
}
The OfType operator is one of the few standard query operators that doesn't extend an IEnumerable<T>-based information source. OfType accepts not only IEnumerable<T>-based sources, but also sources that are written against the non-parameterized IEnumerable interface that was present in version 1.0 of the .NET Framework. Typically you use OfType to convert a collection that was written against the non-parameterized IEnumerable (such as ArrayList) to an IEnumerable<T>-based collection that can then be used with LINQ operators. The following example illustrates:
public void Test_OfType()
{
// Say we have an 'old-style' collection from the old days of .NET 1.0
ArrayList alDoesNotSupportLINQ = new ArrayList { 1, 2, 3, 4 };
// We cannot use 'alDoesNotSupportLINQ' with LINQ operators. Use OfType operator to
// convert 'alDoesNotSupportLINQ' to an IEnumerable<T>-based collection
IEnumerable<int> alSupportsLINQ = alDoesNotSupportLINQ.OfType<int>();
// The new collection now supports LINQ operators. The following prints
// only even numbers, 2 and 4
foreach (int i in alSupportsLINQ.Where(item => (item % 2) == 0))
Trace.WriteLine(i);
// The OfType operator is also useful for extracts a specific type from a heterogeneous array.
// OfType simply omits members of the original sequence that are not compatible with the given
// type argument. For example, the following OfType extracts only integers:
object[] aHetergenous = { 1, "Two", 3.0, 4, "Five", 6.0 };
IEnumerable<int> aIntegersOnly = aHetergenous.OfType<int>();
foreach (int i in aIntegersOnly) Trace.WriteLine(i); // Writes 1 and 4 only
}
All standard query operator are implemented using the yield construct. This implementation technique is common for all the standard operators that return sequences of values. The use of yield has an interesting benefit which is that the query is not actually evaluated until it is iterated over. This deferred evaluation allows queries to be kept as IEnumerable<T>-based values that can be evaluated multiple times, each time yielding potentially different results.
To indicate that a cached copy of the results is needed, we can simply append a ToList or ToArray operator to the query like this:
IEnumerable<int> q = lstData.Where(item => item > 3).ToArray();
Both ToArray and ToList force immediate query evaluation. The same is true for the standard query operators that return singleton values (for example: First, ElementAt, Sum, Average, All, Any). The following example illustrates:
public void Test_DeferredQueryEvaluation()
{
/* NON-CACHED QUERY */
// Create and initialize a test list
List<int> lstData = new List<int> { 1, 2, 3, 4, 5 };
// Declare a query that selects all values greater than 3. Then iterate over the
// results of the query. Note that output is 4 and 5
IEnumerable<int> query1 = lstData.Where(item => item > 3);
foreach (int n in query1) Trace.WriteLine(n); // 4 and 5
// Now Append lstData with more info. And iterate over it. Note that iteration
// over query1 results shows the new values. This effectively means that the
// the query is not actually evaluated until it is iterated over.
lstData.Add(6);
lstData.Add(7);
lstData.Add(8);
foreach (int n in query1) Trace.WriteLine(n); // 4,5,6,7,8
/* CACHED QUERY */
// Create and initialize a test list
List<int> lstData2 = new List<int> { 1, 2, 3, 4, 5 };
// Declare a query that selects all values greater than 3. Note the use of ToArray().
// Then iterate over the results of the query. Note that output is 4 and 5
IEnumerable<int> query2 = lstData2.Where(item => item > 3).ToArray();
foreach (int n in query2) Trace.WriteLine(n); // 4 and 5
// Now Append lstData with more info. And iterate over it. Note that iteration
// over query2 results does not show new values. This effectively means that the
// the query was evaluated and cached.
lstData2.Add(6);
lstData2.Add(7);
lstData2.Add(8);
foreach (int n in query2) Trace.WriteLine(n); // 4 and 5
}
The evaluation of a query results in a sequence of values that are
produced in some order that is intrinsic in the underlying information sources.
To give explicit control over the order in which these values are produced,
standard query operators are defined for controlling the order. The most basic
of these operators is the OrderBy operator. The
following example illustrates:
public void TestSortingAndGrouping()
{
// Test collection
string[] Alphabet = { "A", "B", "C", "D", "E", "F", };
// General sort
IOrderedEnumerable<string> order1 = Alphabet.OrderBy((string letter) => letter);
WriteCollection(order1); // A, B, C, D, E, F
// Descending sort order
IOrderedEnumerable<string> order2 = Alphabet.OrderByDescending((string letter) => letter);
WriteCollection(order2); // F, E, D, C, B, A
// To allow multiple sort criteria, both OrderBy and OrderByDescending return
// OrderedSequence<T> rather than the generic IEnumerable<T>. Two operators are
// defined only on OrderedSequence<T>, namely ThenBy and ThenByDescending which
// apply an additional (subordinate) sort criterion. ThenBy/ThenByDescending
// themselves return OrderedSequence<T>, allowing any number of ThenBy/ThenByDescending
// operators to be applied
List<Person> lstPersons = new List<Person> {
new Person {ID=1, Name="A"},
new Person {ID=1, Name="B"},
new Person {ID=1, Name="C"},
new Person {ID=2, Name="A"},
new Person {ID=2, Name="B"},
new Person {ID=2, Name="C"}};
IOrderedEnumerable<Person> order3 = lstPersons.OrderBy((Person person) => person.Name).ThenBy((Person person) => person.ID);
WriteCollection(order3); // A/1, A/2, B/1, B/2, C/1, C/2
// LINQ also include the GroupBy operator, which imposes a partitioning over a
// sequence of values based on a key extraction function. The GroupBy operator
// returns a sequence of IGrouping values, one for each distinct key value that
// was encountered. An IGrouping is an IEnumerable that additionally contains the
// key that was used to extract its contents. Note how the foreach statement
// prints data in each group
IEnumerable<IGrouping<int, Person>> group1 = lstPersons.GroupBy((Person person) => person.ID);
// Output of the following foreach is:
// Group Key: 1
// Person: 1/A
// Person: 1/B
// Person: 1/C
// Group Key: 2
// Person: 2/A
// Person: 2/B
// Person: 2/C
foreach (IGrouping<int, Person> group in group1)
{
// Ouput information on the current group
Trace.WriteLine( "Group Key: " + group.Key);
foreach (Person p in group)
Trace.WriteLine("\tPerson: " + p.ID + "/" + p.Name);
}
}
// Helper method to write contents of a given colleciton
private void WriteCollection<T>(IEnumerable<T> collection)
{
bool bStart = true;
foreach (T item in collection)
{
if (!bStart) Trace.Write( ", ");
Trace.Write(item);
if (bStart == true) bStart = false;
}
Trace.WriteLine(System.Environment.NewLine);
}
// Helper class to illustrate sorting/grouping/etc.
internal class Person
{
// Data members
private int _id;
private string _name;
// Properties
public int ID
{
get { return _id; }
set { _id = value; }
}
public string Name
{
get { return _name; }
set {_name = value; }
}
public override string ToString()
{
return Name + "/" + ID;
}
}
Aggregation operators are used to aggregate a sequence of values
into a single value. The most general aggregation operator is
Aggregate, which makes it simple to perform a
calculation over a sequence of values. Aggregate
works by calling the lambda expression once for each member of the underlying
sequence. Each time Aggregate calls the lambda
expression, it passes both the member from the sequence and an aggregated value
(the initial value is the seed parameter to Aggregate).
The result of the lambda expression replaces the previous aggregated value, and
Aggregate returns the final result of the lambda
expression.
In addition to the general purpose Aggregate
operator, the standard query operators also include a general purpose
Count operator and four numeric aggregation
operators (Min, Max,
Sum, and Average). The
following example illustrates:
public void TestAggregating()
{
int[] numbers = {1,2,3,4,5,6,7};
int nSum = numbers.Sum( );
double dAvg = numbers.Average();
// Use of Aggregate to simulate sum
int nSum2 = numbers.Aggregate(0, (int sum, int nCurrent) => nCurrent + nSum);
int nSum3 = numbers.Aggregate(aggFunc);
}
private int aggFunc(int nSum, int nCurrent)
{
Trace.WriteLine(nCurrent + "/" + nCurrent);
return nSum + nCurrent;
}
Selecting items within a collection is achieved via the Select operator which produces one value for each value in the source sequence. The following example illustrates:
public void TestSelect()
{
// Test data
string[] technologies = { "WCF", "WPF", "WWF", "LINQ"};
// If the select function returns a value that is itself a sequence, it is up to
// the consumer to traverse the sub-sequences manually. The statements output
// Current string: WCF
// W C F
//
// Current string: WPF
// W P F
//
// Current string: WWF
// W W F
//
// Current string: LINQ
// L I N Q
//
IEnumerable<char[]> characters1 = technologies.Select((string input) => input.ToCharArray());
foreach (char[] chars in characters1)
{
Trace.WriteLine("Current string: " + new string(chars));
foreach (char c in chars)
Trace.Write(c + " ");
Trace.WriteLine(System.Environment.NewLine);
}
// The SelectMany operator works similarly to the Select operator. It differs in that the transform
// function is expected to return a sequence that is then expanded by the SelectMany operator
// The following statement outputs the following
// W C F W P F W W F L I N Q
IEnumerable<char> characters2 = technologies.SelectMany((string input) => input.ToCharArray());
foreach (char c in characters2)
Trace.Write(c + " ");
Trace.WriteLine(System.Environment.NewLine);
}
The concept of Join refers to the operation of bringing the elements of a sequence together with the elements they "match up with" from another sequence. A more powerful cousin of Join is the GroupJoin operator. GroupJoin differs from Join in the way the result-shaping lambda expression is used: Instead of being invoked with each individual pair of outer and inner elements, it will be called only once for each outer element, with a sequence of all of the inner elements that match that outer element. The following example illustrates:
public void TestJoin()
{
// JOIN
{
// Create two spearate sequences
Customer[] customers = { new Customer{ ID=1, Name="A"},
new Customer{ ID=2, Name="B"},
new Customer{ ID=3, Name="C"}
};
Order[] orders = { new Order {CustomerID=1, Details="Details1" },
new Order {CustomerID=2, Details="Details2" },
new Order {CustomerID=3, Details="Details3" },
};
// The join operator is best understood by using the declarative syntax first.
// Note that the join operator performs an inner join: given sequences s1 and s2,
// an inner join of s1 on s2, returns all elements in s2 whose id matches those
// in s1. In the code below, 'customers' collection is referred to as 'outer', while
// 'order' is referred to as 'inner'
// DECLARATIVE SYNTAX
// Output:
// 1/A/Details1
// 2/B/Details2
// 3/C/Details3
var customerOrders1 = from c in customers
join o in orders on c.ID equals o.CustomerID
select (new { c.Name, c.ID, o.Details });
foreach (var custorder in customerOrders1)
Trace.WriteLine(custorder.ID + "/" + custorder.Name + "/" + custorder.Details);
// METHOD CALL
// Output:
// 1/A/Details1
// 2/B/Details2
// 3/C/Details3
var customerOrders2 = customers.Join(orders,
(Customer c) => c.ID,
(Order o) => o.CustomerID,
(Customer c, Order o) => new { c.Name, c.ID, o.Details }
);
foreach (var custorder in customerOrders2)
Trace.WriteLine(custorder.ID + "/" + custorder.Name + "/" + custorder.Details);
}
// GROUPJOIN
{
// GroupJoin operator produces hirarchical data: outer elements paired with sequences
// of matching inner elements. It has no direct equivalent in traditional relational
// databases
// Create two spearate sequences. A customer may have many orders
Customer[] customers = { new Customer{ ID=1, Name="A"},
new Customer{ ID=2, Name="B"}};
Order[] orders = { new Order {CustomerID = 1, Details="Details1", OrderTotal=1.1},
new Order {CustomerID = 1, Details="Details2", OrderTotal=2.2},
new Order {CustomerID = 1, Details="Details3", OrderTotal=3.3 },
new Order {CustomerID = 2, Details="Details3", OrderTotal=4.4 },
new Order {CustomerID = 2, Details="Details3", OrderTotal=5.5 }
};
// Use join operator to group each customer with hir/her orders into co
// where co is an anonymous type. The following effecticely creates a collection
// where each entry in the collection contains the customers ID and the customers
// collection of orders (identified by co)
var customerToOrders = from c in customers
join o in orders on c.ID equals o.CustomerID into co
select (new { c.ID, Orders = co });
// Print orders for each customer. vCustomerOrders is an anonymous type
// containing an int property named ID and an enumerable property named Orders
// The following prints:
// Orders for customer : 1
// CustomerID: 1 / Details: Details1 / OrderTotal: 1.1
// CustomerID: 1 / Details: Details2 / OrderTotal: 2.2
// CustomerID: 1 / Details: Details3 / OrderTotal: 3.3
// Orders for customer : 2
// CustomerID: 2 / Details: Details3 / OrderTotal: 4.4
// CustomerID: 2 / Details: Details3 / OrderTotal: 5.5
foreach (var vCustomerOrders in customerToOrders)
{
Trace.WriteLine( "Orders for customer : " + vCustomerOrders.ID);
foreach (Order o in vCustomerOrders.Orders)
Trace.WriteLine(o.ToString());
}
// The following query is similar to the one above, except that it uses
// each customers Order collection to calculate the order total
var customerToOrders2 = from c in customers
join o in orders on c.ID equals o.CustomerID into co
select new { c.Name, Total = co.Sum( (Order o) => o.OrderTotal) };
// The following prints order totals for each customer:
// A/6.6
// B/9.9
foreach (var v in customerToOrders2)
Trace.WriteLine(v.Name + "/" + v.Total);
}
}
// Helper classs to illustrate joining/group joining
internal class Customer
{
// Data members
private int _id;
private string _name;
// Properties
public int ID
{
get { return _id; }
set { _id = value; }
}
public string Name
{
get { return _name; }
set { _name = value; }
}
public override string ToString()
{
return "Name: " + Name + "/ID: " + ID;
}
}
internal class Order
{
// Data members
private int _id;
private string _details;
private double _dOrderTotal;
// Properties
public int CustomerID
{
get { return _id; }
set { _id = value; }
}
public string Details
{
get { return _details; }
set { _details = value; }
}
public double OrderTotal
{
get { return _dOrderTotal; }
set { _dOrderTotal = value; }
}
// override to get string representation of the object
public override string ToString()
{
return "CustomerID: " + CustomerID + " / Details: " + Details + " / OrderTotal: " + OrderTotal;
}
}
Modern programming languages define information in the form of objects.
Relational databases use rows. Objects have unique identity as each instance is
physically different from another. Rows are identified by primary key values.
Objects have references that identify and link instances together. Rows are left
intentionally distinct requiring related rows to be loosely tied together using
foreign keys. Objects stand alone, existing as long as they are still referenced
by another object. Rows exist as elements of tables, vanishing as soon as they
are removed.
The best solutions so far have been elaborate database abstraction layers that
transfer information between the applications domain-specific object-models and
the tabular representation of the database, reshaping and reformatting the data
each way. Yet by obscuring the true data source, these solutions end up throwing
away the most compelling feature of relational databases; the ability for the
data to be queried.
LINQ to SQL is a language-agnostic runtime-engine for managing relational
data as objects without losing the ability to query. It does this by
translating language-integrated queries (i.e., queries written using a
programming language such as C#) into SQL for execution by the database, and
then translating the tabular results back into objects you define. Your
application is then free to manipulate the objects while LINQ to SQL stays in
the background tracking your changes automatically.
Note that LINQ to SQL does not actually execute queries; the relational
database does. LINQ to SQL translates the queries you wrote into equivalent SQL
queries and sends them to the server for processing. Because execution is
deferred, LINQ to SQL is able to examine your entire query even if assembled
from multiple parts.
Objects linked to relational data can be defined just like normal objects, only
decorated with attributes to identify how properties correspond to columns. Of
course, it is not even necessary to do this by hand. A design-time tool is
provided to automate translating pre-existing relational database schemas into
object definitions for you. Together, the LINQ to SQL run-time infrastructure
and design-time tools significantly reduce the workload for the database
application developer.
If you query for a specific customer twice, you get two rows of
data, each containing the same information. But with objects, you expect
something quite different. You expect that if you ask the
DataContext for the same information again, it will in fact give you back
the same object instance. You expect this because objects have special meaning
for your application and you expect them to behave like normal objects.
Because of this, the DataContext manages object
identity. Whenever a new row is retrieved from the database, it is logged in an
identity table by its primary key and a new object is created. Whenever that
same row is retrieved again, the original object instance is
handed back to the application. In this way, the
DataContext translates the databases concept of identity (keys) into the
languages concept (instances). The application only ever sees the object in the
state that it was first retrieved. The new data, if different, is
thrown away.
You might be puzzled by this, since why would any application throw data away?
As it turns out this is how LINQ to SQL manages integrity of the local objects
and is able to support optimistic updates. Since the only changes that occur
after the object is initially created are those made by the application, the
intent of the application is clear. If changes by an outside party have occurred
in the interim they will be identified at the time
SubmitChanges() is called. More of this is explained in the Simultaneous
Changes section.
Note that, in the case that the database contains a table without a primary key,
LINQ to SQL allows queries to be submitted over the table, but it does not
allow updates. This is because the framework cannot identify which row
to update given the lack of a unique key. Of course, if the object requested by
the query is easily identifiable by its primary key as one already retrieved no
query is executed at all. The identity table acts as a cache storing all
previously retrieved objects.
Note: All the code contained here is typically auto-generated by creating a new data class of type *.dbml (Linq to SQL classes in VS.NET 2008). This manually created code is used to illustrate concepts contained within when a .dbml file is generated.
In the code below, Customer class is an entity class that is associated with the customers table in the Northwind sample database. Note the following regarding the definition of the Customer class:
The [Table] attribute has a
Name property that you can use to specify the
exact name of the database table. If no Name
property is supplied, LINQ to SQL will assume the database table has the
same name as the class. In addition to associating classes to tables you
will need to denote each field or property you intend to associate with a
database column.
The [Column] attribute is needed to
associate between class properties and actual fields in a table. You only need
to supply information in the [Column] attribute if
it differs from what can be deduced from your field or property declaration.
In this example, you need to tell LINQ to SQL that the
CustomerID field is part of the primary key in the table, yet you don't
have to specify the exact name or type.
The [Association] attribute is used to identify
relations between tables. Relationships in relational databases are typically
modelled as foreign key values referring to primary keys in other tables. To
navigate between them, you must explicitly bring the two tables together using a
relational join operation. Objects, on the other hand, refer to each other using
property references or collections of references navigated using "dot" notation.
Obviously, dotting is simpler than joining, since you need not recall the
explicit join condition each time you navigate.
For example, in a one-to-many relation, a customer has many orders. To establish
this relation between the Customer and
Order classes, the Customer
class needs to maintain a collection of Orders, and
in LINQ this collection of Orders is represented by
an instance of EntitySet.
EntitySet provides for deferred loading and relationship maintenance for
the collection side of one-to-many and one-to one, i.e., for the orders
collection. To complete this relationship, the Order
class needs to link back to a customer, and this is accomplished with
EntityRef structure. EntityRef
provides for deferred loading and relationship maintenance for the singleton
side of a one-to-many relationship in a LINQ to SQL application.
LINQ to SQL defines an [Association] attribute you
can apply to a member used to represent a relationship. An association
relationship is one like a foreign-key to primary-key relationship that is made
by matching column values between tables. [Association]
attribute designates a property to represent a database association, such as a
foreign key relationship. The [Association]
attribute is typically applied to members of type EntitySet
or EntityRef.
[Association] attribute has a few relevant properties such as:
Storage property: gets or sets a private storage field to hold the value from a column.
OtherKey property: gets or sets one or more members of the target entity class as key values on the other side of the association.
ThisKey property: gets or sets members of this entity class to represent the key values on this side of the association.
The following example illustrates the concepts above:
[Table( Name="Customers")]
internal class Customer
{
// Data members (Columns(
private string _strCustomerID;
private string _strCity;
// Data members (Relations)
private EntitySet<Order> _esOrders; // Relationship is one-to-many
public Customer()
{
_esOrders = new EntitySet<Order>(new Action<Order>(this.attach_Orders), new Action<Order>(this.detach_Orders));
}
// Properties (Columns)
[Column(IsPrimaryKey=true)]
public string CustomerID
{
get { return _strCustomerID; }
set { _strCustomerID = value; }
}
[Column]
public string City
{
get { return _strCity; }
set { _strCity = value; }
}
// Properties (Relation)
// This property defines the relation between customers and their orders. The OtherKey
// property specifies the name of one or more properties (CustomerID in this case)
// in the related class (Order in this case) to be compared with this one.
[Association(Storage = "_esOrders", OtherKey = "OrderCustomerID")]
public EntitySet<Order> Orders
{
get { return _esOrders; }
set { _esOrders.Assign(value); }
}
// Overrides
public override string ToString() { return CustomerID + "/" + City; }
// Helpers
private void attach_Orders(Order entity)
{
entity.Customer = this;
}
private void detach_Orders(Order entity)
{
entity.Customer = null;
}
}
[Table(Name="Orders")]
internal class Order
{
// Data members (Columns)
private int _nOrderID;
private string _strCustomerID;
private DateTime _dtOrderDate;
// Data members (Relations)
private EntityRef<Customer> _erCustomer;
// Constructor
public Order()
{
_erCustomer = default(EntityRef<Customer>);
}
// Properties (Columns)
[Column(IsPrimaryKey=true)]
public int OrderID
{
get { return _nOrderID; }
set { _nOrderID = value; }
}
[Column(Name="CustomerID")]
public string OrderCustomerID
{
get { return _strCustomerID; }
set { _strCustomerID = value; }
}
[Column]
public DateTime OrderDate
{
get { return _dtOrderDate; }
set { _dtOrderDate = value; }
}
// Properties (Relations)
[Association(Storage = "_erCustomer", ThisKey = "OrderCustomerID")]
public Customer Customer
{
get { return _erCustomer.Entity; }
set { _erCustomer.Entity = value; }
}
// Overrides
public override string ToString() { return OrderID + "/" + OrderCustomerID + "/" + OrderDate.ToString(); }
}
A Data context (DataContext class) represents the main entry point for the LINQ to SQL framework. The DataContext is the source of all entities mapped over a database connection. 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. In general, a DataContext instance is designed to last for one "unit of work" however your application defines that term. A DataContext is lightweight and is not expensive to create. A typical LINQ to SQL application creates DataContext instances at method scope or as a member of short-lived classes that represent a logical set of related database operations. The purpose of the DataContext is therefore, to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results.
The following example illustrates the basic structure of a DataContext-derived class. Again note that a DataContext along with entity classes are automatically created when a .dbml file is created and tables/stored procedures are dragged and dropped (from Server Explorer) on the design surface of a .dbml file
class NorthwindDataContext : DataContext
{
// Data Members (i.e., tables to be queried from the database)
Table<Customer> _tblCustomers;
Table<Order> _tblOrders;
// Constructors
public NorthwindDataContext(string connection)
: base(connection)
{
Customers = GetTable<Customer>(); // Retrieves data from database!
Orders = GetTable<Order>(); // Retrieves data from database!
}
public Table<Customer> Customers
{
get { return _tblCustomers; }
set { _tblCustomers = value; }
}
public Table<Order> Orders
{
get { return _tblOrders; }
set { _tblOrders = value; }
}
}
It is recommended that you declare a strongly typed DataContext instead of relying on the basic DataContext class and the GetTable() method. A strongly typed DataContext declares all Table collections as members of the context
The first step in building a LINQ to SQL application is declaring the classes you will use to represent your application data. These classes are referred to as entity classes. The second step is creating and using a DataContext object. The DataContext is the main conduit by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply.
The following example the classes declared in Entity Classes and Data Contexts sections above:
/// <summary>
/// Shows the very basics of LINQ to SQL
/// </summary>
public void TestBasicDataContext()
{
// Establish a data context session. Note here that we are relying on the basic
// DataContext class and its GetTable() method. The next test method, 'TestStronglyTypedDataContext'
// will use a data context class that derives from DataContext to declare all Table
// collections as members of the context
DataContext ctxt = new DataContext(Program.ConnectionString);
// Get customer data table. Each database table is accessible via the GetTable() method using
// its entity class to identify it
Table<Customer> customers = ctxt.GetTable<Customer>();
// Write query to collect data. Note that the local variable 'result' refers to the description
// of the query not the result of executing it. not until the application attempts to enumerate
// the contents of the query that it actually executes.
IQueryable<Customer> result = from customer in customers
where customer.City == "London"
select customer;
// Output some information about 'result'
// Type of 'result': DataQuery`1
Trace.WriteLine("Type of 'result': " + result.GetType().Name);
// Query Provider: SELECT [t0].[CustomerID], [t0].[City] FROM [Customers] AS [t0] WHERE [t0].[City] = @p0
Trace.WriteLine("Query Provider: " + result.Provider.ToString());
// Query Expression: Table(Customer).Where(customer => (customer.City = "London"))
Trace.WriteLine("Query Expression: " + result.Expression.ToString());
// Query Element: LinqToSQL.EntityClasses.Customer
Trace.WriteLine("Query Element: " + result.ElementType.ToString());
// Output:
//AROUT/London
//BSBEV/London
//CONSH/London
//EASTC/London
//NORTS/London
//SEVES/London
foreach (Customer customer in result)
Trace.WriteLine( customer.ToString() );
}
/// <summary>
/// Basics of LINQ to SQL but using a strongly-typed DataContext
/// </summary>
public void TestStronglyTypedDataContext()
{
// The following shows how to use a stronglyl-typed data context. NorthwindDataContext
// constructor initializes the underlying tables
DataContexts.NorthwindDataContext ctx = new LinqToSQL.DataContexts.NorthwindDataContext(Program.ConnectionString);
// Write a query to collect data. Note that the local variable 'customers' refers to the
// description of the query not the result of executing it. Only difference from previous
// example is the use of a strongly-types data context. Again, it is not until the application
// attempts to enumerate the contents of the query that it actually executes.
IQueryable<Customer> customers = from customer in ctx.Customers
where customer.City == "London"
select customer;
// Output
//AROUT/London
//BSBEV/London
//CONSH/London
//EASTC/London
//NORTS/London
//SEVES/London
foreach (Customer customer in customers)
Trace.WriteLine(customer.ToString());
// Use the strongly-typed data context to query relations
// result has a type of IQueryable<Customer,Order>, in other words, it is a collection
// where each entry is an anonymous object containing Customer and Order objects
// as its properties
// result corresponds to the following query
// "SELECT [t0].[CustomerID], [t0].[City], [t1].[OrderID], [t1].[CustomerID] AS [OrderCustomerID],
// [t1].[OrderDate]
// FROM [Customers] AS [t0], [Orders] AS [t1]
// WHERE ([t0].[City] = @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])"
//
var result = from customer in ctx.Customers // result type: IQueryable<<LinqToSQL.Customer,LinqToSQL.Order>>
from order in customer.Orders
where customer.City == "London"
select new { customer, order };
// Output:
//{ customer = BSBEV/London, order = 10289/BSBEV/26/08/1996 00:00:00 }
//{ customer = AROUT/London, order = 10355/AROUT/15/11/1996 00:00:00 }
//{ customer = SEVES/London, order = 10359/SEVES/21/11/1996 00:00:00 }
//{ customer = EASTC/London, order = 10364/EASTC/26/11/1996 00:00:00 }
//{ customer = SEVES/London, order = 10377/SEVES/09/12/1996 00:00:00 }
//{ customer = AROUT/London, order = 10383/AROUT/16/12/1996 00:00:00 }
//{ customer = SEVES/London, order = 10388/SEVES/19/12/1996 00:00:00 }
//{ customer = EASTC/London, order = 10400/EASTC/01/01/1997 00:00:00 }
//{ customer = CONSH/London, order = 10435/CONSH/04/02/1997 00:00:00 }
//{ customer = AROUT/London, order = 10453/AROUT/21/02/1997 00:00:00 }
//{ customer = CONSH/London, order = 10462/CONSH/03/03/1997 00:00:00 }
//{ customer = BSBEV/London, order = 10471/BSBEV/11/03/1997 00:00:00 }
//{ customer = SEVES/London, order = 10472/SEVES/12/03/1997 00:00:00 }
//{ customer = BSBEV/London, order = 10484/BSBEV/24/03/1997 00:00:00 }
//{ customer = NORTS/London, order = 10517/NORTS/24/04/1997 00:00:00 }
//{ customer = SEVES/London, order = 10523/SEVES/01/05/1997 00:00:00 }
//{ customer = EASTC/London, order = 10532/EASTC/09/05/1997 00:00:00 }
//{ customer = BSBEV/London, order = 10538/BSBEV/15/05/1997 00:00:00 }
//{ customer = BSBEV/London, order = 10539/BSBEV/16/05/1997 00:00:00 }
//{ customer = SEVES/London, order = 10547/SEVES/23/05/1997 00:00:00 }
//{ customer = AROUT/London, order = 10558/AROUT/04/06/1997 00:00:00 }
//{ customer = BSBEV/London, order = 10578/BSBEV/24/06/1997 00:00:00 }
//{ customer = BSBEV/London, order = 10599/BSBEV/15/07/1997 00:00:00 }
//{ customer = AROUT/London, order = 10707/AROUT/16/10/1997 00:00:00 }
//{ customer = EASTC/London, order = 10726/EASTC/03/11/1997 00:00:00 }
//{ customer = AROUT/London, order = 10741/AROUT/14/11/1997 00:00:00 }
//{ customer = AROUT/London, order = 10743/AROUT/17/11/1997 00:00:00 }
//{ customer = NORTS/London, order = 10752/NORTS/24/11/1997 00:00:00 }
//{ customer = AROUT/London, order = 10768/AROUT/08/12/1997 00:00:00 }
//{ customer = AROUT/London, order = 10793/AROUT/24/12/1997 00:00:00 }
//{ customer = SEVES/London, order = 10800/SEVES/26/12/1997 00:00:00 }
//{ customer = SEVES/London, order = 10804/SEVES/30/12/1997 00:00:00 }
//{ customer = CONSH/London, order = 10848/CONSH/23/01/1998 00:00:00 }
//{ customer = AROUT/London, order = 10864/AROUT/02/02/1998 00:00:00 }
//{ customer = SEVES/London, order = 10869/SEVES/04/02/1998 00:00:00 }
//{ customer = AROUT/London, order = 10920/AROUT/03/03/1998 00:00:00 }
//{ customer = BSBEV/London, order = 10943/BSBEV/11/03/1998 00:00:00 }
//{ customer = BSBEV/London, order = 10947/BSBEV/13/03/1998 00:00:00 }
//{ customer = AROUT/London, order = 10953/AROUT/16/03/1998 00:00:00 }
//{ customer = EASTC/London, order = 10987/EASTC/31/03/1998 00:00:00 }
//{ customer = AROUT/London, order = 11016/AROUT/10/04/1998 00:00:00 }
//{ customer = BSBEV/London, order = 11023/BSBEV/14/04/1998 00:00:00 }
//{ customer = EASTC/London, order = 11024/EASTC/15/04/1998 00:00:00 }
//{ customer = EASTC/London, order = 11047/EASTC/24/04/1998 00:00:00 }
//{ customer = EASTC/London, order = 11056/EASTC/28/04/1998 00:00:00 }
//{ customer = NORTS/London, order = 11057/NORTS/29/04/1998 00:00:00 }
foreach (var v in result)
Trace.WriteLine(v);
}
/// <summary>
/// As soon as entity objects are available—either by retrieving them through a query or
/// constructing them anew—you may manipulate them as normal objects in your application,
/// changing their values or adding and removing them from collections as you see fit.
/// LINQ to SQL tracks all your changes and transmits them back to the database when you
/// call SubmitChanges() method
/// </summary>
public void TestSubmittingQueryChanges()
{
try
{
// Collect data
DataContexts.NorthwindDataContext ctx = new LinqToSQL.DataContexts.NorthwindDataContext(Program.ConnectionString);
// Get Customer object for id "BSBEV"
string strID = "BSBEV";
Customer customer = ctx.Customers.Single((Customer c) => c.CustomerID == strID);
// Output some details
Trace.WriteLine(customer.ToString());
// Change customer details
customer.City = "Paris";
// Create and initialize a new order
// Note: OrderID is an identity column and does not need to be initialized
Order order = new Order();
order.OrderCustomerID = customer.CustomerID;
order.OrderDate = DateTime.Now;
customer.Orders.Add(order);
// Submit all changes to database via the DataContext
ctx.SubmitChanges();
}
catch (Exception ex)
{
// TODO: SubmitChanges fails on this error:
// "Cannot insert explicit value for identity column in table 'Orders' when IDENTITY_INSERT
// is set to OFF"
Trace.WriteLine(ex.Message);
}
}
After entities are retrieved from the database, you are free to manipulate them. As you do this, LINQ to SQL tracks changes so that it can persist them into the database when SubmitChanges() is called. The following example illustrates:
public void TestTrackingChanges()
{
// Open a connection to database
LinqToSQL.NorthwindClassesDataContext ctxt = new NorthwindClassesDataContext(Program.ConnectionString);
// As soon as CompanyName is assigned, LINQ to SQL becomes aware of the change and is able to record
// it. The original values of all data members are retained by the change tracking service.
Customer customer = ctxt.Customers.Single((Customer c) => c.CustomerID == "ALFKI");
customer.CompanyName = "Some new name";
// The change tracking service also records all manipulations of relationship properties.
// For example, you can move orders from one customer to another by simply making an
// assignment to their Customer property. The following shows a way to change orders from
// one custoemr to another
Customer customer1 = ctxt.Customers.Single(c1 => c1.CustomerID == "ALFKI");
Customer customer2 = ctxt.Customers.Single(c2 => c2.CustomerID == "ANATR");
Order order = customer2.Orders[0];
customer1.Orders.Add(order);
customer2.Orders.Remove(order); // Removes order from customer but not from database
// If you assign a relationship the value of null, you are in fact getting rid of the
// relationship completely. Assigning a Customer property of an order to null actually
// removes the order from the customer's list.
Customer customer3 = ctxt.Customers.Single(c1 => c1.CustomerID == "BOLID");
Order o1 = customer3.Orders[0];
o1.Customer = null; // Remove order from the customer's list of orders
// It is important to note that removing a relationship (just as in the example above) does
// not imply that an object has been deleted from the database. Remember, the lifetime of
// the underlying data persists in the database until the row has been deleted from the table.
// The only way to actually delete an object is to remove it from its Table collection.
// When the order object is removed from the Orders table, it is marked for deletion by the
// change tracking service. Actual deletion from the database will occur when the changes are
// submitted on a call to SubmitChanges(). Note that the object itself is never deleted.
// The runtime manages the lifetime of object instances, so it sticks around as long as you
// are still holding a reference to it. However, after an object has been removed from its Table
// and changes submitted it is no longer tracked by the change tracking service.
ctxt.Orders.DeleteOnSubmit(o1);
// Change tacking and identity management only apply to those objects that the DataContext
// is aware of. In the following code object customer3 is not part of the change tracking
// service until it has been added to the DataContext
Customer customer4 = new Customer();
customer4.CustomerID = "YMD";
customer4.CompanyName = "YMD Ltd";
ctxt.Customers.InsertOnSubmit(custom4);
// Regardless of how many changes you make to your objects, those changes were only made to
// in-memory replicas. Nothing has yet happened to the actual data in the database.
// Transmission of this information to the server will not happen until you explicitly
// request it by calling SubmitChanges() on the DataContext.
//
// DataContext.SubmitChanges() will attempt to translate all your changes into equivalent SQL
// commands, inserting, updating, or deleting rows in corresponding tables. The order of
// submission is orchestrated by a service of the DataContext known as the 'change processor'.
//
// Note that if no transaction is already in scope, the DataContext will automatically start
// a database transaction to guard updates when you call SubmitChanges().
//
// Also note that foreign key constraints and uniqueness constraints in the database play a big
// part in determining the correct ordering of changes. Then, just before any actual changes
// are transmitted, a transaction is started to encapsulate the series of individual commands
// unless one is already in scope. Finally, one by one the changes to the objects are
// translated into SQL commands and sent to the server. At this point, any errors detected
// by the database will cause the submission process to abort and an exception will be raised.
// All changes to the database will be rolled back as if none of the submissions ever took
// place. When the transaction around the submission completes successfully, the DataContext will
// accept the changes to the objects by simply forgetting the change tracking information.
ctxt.SubmitChanges();
// Read-only data context: Many scenarios do not require updating the entities retrieved
// from the database. Showing a read-only table is one obvious example. In all such cases,
// it is possible to improve performance by instructing the DataContext not to track the
// changes to the entities. This is achieved by specifying the DataContext.ObjectTrackingEnabled
// to false:
{
// Create a new read-only data context
LinqToSQL.NorthwindClassesDataContext ctxtReadonly = new NorthwindClassesDataContext(Program.ConnectionString);
ctxtReadonly.ObjectTrackingEnabled = false;
// All object retrieved via ctxtReadonly are readonly
Customer c1= ctxtReadonly.Customers.Single((Customer c) => c.CustomerID == "ALFKI");
// Attempt to modity an object
try
{
c1.City = "LA";
ctxtReadonly.SubmitChanges();
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
}
The most popular form of managing simultaneous changes is to employ
a form of optimistic concurrency. In this model, no locks against the
database rows are taken at all. That means any number of changes to the database
could have occurred between the time you first retrieved your objects and the
time you submitted your changes. Unless you want to go with a policy that the
last update wins, wiping over whatever else occurred before you, you probably
want to be alerted to the fact that the underlying data was changed by someone
else.
The DataContext has built-in support for optimistic
concurrency by automatically detecting change conflicts. Individual updates only
succeed if the database's current state matches the state of the data when you
first retrieved it. This happens on a per object basis, only alerting you
to violations if they happen to objects you have made changes to.
You can control the degree to which the DataContext detects change conflicts when you define your entity classes. Each Column attribute has a property called UpdateCheck that can be assigned one of three values: Always, Never, and WhenChanged. If not set, the default for a Column attribute is Always, meaning the data values represented by that member are always checked for conflicts, that is, unless there is an obvious tie-breaker like a version stamp. A Column attribute has an IsVersion property that allows you to specify whether the data value constitutes a version stamp maintained by the database. If a version exists, then the version is used alone to determine if a conflict has occurred.
When a change conflict does occur, an exception will be thrown just as if it were any other error. The transaction surrounding the submission will abort, yet the DataContext will remain the same, allowing you the opportunity to rectify the problem and try again.
As for transactions, if a transaction completes successfully, the DataContext throws out all the accumulated tracking information and treats the new states of the entities as unchanged. It does not, however, rollback the changes to your objects if the transaction fails. This allows you the maximum flexibility in dealing with problems during change submission. The following example illustrates:
public void TestConcurrencyAndTransactions()
{
try
{
LinqToSQL.NorthwindClassesDataContext ctxt = new NorthwindClassesDataContext(Program.ConnectionString);
// Retrieve a customer and change its details
Customer customerCopy1 = ctxt.Customers.Single<Customer>((customer) => customer.CustomerID = "YMD");
Customer customerCopy2 = ctxt.Customers.Single<Customer>((customer) => customer.CustomerID = "YMD");
// Change copy 1 and submit changes to database
using (TransactionScope ts = new TransactionScope())
{
customerCopy1.Country = "UK";
ctxt.SubmitChanges();
ts.Complete();
}
// Change copy 2 and submit changes to database
customerCopy1.ContactName = "Yazan Diranieh";
ctxt.SubmitChanges(); // Throws exception. Conflit detected
}
catch (ChangeConflictException ex)
{
Trace.WriteLine(ex.Message);
}
catch (Exception ex)
{
Trace.WriteLine(ex.Message);
}
}
When SubmitChanges() is called, LINQ to
SQL generates and executes SQL commands to insert, update, and delete rows in
the database. These actions can be overridden and in their place custom code can
be used to perform the desired actions, such as calling stored procedures.
To call a stored procedure, simply define a method in your strongly-typed
DataContext. If your strongly-typed
DataContext is auto-generated, as it should be, make
sure that this method is placed in a partial class. This ensures that your code
will not be overwritten by the code-generation tool. The following example
illustrates:
// TO DO
A LINQ to SQL command object holds onto a string that describes a
query. Likewise, an IQueryable object holds onto a
description of a query encoded as a data structure known as an
Expression. A command object has an
ExecuteReader() method that causes execution,
returning results as a DataReader. An
IQueryable object has a
GetEnumerator() method that causes the execution. Therefore, if a query
is enumerated twice, it will be executed twice. This behavior is known as
deferred execution (just like an ADO.NET command object which can be
executed multiple times).
It is common in many applications to execute structurally similar queries many
times. To avoid executing multiple times, execute the query only once by
converting the results into a standard collection class, using the standard
query operators ToList() or
ToArray(). You can then iterate over the results without actually
re-executing the query in each iteration.
Any approach to increase performance is to compile the query once. This allows
you to execute the query several times in the application with different
parameters. You can compile querys in LINQ to SQL by using the
CompiledQuery class.
Note: The following uses the auto-generated classes from the LONQ-To-SQL designer (Project → Add New Item → LINQ To SQL Classes)
public void TestQueryExecution()
{
LinqToSQL.NorthwindClassesDataContext ctx = new LinqToSQL.NorthwindClassesDataContext(Program.ConnectionString);
// The following shows how to avoid executing a query multiple times by converting
// the results into a standard collection class
{
// Declare a query (does not execute).
var q1 = from customer in ctx.Customers
where (customer.City == "London")
select customer;
// Execute query once! SQL Servcer Profile shows the following query
// exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[CompanyName],
// [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City],
// [t0].[Region], [t0].[PostalCode],
// [t0].[Country], [t0].[Phone], [t0].[Fax]
// FROM [dbo].[Customers] AS [t0]
// WHERE [t0].[City] = @p0',N'@p0 nvarchar(6)',@p0=N'London'
List<Northwind.Customer> lstCustomers = q1.ToList();
// Iterate over query results (does not re-execute query)
// Output
//AROUT/London
//BSBEV/London
//CONSH/London
//EASTC/London
//NORTS/London
//SEVES/London
foreach (Northwind.Customer customer in lstCustomers)
Trace.WriteLine(customer.CustomerID + "/" + customer.City);
}
// One benefit of deferred execution is that queries may be piecewise constructed with
// execution only occurring when the construction is complete. You can start out composing
// a portion of a query, assigning it to a local variable and then sometime later continue
// applying more operators to it.
{
// Create and initialize some flags
bool bOrderByCustomerID = true;
string strCityFilter = "London";
// Create the base query
var q2 = from customer in ctx.Customers
select customer;
// Examine flags and enhance query as appropriate
if (bOrderByCustomerID)
q2 = from customer in q2
orderby customer.CustomerID
select customer;
if (strCityFilter != string.Empty)
q2 = from customer in q2
where (customer.City == strCityFilter)
select customer;
// Execute query. SQL Server profiler shows the following query only once (i.e,.
// this query is executed at the start of the foreach loop)
// exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
// [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
// [t0].[Country], [t0].[Phone], [t0].[Fax]
// FROM [dbo].[Customers] AS [t0]
// WHERE [t0].[City] = @p0
// ORDER BY [t0].[CustomerID]',N'@p0 nvarchar(6)',@p0=N'London'
// Output
//AROUT/London
//BSBEV/London
//CONSH/London
//EASTC/London
//NORTS/London
//SEVES/London
foreach (Northwind.Customer customer in q2)
Trace.WriteLine(customer.CustomerID + "/" + customer.City);
}
// CompileD queries
{
// In writing this function, write first the function call then use Intellisense
// to show what the function signature should be! The Compile method returns a
// delegate that can be cached and executed afterward several times by just
// changing the input parameter.
Func<NorthwindClassesDataContext, string, IQueryable<Customer>> delCustomerByCity =
CompiledQuery.Compile(
(NorthwindClassesDataContext dcNorthwind, string argCity) =>
from customer in dcNorthwind.Customers
where (customer.City == argCity)
select customer
);
// Execute query. SQL Server profiler shows the following query only once
// at the start of the foreach loop
// exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[CompanyName],
// [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City],
// [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
// FROM [dbo].[Customers] AS [t0]
// WHERE [t0].[City] = @p0',N'@p0 nvarchar(6)',@p0=N'London'
IQueryable<Customer> customers = delCustomerByCity(ctx, "London");
}
}
With LINQ-to-SQL you simply using dot notation to access the
relations between two table objects. These access operations translate to more
complicated joins or correlated sub-queries in the equivalent SQL, allowing you
to walk through your object graph during a query.
You expect your object model to maintain an illusion that it is an in-memory
extension of the database, with related objects immediately available. LINQ to
SQL implements a technique called deferred loading in order to help maintain
this illusion. When you query for an object you actually only retrieve the
objects you asked for. The related objects are not automatically fetched at the
same time. However, the fact that the related objects are not already loaded is
not observable since as soon as you attempt to access them a request goes out to
retrieve them. The following example illustrates:
public void TestRelations()
{
LinqToSQL.NorthwindClassesDataContext ctx = new LinqToSQL.NorthwindClassesDataContext(Program.ConnectionString);
// The following query queries for a particular set of orders (shipVia = 3) and then only
// sends an email notification to particular customers if the Frieght is ? 300.
// Deferred loading means that you would not necessary need to retrieve all customer
// data up front with every order.
{
// Create query
var q = from order in ctx.Orders
where (order.ShipVia == 3)
select order;
// Execute query. The following query is executed only once at the beginning of the
// foreach loop:
//exec sp_executesql N'SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],
// [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia],
// [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity],
// [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
//FROM [dbo].[Orders] AS [t0]
//WHERE [t0].[ShipVia] = @p0',N'@p0 int',@p0=3
foreach (Northwind.Order order in q)
{
// At this point a Northwind.Order object has been loaded with data from the database
// Customer information associated with this order object is not yet retrieved
if (order.Freight > 300)
{
// Customer information retreived here. The following query is executed for
// each customer show Freight is ? 300:
//xec sp_executesql N'SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
// [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
// [t0].[Country], [t0].[Phone], [t0].[Fax]
//FROM [dbo].[Customers] AS [t0]
//WHERE [t0].[CustomerID] = @p0',N'@p0 nvarchar(5)',@p0=N'PICCO'
SendCustomerNotification(order.Customer);
}
else
{
// No need to retrieve customer information
Trace.WriteLine("Freight less than maximum");
}
}
}
// On the other hand, you might have an application that needs to look at customer and
// order data at the same time. Given that declaring a query does not execute it, you
// force data retreival by iterating over each element. The following illustrates
// how to retreive a customer and all the customer's orders
// TODO: Check if each iteration first a query to the DB (use SQL Server Profiler)
{
// Crate a query. Does NOT retreive data
var q = from customer in ctx.Customers
where (customer.City == "London")
select customer;
// First foreach iteration retrieves all customers in one query only. Second iteration
// retreives all orders for the current customer. In other words, a query is issued
// to retreive orders for each customer. For example, the following query is issued
// to retrieve all orders for customer ID 'AROUT':
//exec sp_executesql N'SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],
// [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia],
// [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
// [t0].[ShipPostalCode], [t0].[ShipCountry]
//FROM [dbo].[Orders] AS [t0]
//WHERE [t0].[CustomerID] = @p0',N'@p0 nvarchar(5)',@p0=N'AROUT'
foreach (Northwind.Customer customer in q)
foreach (Northwind.Order order in customer.Orders)
ProcessCustomerOrder(order);
}
// LINQ to SQL allows you to request immediate loading of a region of your object model
// It does this by allowing the specification of a DataLoadOption for a DataContext. The
// DataLoadOption class is used to instruct the framework about which objects to retrieve
// when a particular type is retrieved.
// The call to DataLoadOptions.LoadWith ensures that successive access to the Orders
// property on a Customer object doesn't trigger a database query. In other words, the
// code ensures that orders are loaded for each customer
{
// We need to have a new data context. Use of the existing 'ctx' data context generates
// the following error: "Setting load options is not allowed after results have been
// returned from a query."
LinqToSQL.NorthwindClassesDataContext ctxNew = new LinqToSQL.NorthwindClassesDataContext(Program.ConnectionString);
// LoadWith<T> function specifies which sub-objects to retrieve when a query is submitted
// for an object of type T.
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Customer>((Customer customer) => customer.Orders);
ctxNew.LoadOptions = dlo;
var q = from customer in ctxNew.Customers
where (customer.City == "London")
select customer;
// The following query is issued only once at the start of the outer foreach statement:
//exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
//[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
//[t0].[Country], [t0].[Phone], [t0].[Fax], [t1].[OrderID], [t1].[CustomerID] AS [CustomerID2],
//[t1].[EmployeeID], [t1].[OrderDate], [t1].[RequiredDate], [t1].[ShippedDate],
//[t1].[ShipVia], [t1].[Freight], [t1].[ShipName], [t1].[ShipAddress], [t1].[ShipCity],
//[t1].[ShipRegion], [t1].[ShipPostalCode], [t1].[ShipCountry], (
//SELECT COUNT(*)
//FROM [dbo].[Orders] AS [t2]
//WHERE [t2].[CustomerID] = [t0].[CustomerID]
//) AS [count]
//FROM [dbo].[Customers] AS [t0]
//LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
//WHERE [t0].[City] = @p0
//ORDER BY [t0].[CustomerID], [t1].[OrderID]',N'@p0 nvarchar(6)',@p0=N'London'
foreach (Customer customer in q)
{
Trace.WriteLine(customer.CustomerID);
foreach (Order order in customer.Orders)
Trace.WriteLine(order.OrderID);
}
}
// The DataLoadOptions class can also be used to specify sub-queries that are applied
// to a relationship navigation. DataLoadOptions.AssociateWith method filters the objects
// retrieved for a particular relationship.
{
// We need to have a new data context. Use of the existing 'ctx' data context generates
// the following error: "Setting load options is not allowed after results have been
// returned from a query."
LinqToSQL.NorthwindClassesDataContext ctxNew = new LinqToSQL.NorthwindClassesDataContext(Program.ConnectionString);
// The following uses AssociateWith on Customer object to filter the associated
// Order objects such that only orders shipped today are retrieved
System.DateTime dtShipDate = new System.DateTime(1996, 12, 20);
DataLoadOptions dlo = new DataLoadOptions();
dlo.AssociateWith<Customer>(
(Customer customer) => customer.Orders.Where( (Order order) => order.ShippedDate < dtShipDate));
ctxNew.LoadOptions = dlo;
// Create query
var q = from customer in ctxNew.Customers
where (customer.City == "London")
select customer;
// Customer information retrieved at the beginning of the outer foreach statement.
// The following query is the issued to retrieve order information for the current
// customer:
//exec sp_executesql N'SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID],
// [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia],
// [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion],
// [t0].[ShipPostalCode], [t0].[ShipCountry]
//FROM [dbo].[Orders] AS [t0]
//WHERE ([t0].[ShippedDate] < @p0) AND ([t0].[CustomerID] = ((
// SELECT [t2].[CustomerID]
// FROM (
// SELECT TOP 1 [t1].[CustomerID]
// FROM [dbo].[Customers] AS [t1]
// WHERE [t1].[CustomerID] = @p1
// ) AS [t2]
// )))',N'@p0 datetime,@p1 nvarchar(5)',@p0='Dec 20 1996 12:00:00:000AM',@p1=N'AROUT'
//
// Output
//Today's date: 21/11/2007 17:07:57
//AROUT
//10355: 15/11/1996 00:00:00
//10383: 16/12/1996 00:00:00
//BSBEV
//10289: 26/08/1996 00:00:00
//CONSH
//EASTC
//10364: 26/11/1996 00:00:00
//NORTS
//SEVES
//10359: 21/11/1996 00:00:00
//10377: 09/12/1996 00:00:00
Trace.WriteLine("Today's date: " + DateTime.Now);
foreach (Customer customer in q)
{
Trace.WriteLine(customer.CustomerID);
foreach (Order order in customer.Orders)
Trace.WriteLine(order.OrderID + ": " + order.OrderDate);
}
}
}
private void SendCustomerNotification(Northwind.Customer customer)
{
// Logic to send notifications
}
private void ProcessCustomerOrder(Northwind.Order order)
{
// Logic to process a specific order
}
Joins provide an additional mechanism to handle ad-hoc relationships that are not based on primary key/foreign key relations. The following example illustrates:
public void TestJoins()
{
LinqToSQL.NorthwindClassesDataContext ctx = new LinqToSQL.NorthwindClassesDataContext(Program.ConnectionString);
// A simple join query
{
var q = from supplier in ctx.Suppliers
join customer in ctx.Customers on supplier.City equals customer.City
select new { Supplier = supplier.SupplierID, Customer = customer.CustomerID, City = customer.City };
// Query executed only once at the beginning of the foreach statement:
//SELECT [t0].[SupplierID], [t1].[CustomerID], [t1].[City]
//FROM [dbo].[Suppliers] AS [t0]
//INNER JOIN [dbo].[Customers] AS [t1] ON [t0].[City] = [t1].[City]
foreach (var entity in q)
Trace.WriteLine(entity.Supplier + "/" + entity.Customer + "/" + entity.City);
}
// The following is a group-join: Joins Supplier and Customer sequences and groups elements
// of the second sequence (Customer) by the elements of the first sequence (Supplier). In
// other word:
{
var q = from s in ctx.Suppliers
join c in ctx.Customers on s.City equals c.City into scusts
select new { s, scusts };
// Query executed only once at the beginning of the foreach statement:
//SELECT [t0].[SupplierID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
// [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],
// [t0].[Phone], [t0].[Fax], [t0].[HomePage], [t1].[CustomerID],
// [t1].[CompanyName] AS [CompanyName2], [t1].[ContactName] AS [ContactName2],
// [t1].[ContactTitle] AS [ContactTitle2], [t1].[Address] AS [Address2],
// [t1].[City] AS [City2], [t1].[Region] AS [Region2], [t1].[PostalCode] AS [PostalCode2],
// [t1].[Country] AS [Country2], [t1].[Phone] AS [Phone2],
// [t1].[Fax] AS [Fax2], (
// SELECT COUNT(*)
// FROM [dbo].[Customers] AS [t2]
// WHERE [t0].[City] = [t2].[City]
// ) AS [count]
//FROM [dbo].[Suppliers] AS [t0]
//LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t0].[City] = [t1].[City]
//ORDER BY [t0].[SupplierID], [t1].[CustomerID]
// Output
//SupplierID: 1. Customer IDs: AROUT/BSBEV/CONSH/EASTC/NORTS/SEVES/
//SupplierID: 10. Customer IDs: COMMI/FAMIA/QUEEN/TRADH/
//SupplierID: 11. Customer IDs: ALFKI/
//SupplierID: 18. Customer IDs: PARIS/SPECD/
//SupplierID: 25. Customer IDs: MEREP/
int nIndex = 0;
foreach (var item in q)
{
nIndex = 0;
foreach (Customer customer in item.scusts)
{
if (nIndex == 0) Trace.Write("SupplierID: " + item.s.SupplierID + ". Customer IDs: ");
Trace.Write(customer.CustomerID + "/");
nIndex++;
if (nIndex == item.scusts.Count()) Trace.WriteLine(Environment.NewLine);
}
}
}
}
So far, we have only looked at queries for retrieving entities—objects directly associated with database tables. The beauty of a query language is that you can retrieve information in any form you want. You will not be able to take advantage of automatic change tracking or identity management when you do so. However, you can get just the data you want. For example, there is no particular reason to retrieve entire customer objects merely to get each customer's name. You can project out the names as part of the query. Examples in this function illustrate:
public void TestProjections()
{
LinqToSQL.NorthwindClassesDataContext ctx = new LinqToSQL.NorthwindClassesDataContext(Program.ConnectionString);
// The following shows how to retrieve company names for each "London" customer.
// There is no need to retreive entire Customer objects. Note the return type of
// the query
{
IEnumerable<string> names = from customer in ctx.Customers
where (customer.City == "London")
select customer.CompanyName;
foreach (string name in names)
Trace.WriteLine(name);
}
// If you want to get back more than just a single name, but not enough to justify fetching
// the entire customer objectYou can specify any subset you want by constructing the
// results as part of your query. This example uses an anonymous object initializer to
// create a structure that holds two properties (customer name and customer region)
{
var q2 = from customer in ctx.Customers
where (customer.City == "London")
select new { CompanyName = customer.CompanyName, Region = customer.Region };
// Because q2 is an anonymous type, we use implicitly typed local variable declaration
// to access objects within q2
foreach (var item in q2)
Trace.WriteLine(item.CompanyName + "/" + item.Region);
}
// You can project data into newly constructed objects and then refer to those objects'
// members in subsequent query operations
{
var q = from customer in ctx.Customers
where (customer.City == "London")
select new { CompanyName = customer.CompanyName, Region = customer.Region } into x
select x;
}
// Be wary of using parameterized constructors. It is impossible for LINQ to SQL to track
// how constructor usage affects member state without understanding the actual code inside
// the constructor. Instead, the best practice is to always use object initializers to encode
// projections
{
var qGood = from customer in ctx.Customers
where (customer.City == "London")
select new MyType { CompanyName = customer.CompanyName, Region = customer.Region };
var qAvoid = from customer in ctx.Customers
where (customer.City == "London")
select new MyType (customer.CompanyName, customer.Region);
}
}
internal class MyType
{
private string companyname;
private string region;
public MyType() { /* Empty implementation */}
public MyType(string name, string rgn)
{
companyname = name;
region = rgn;
}
public string CompanyName
{
get { return companyname; }
set { companyname = value; }
}
public string Region
{
get { return region; }
set { region = value; }
}
}
LINQ to XML is a modernized in-memory XML programming API designed to take advantage of the latest .NET Framework language innovations. You can think of LINQ to XML as a member of the LINQ Project family of technologies (LINQ to SQL, LINQ to DataSet, LINQ to XML) with LINQ to XML providing an XML Language-Integrated Query capability. LINQ to XML takes advantage of standard query operators and adds query extensions specific to XML. From an XML perspective, LINQ to XML provides the query and transformation power of XQuery and XPath integrated into .NET Framework languages that implement the LINQ pattern.
Because LINQ to XML provides a fully featured in-memory XML programming API you can do all of the things you would expect when reading and manipulating XML. A few examples include the following:
Load XML into memory in a variety of ways (file, XmlReader, and so on).
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, and so on).
You should be able to accomplish most XML programming tasks you run into using this technology. The following outlines some key concepts that differentiate LINQ to XML from other XML programming APIs:
LINQ to XML supports a functional construction approach to constructing XML documents from scratch. Here is how you would construct the following XML tree by using LINQ to XML functional construction:
<contacts>
<contact>
<name>Patrick Hines</name>
<phone type="home">206-555-0144</phone>
<phone type="work">425-555-0145</phone>
<address>
<street1>123 Main St</street1>
<city>Mercer Island</city>
<state>WA</state>
<postal>68042</postal>
</address>
<netWorth>10</netWorth>
</contact>
</contacts>
// Note that indented code shows the structure of the underlying XML
XElement Authors =
new XElement("Authors",
new XElement("contact",
new XElement("name", "Patrick Hines"),
new XElement("phone", "206-555-0144",
new XAttribute("type", "home")),
new XElement("phone", "425-555-0145",
new XAttribute("type", "work")),
new XElement("address",
new XElement("street1", "123 Main St"),
new XElement("city", "Mercer Island"),
new XElement("state", "WA"),
new XElement("postal", "68042")
)
)
);
In DOM, XML nodes, including elements and attributes, must be created in the context of an XML document. In other words, XML nodes are created in the context of the XML document only. With LINQ to XML you can work directly with XML elements in a natural way without the need for a containing document. For example you can do the following:
Create XML elements directly (without an XML document involved at all.)
Load them from XML that exists in a file.
Save (write) them to a writer.
In LINQ to XML you create XML elements directly:
XElement name = new XElement("name");
You do not have to create an XML Document to hold the XML tree. The LINQ to XML object model does provide an XML document to use if necessary. The following is an example of how to create an XML Document with an XML Declaration, Comment, and Processing Instruction along with the contacts content.
// Note that indented code shows the structure of the underlying XML
XElement Authors =
new XElement("Authors",
new XElement("contact",
new XElement("name", "Patrick Hines"),
new XElement("phone", "206-555-0144",
new XAttribute("type", "home")),
new XElement("phone", "425-555-0145",
new XAttribute("type", "work")),
new XElement("address",
new XElement("street1", "123 Main St"),
new XElement("city", "Mercer Island"),
new XElement("state", "WA"),
new XElement("postal", "68042")
)
)
);
Typically, the leaf elements in an XML tree contain values such as strings, integers, and decimals. The same is true for attributes. In LINQ to XML, you can treat elements and attributes that contain values in a natural way, simply cast them to the type that they contain. For example, assuming that name is an XElement that contains a string, you could do the following:
string nameString = (string) name;
Usually this will show up in the context of referring to a child element directly like this:
string name = (string) contact.Element("name");
The following shows the LINQ to XML class hierarchy:
Note the following about the LINQ to XML class hierarchy:
XElement is the fundamental class in LINQ to XML. XML trees are generally made up of a tree of XElements.
XAttributes are name/value pairs associated with an XElement.
XDocuments are created only if necessary, such as to hold a DTD or top level XML processing instruction (XProcessingInstruction). All other XNodes can only be leaf nodes under an XElement, or possibly an XDocument (if they exist at the root level).
The only XNode that can have children is an XContainer, meaning either an XDocument or XElement. An XDocument can contain an XElement (the root element), an XDeclaration, an XDocumentType, or an XProcessingInstruction. An XElement can contain another XElement, an XComment, an XProcessingInstruction, and text (which can be passed in a variety of formats, but will be represented in the XML tree as text).
In the code below, the following sample data is used:
<?xml version="1.0" encoding="utf-8" ?>
<contacts>
<contact>
<name>Patrick Hines</name>
<phone type="home">206-555-0144</phone>
<phone type="work">425-555-0145</phone>
<address>
<street1>123 Main St</street1>
<city>Mercer Island</city>
<state>WA</state>
<postal>68042</postal>
</address>
<netWorth>10</netWorth>
</contact>
<contact>
<name>Gretchen Rivas</name>
<phone type="mobile">206-555-0163</phone>
<address>
<street1>123 Main St</street1>
<city>Mercer Island</city>
<state>WA</state>
<postal>68042</postal>
</address>
<netWorth>11</netWorth>
</contact>
<contact>
<name>Scott MacDonald</name>
<phone type="home">925-555-0134</phone>
<phone type="mobile">425-555-0177</phone>
<address>
<street1>345 Stewart St</street1>
<city>Chatsworth</city>
<state>CA</state>
<postal>91746</postal>
</address>
<netWorth>500000</netWorth>
</contact>
</contacts>
An XML name is represented by an XNamespace
object (which encapsulates the XML namespace URI) and a local name. In XML
syntax, prefixes allow you to create a shortcut for an XML namespace, which
makes the XML document more concise and understandable. LINQ to XML simplifies
XML names by removing XML prefixes from the XML Programming API and encapsulates
them in XNamespace objects. When reading in XML,
each XML prefix is resolved to its corresponding XML namespace. Therefore, when
developers work with XML names they are working with a fully qualified XML name;
an XML namespace, and a local name.
XName represents a name of an XML element or
attribute. The name of an XML element or attribute includes a namespace and a
local name. A fully qualified name is the combination of the two. For example,
<ns:Contact> ... </ns:Contact>
XName contains an implicit conversion operator from string to XName:
public static implicit operator XName (string expandedName)
This means that an XElement or XAttribute constructor that expects an XName object can be passed a string. The following example illustrates:
public void TestXNames()
{
/* Creating an XName in no Namespace */
// Create an XElement called topic that has no namespce. The following
// trace output:
// Local Name: topic
// Namespace:
// XName: topic
// <topic>LINQ to XML</topic>
XElement topic1 = new XElement("topic", "LINQ to XML");
Trace.WriteLine("Local Name: " + topic1.Name.LocalName + "\n" +
"Namespace: " + topic1.Name.NamespaceName + "\n" +
"XName: " + topic1.Name.ToString());
Trace.WriteLine(topic1.ToString());
/* Creating an XName in a Namespace */
// Create an XElement called topic that has the namespace "http://www.diranieh.com".
// Note: XElement constructor used below takes an XName parameter. However, you seldom
// work directly with an XName. For C#, the recommended approach for creating an XName
// in a namespace is to create the fully-qualified name as a string and pass this string
// to an XElement or XAttribute constructor that expects an XName object. XName implicit
// conversion operator from string to XName creates the proper XName object needed
// by XElement constructor. The following trace outputs:
// Local Name: topic
// Namespace: www.diranieh.com
// XName: {www.diranieh.com}topic
// <topic xmlns="www.diranieh.com" />
XNamespace ns = "www.diranieh.com"; // Assigning a string to an XNamespace uses the implicit conversion from String.
XElement topic2 = new XElement(ns + "topic"); // XName implicit conversion from string invoked here
Trace.WriteLine("Local Name: " + topic2.Name.LocalName + "\n" +
"Namespace: " + topic2.Name.NamespaceName + "\n" +
"XName: " + topic2.Name.ToString());
Trace.WriteLine(topic2.ToString());
// You don't have to type the XML namespace every time you use an XML name. You can use the
// facilities of the language itself to make this easier.
// Output:
// <Names xmlns="www.diranieh.com">
// <Name>Yazan</Name>
// </Names>
XNamespace ns2 = "www.diranieh.com";
XElement element1 = new XElement(ns2 + "Names",
new XElement(ns2 + "Name", "Yazan")
);
Trace.WriteLine(element1.ToString());
// When reading in XML, prefixes are resolved to their corresponding XML namespaces.
// What if you need or want to influence prefixes when outputting the XML? You can do
// this by creating xmlns attributes that associate a prefix to an XML namespace.
// Output:
// <ns:Names xmlns:ns="www.diranieh.com">
// <ns:Name>Yazan</ns:Name>
// </ns:Names>
XNamespace ns3 = "www.diranieh.com";
XElement element2 = new XElement(ns2 + "Names", new XAttribute(XNamespace.Xmlns + "ns", ns3),
new XElement(ns2 + "Name", "Yazan"));
Trace.WriteLine(element2.ToString());
}
You can load existing XML into an LINQ to XML XML tree so that you can read it or manipulate it. LINQ to XML provides multiple input sources, including a file, an XmlReader, a TextReader, or a string. The following example illustrates:
public void TestLoadingXML()
{
// Create an XML element from a string//
// Output:
// <Names xmlns="www.diranieh.com">
// <Name>Yazan</Name>
// </Names>
XElement xmlNames = XElement.Parse( @"<Names xmlns=""www.diranieh.com""><Name>Yazan</Name></Names>");
Trace.WriteLine(xmlNames.ToString()); // Output XML string above.
// To input from any of the other sources, you use the Load method
// Output: Contents of the loaded XML file
XElement xmlContacts = XElement.Load(@"C:\Projects\C30\LiniqToXml\TestData.xml");
Trace.WriteLine(xmlContacts.ToString());
// The following shows how to create a document that uses all of the different XML
//node types
// Output
// <?PI Some meaningful instuctions?>
// <!--LINQ to XML demo-->
// <Authors Version="1">
// <Author>Name1</Author>
// <Author>Name2</Author>
// </Authors>
XDocument doc = new XDocument(
new XDeclaration("1.0", "UTF-8", "yes"),
new XProcessingInstruction("PI", "Some meaningful instuctions"),
new XComment("LINQ to XML demo"),
new XElement("Authors", new XAttribute("Version", "1"),
new XElement("Author", "Name1"),
new XElement("Author", "Name2")));
Trace.WriteLine(doc.ToString());
// Functional construction lets you create all or part of your XML tree in a single statement.
// Functional construction is enabled by an XElement constructor that takes a params object.
// public XElement(XName name, params object[] contents);
// The contents parameter is extremely flexible, supporting any type of object that is a legitimate
// child of an XElement.Parameters can be string, XText, XElement, XAttribute, anything else
// whose ToString() result is added as text content.
// By indenting, the XElement constructor resembles the structure of the underlying XML
// Output
// <Contacts>
// <Contact>
// <Name>yazan</Name>
// <Phone>1234</Phone>
// <Address>
// <Street>LINQ Road</Street>
// <Number>33</Number>
// <City>NetVille</City>
// <PostCode>1234-5678</PostCode>
// </Address>
// </Contact>
// </Contacts>
XElement xmlContacts2 = new XElement("Contacts",
new XElement("Contact",
new XElement("Name", "yazan"),
new XElement("Phone", "1234"),
new XElement("Address",
new XElement("Street", "LINQ Road"),
new XElement("Number", 33), // Note number
new XElement("City", "NetVille"),
new XElement( "PostCode", GetPostCode() ) // Note function
)
)
);
Trace.WriteLine( xmlContacts2.ToString()) ;
// The following shows how to use an IEnumerable-based collection and add
// its contents to an XElement object
// Output
// <Persons>
// <Person>A<ID>1</ID></Person>
// <Person>B<ID>2</ID></Person>
// <Person>B<ID>2</ID></Person>
// </Persons>
var Persons = new[] { new { ID = 1, Name = "A" }, new { ID = 2, Name = "B" }, new { ID = 2, Name = "B" } };
XElement xmlPersons = new XElement( "Persons", from person in Persons select
new XElement("Person", person.Name,
new XElement( "ID", person.ID )
));
Trace.WriteLine( xmlPersons.ToString()) ;
}
private string GetPostCode() { return "1234-5678"; }
This function describes more traditional approaches to walking through an XML tree. LINQ provides more powerful options for doing just this (as described in the function named TestQueryingXMLWithLINQ). The following code shows how to do simple XML traversal. Nodes(), Elements(), Elements(XName), and Element(XName) are the basic methods for simple traversal of XML. If you are familiar with XPath, these methods are analogous to child::node(), child::*, child::name, and child::name[1], respectively. The following example illustrates:
public void TestTraversingXML()
{
// To get all of the children of an XElement (or XDocument), you can use the Nodes() method.
// This returns an IEnumerable<> - based object that can be traversed with foreach
// The following foreach statement prints the ENTIRE contents of TestData.xml input file.
// Notice that ToString() on an XNode returns a formatted XML string based on the node type.
// Output
//<contact>
// <name>Patrick Hines</name>
// <phone type="home">206-555-0144</phone>
// <phone type="work">425-555-0145</phone>
// <address>
// <street1>123 Main St</street1>
// <city>Mercer Island</city>
// <state>WA</state>
// <postal>68042</postal>
// </address>
// <netWorth>10</netWorth>
//</contact>
//<contact>
// <name>Gretchen Rivas</name>
// <phone type="mobile">206-555-0163</phone>
// <address>
// <street1>123 Main St</street1>
// <city>Mercer Island</city>
// <state>WA</state>
// <postal>68042</postal>
// </address>
// <netWorth>11</netWorth>
//</contact>
//<contact>
// <name>Scott MacDonald</name>
// <phone type="home">925-555-0134</phone>
// <phone type="mobile">425-555-0177</phone>
// <address>
// <street1>345 Stewart St</street1>
// <city>Chatsworth</city>
// <state>CA</state>
// <postal>91746</postal>
// </address>
// <netWorth>500000</netWorth>
//</contact>
XElement xmlContacts = XElement.Load(@"C:\Projects\C30\LiniqToXml\TestData.xml");
foreach (XNode node in xmlContacts.Nodes())
Trace.WriteLine(node.ToString());
Trace.WriteLine("");
// The previous code got all nodes, irrespective of their type. To be more specific, you
// can ask for content nodes of an XElement of a particular type.
// The following gets all XElement children of xmlContacts object
// Output: Same as above
foreach (XNode node in xmlContacts.Nodes().OfType<XElement>())
Trace.WriteLine(node.ToString());
Trace.WriteLine("");
// The following code is equivalent to the previous code blok. The method Elements()
// returns IEnumerable<XElement>, and is a shortcut for Nodes().OfType<XElement>().
// Output: Same as above
foreach (XNode node in xmlContacts.Elements())
Trace.WriteLine(node.ToString());
Trace.WriteLine("");
// Note that the Parent property of a root element is null. It is not the associated
// document as it is in some other XML APIs. In LINQ to XML, the XML document is not
// considered a part of the XML tree.
// Output: root is null
XElement root = xmlContacts.Parent;
Trace.WriteLine((root == null) ? "root is null" : "root is not null!");
// The following gets all XElement-children of contact element and whose name is <phone>
// Output:
// <phone type="home">206-555-0144</phone>
// <phone type="work">425-555-0145</phone>
XElement contact = xmlContacts.Element("contact");
foreach (XNode node in contact.Elements("phone"))
Trace.WriteLine(node.ToString());
Trace.WriteLine("");
// If you know that there is only one child element with a particular name, you can use
// the Element(XName) (not plural) method, which returns a single XElement. If there is
// more than one element with this name, you will get the first one
// Output:
// <phone type="home">206-555-0144</phone>
XElement phone = contact.Element("phone");
Trace.WriteLine(phone.ToString());
// To traverse upwards in the XML tree, you can use the Parent property of XElement
// Output:
//<contact>
// <name>Patrick Hines</name>
// <phone type="home">206-555-0144</phone>
// <phone type="work">425-555-0145</phone>
// <address>
// <street1>123 Main St</street1>
// <city>Mercer Island</city>
// <state>WA</state>
// <postal>68042</postal>
// </address>
// <netWorth>10</netWorth>
XElement paraentContract = phone.Parent;
Trace.WriteLine(paraentContract.ToString());
}
The following example illustrates how to manipulate XML (adding, removing, updating):
public void TestManiuplatingXML()
{
// Note: When you first create an XElement it is unparented. If you check its Parent
// property you will get back null.
// Output:
// <Name />
XElement name1 = new XElement("Name");
Trace.WriteLine(name1); // null
/* ADDING */
// You can easily add content to an XML using the AddX variety of elements
// Note: When you use Add to add a child element to a parent, LINQ to XML checks to see
// if the child element is unparented, if so, LINQ to XML parents the child element by
// setting the child's Parent property to the XElement that Add was called on.
// Output
//<Name>
// <Phone>555-1212</Phone>
// <Phone>555-4242</Phone>
//</Name>
XElement eFirstPhone = new XElement("Phone", "555-1212");
name1.Add(eFirstPhone);
name1.Add(new XElement("Phone", "555-4242"));
Trace.WriteLine(name1);
name1.Save( @"c:\Name1.xml" ); // Save() method has many overloads
// When adding an element to a parent, iLINQ to XML checks to see if the child element
// is parented. If the child is already parented, LINQ to XML clones the child element
// under subsequent parents.
XElement name2 = new XElement("Name");
name2.Add(eFirstPhone); // Same as name2.Add( new XElement("Phone", "555-1212") )
name2.Add(new XElement("Phone", "555-7878"));
Trace.WriteLine(name2);
/* REMOVING */
// You can delete any elemetn, or all child elements
name2.Elements("Phone").Remove();
Trace.WriteLine("name2 element count: " + name2.Elements().Count());
/* UPDATING */
// Create a test element
XElement name3 = new XElement("name");
name3.Add("ID", 1);
name3.Add("DOB", "1/1/2000");
name3.Add(new XElement("Phones",
new XElement("Phone", "555-1212"),
new XElement("Phone", "555-1313")));
// To update XML, you can navigate to the XElement whose contents you want to replace, and then
// use the ReplaceNodes() method
name3.Element("ID").ReplaceNodes(2);
// You can also update an XML subtree using ReplaceContent().
name3.Element("Phones").ReplaceNodes(new XElement("Phone", "222-3333"),
new XElement("Phone", "222-4444"));
// SetElement
name3.SetElementValue("DOB", "1/1/2001"); // Updates DOB element
name3.SetElementValue("Gender", "M"); // Add the Geneder element
// Display all changes
Trace.WriteLine(name3);
}
In the LINQ to XML class hierarchy, XElement and XAttribute are quite distinct and do not derive from a common base class. This is because XML attributes are not nodes in the XML tree; they are unordered name/value pairs associated with an XML element. LINQ to XML makes this distinction, but in practice, working with XAttribute is quite similar to working with XElement. The following example illustrates:
public void TestAttributes()
{
// You create an XAttribute by using functional construction the same way you would
// create an XElement with a simple type
// Output:
//<Contacts>
// <Contact>
// <Name>yazan</Name>
// <Phone Type="mobile">1234</Phone>
// <Phone Type="mobile">1234</Phone>
// </Contact>
//</Contacts>
XElement xmlContacts = new XElement("Contacts",
new XElement("Contact",
new XElement("Name", "yazan"),
new XElement("Phone", new XAttribute("Type", "mobile"), "1234"),
new XElement("Phone", new XAttribute("Type", "mobile"), "1234")
)
);
Trace.WriteLine(xmlContacts.ToString());
// SetAttributeValue is very similar to SetElementValue in functionality: If the attribute
// exists, it will be updated. If the attribute does not exist, it will be added. If the
// value of the object is null, the attribute will be deleted.
// Output
//<Contacts>
// <Contact Type="Author">
// <Name>yazan</Name>
// <Phone Type="home">1234</Phone>
// <Phone Type="mobile">1234</Phone>
// </Contact>
//</Contacts>
XElement xmlContact = xmlContacts.Element("Contact");
xmlContact.SetAttributeValue("Type", "Author"); // Add new attrribute to <Contact>
xmlContact.Element("Phone").SetAttributeValue("Type", "home");
Trace.WriteLine(xmlContacts.ToString());
// The primary method for accessing an XAttribute is by using the Attribute(XName) method
// on XElement
// Output
// Phone type: home
// Phone type: mobile
foreach (XElement phone in xmlContact.Elements("Phone"))
Trace.WriteLine("Phone type: " + (string)phone.Attribute("Type"));
// If you want to delete an attribute you can use Remove or SetAttribute(XName, object) passing
// null as the value of object. The following gets the first <Phone> element, then deletes its
// null as the value of object. The following gets the first <Phone> element, then deletes its
// Output:
//<Contact Type="Author">
// <Name>yazan</Name>
// <Phone>1234</Phone>
// <Phone Type="mobile">1234</Phone>
//</Contact>
xmlContact.Elements("Phone").First().Attributes("Type").Remove();
Trace.WriteLine(xmlContact);
}
This method describes how to use Language-Integrated Query with XML. Recall that Standard query operators show up as extension methods on any object that implements IEnumerable<T> and can be invoked like any other method. This approach is knows as the 'explicit dot notation'. In addition to the 'explicit dot notation', are query expressions such as: Where, Select, SelectMany, OrderBy and GroupBy (among others). Note that query expressions provide an ease-of-use layer on top of the underlying explicit dot notation similar to the way that foreach is an ease-of-use mechanism that consists of a call to GetEnumerator() and a while loop. The following example illustrates:
public void TestQueryingXMLWithLINQ()
{
// Load test XML data
XElement data = XElement.Load( @"C:\Projects\C30\LiniqToXml\TestData.xml" );
// Creating a single XElement with the Select standard query operator works as you would
// expect when doing a transform into XML
// Output
// <ContactsPhones>
// <phone type="home">206-555-0144</phone>
// <phone type="work">425-555-0145</phone>
// <phone type="mobile">206-555-0163</phone>
// <phone type="home">925-555-0134</phone>
// <phone type="mobile">425-555-0177</phone>
// </ContactsPhones>
XElement e1 = new XElement( "ContactsPhones",
from contact in data.Elements("contact")
select contact.Elements("phone"));
Trace.WriteLine( e1.ToString() );
// The following is similar to the above except that it allows you to create multiple
// peer elements within the select statement. To do this, the select statement uses
// an array initializer to create the sequence of children that will be placed directly
// under the 'ContactsNamesAndPhones' element
// Output
//<ContactsNamesAndPhones>
// <!--Contacts by name and phone only-->
// <contact>
// <Name>Patrick Hines</Name>
// <Phone>206-555-0144</Phone>
// </contact>
// <!--Contacts by name and phone only-->
// <contact>
// <Name>Gretchen Rivas</Name>
// <Phone>206-555-0163</Phone>
// </contact>
// <!--Contacts by name and phone only-->
// <contact>
// <Name>Scott MacDonald</Name>
// <Phone>925-555-0134</Phone>
// </contact>
//</ContactsNamesAndPhones>
XElement e2 = new XElement( "ContactsNamesAndPhones",
from contact in data.Elements("contact")
select new object[]
{ new XComment("Contacts by name and phone only"),
new XElement("contact",
new XElement("Name", contact.Element("name").Value),
new XElement("Phone", contact.Element("phone").Value) )
} );
Trace.WriteLine(e2.ToString());
// Handling optional elements. Note that <phone> elements in the test xml file are
// optional. The following shows how to handle the case when a <phone> element whose
// 'type' attribute is 'mobile' is missing
// Output
XElement e3 = new XElement("Contacts",
from contact in data.Elements("contact")
select new XElement("contact",
contact.Element("name"),
contact.Elements("phone").Any() ?
new XElement( "phone", contact.Elements("phone")) : null));
Trace.WriteLine(e3.ToString());
}
XML-specific query extensions provide you with the query operations
you would expect when working in an XML tree data structure. These XML-specific
query extensions are analogous to the XPath axes. For example, the
Elements method is equivalent to the XPath * (star)
operator.
Just as standard query operators are generally defined as extension methods on
IEnumerable<T>, the XML query operators are
generally defined as extension methods on IEnumerable<XElement>.XElementSequence
is just a container class to hold these extension methods. The following example
illustrates:
public void TestXMLQueryExtensions()
{
// Load test XML
XElement data = XElement.Load(@"C:\Projects\C30\LiniqToXml\TestData.xml");
// The Elements query operator returns the child elements for each XElement in a
// sequence of XElements (IEnumerable<XElement>).
// In the following code note that the two Elements() methods are different,
// although they do identical things. The first Elements is calling the XElement
// method Elements(), which returns an IEnumerable<XObject> containing the child
// elements in the single XElement 'contacts'. The second Elements() method is defined
// as an extension method on IEnumerable<XObject>. It returns a sequence containing
// the 'name' child elements of every XElement in the list
// Output
// <name>Patrick Hines</name>
// <name>Gretchen Rivas</name>
// <name>Scott MacDonald</name>
foreach (XElement element in data.Elements("contact").Elements("name"))
Trace.WriteLine(element);
// The Descendants and Ancestors query operators let you query down and up the XML
// tree, respectively. Descendants with no parameters gives you all the child content
// of an XElement and, in turn, each child's content down to the leaf nodes (the XML
// subtree). Optionally, you can specify an XName (Descendants(XName)) and retrieve
// all of the descendants with a specific name, or specify a type (Descendants<T>)
// and retrieve all of the descendants of a specified LINQ to XML type (for example,
// XComment).
// Ancestors and AncestorsAndSelf work similarly to Descendants and DescendantsAndSelf;
// they just go up the XML tree instead of down
// Output
// <phone type="home">206-555-0144</phone>
// <phone type="work">425-555-0145</phone>
// <phone type="mobile">206-555-0163</phone>
// <phone type="home">925-555-0134</phone>
// <phone type="mobile">425-555-0177</phone>
foreach (XElement element in data.Descendants("phone"))
Trace.WriteLine(element);
// The Attributes XML query extension is called on an IEnumerable<XElement> and returns
// a sequence of attributes (IEnumerable<XAttribute>). Optionally, you can specify an
// XName to return only attributes with that name.
// Output
// home
// work
// mobile
// home
// mobile
IEnumerable<string> PhoneTypes = data.Descendants("phone").Attributes("type").Select((XAttribute a) => a.Value);
foreach (string PhoneType in PhoneTypes)
Trace.WriteLine( PhoneType );
// Same as above but uses the 'Distinct' extension method
// Output:
// home
// work
// mobile
IEnumerable<string> DistinctPhoneTypes = data.Descendants("phone").Attributes("type").Select((XAttribute a) => a.Value).Distinct();
foreach (string PhoneType in DistinctPhoneTypes)
Trace.WriteLine( PhoneType );
}
While XSLT is accessible in LINQ to XML, the "pure" LINQ way to do transformation, which works for all types of input data, is via functional construction. Most transformations to an XML document can be thought of in terms of functionally constructing your target XML. In other words, you can "begin with the end in mind," shaping your goal XML and filling in chunks of the XML by using combinations of queries and functions as needed. The following example illustrates:
public void TestXMLTransformation()
{
// Load test XML
XElement data = XElement.Load(@"C:\Projects\C30\LiniqToXml\TestData.xml");
// In both transformations below, the output looks like this:
// <Customers>
// <Customer>
// <Name>Patrick Hines</Name>
// <PhoneNumbers>
// <phone type="home">206-555-0144</phone>
// <phone type="work">425-555-0145</phone>
// </PhoneNumbers>
// </Customer>
// <Customer>
// <Name>Gretchen Rivas</Name>
// <PhoneNumbers>
// <phone type="mobile">206-555-0163</phone>
// </PhoneNumbers>
// </Customer>
// <Customer>
// <Name>Scott MacDonald</Name>
// <PhoneNumbers>
// <phone type="home">925-555-0134</phone>
// <phone type="mobile">425-555-0177</phone>
// </PhoneNumbers>
// </Customer>
// </Customers>
// The following is an example of how LINQ to XML an be used to transform XML
// from one format to another
XElement e = new XElement("Customers",
from contact in data.Elements("contact")
select new XElement("Customer",
new XElement("Name", contact.Element("name").Value),
new XElement("PhoneNumbers",
from phone in contact.Elements("phone")
select new XElement( "phone", // Element name
(string)phone, // Element content
phone.Attribute("type"))// Attribute object
)
)
);
Trace.WriteLine(e.ToString());
// The following shows how to simplify transformations by having functions that do
// the work for portions of the tranformations. In the following statement, note
// the signature of each helper function
XElement e2 = new XElement("Customers", GetCustomers(data));
Trace.WriteLine(e2.ToString());
}
private static IEnumerable<XElement> GetCustomers(XElement data)
{
IEnumerable<XElement> ret = from contact in data.Elements("contact") select FormatCustomer(contact);
return ret;
}
private static XElement FormatCustomer(XElement contact)
{
XElement e = new XElement("Customer",
new XElement("Name", contact.Element("name").Value),
GetPhoneNumbers(contact)
);
return e;
}
private static XElement GetPhoneNumbers(XElement contact)
{
XElement ret = new XElement("PhoneNumbers",
from phone in contact.Elements("phone")
select new XElement("phone", // Element name
(string)phone, // Element content
phone.Attribute("type")));// Attribute object
return ret;
}
More details from: http://msdn2.microsoft.com/en-us/library/bb308960.aspx