LINQ - Object Integration
To see language integrated query at work, we’ll begin with a simple C# 3.0 program that uses the standard query operators to process the contents of an array:
using System;
using System.Query;
using System.Collections.Generic;
class app {
static void Main() {
string[] names = { "Burke", "Connor", "Frank",
"Everett", "Albert", "George",
"Harris", "David" };
IEnumerable<string> expr = from s in names
where s.Length == 5
orderby s
select s.ToUpper();
foreach (string item in expr)
Console.WriteLine(item);
}
}
If you were to compile and run this program, you’d see this as output:
BURKE
DAVID
FRANK
DLinq: SQL Integration
create table People (
Name nvarchar(32) primary key not null,
Age int not null,
CanCode bit not null
)
create table Orders (
OrderID nvarchar(32) primary key not null,
Customer nvarchar(32) not null,
Amount int
)
The CLR equivalent looks like this:
[Table(Name="People")]
public class Person {
[Column(DbType="nvarchar(32) not null", Id=true)]
public string Name;
[Column]
public int Age;
[Column]
public bool CanCode;
}
[Table(Name="Orders")]
public class Order {
[Column(DbType="nvarchar(32) not null", Id=true)]
public string OrderID;
[Column(DbType="nvarchar(32) not null")]
public string Customer;
[Column]
public int? Amount;
}
Note from this example that nullable columns map to nullable types in the CLR (nullable types first appeared in version 2 of the .NET Framework), and that for SQL types that don’t have a 1:1 correspondence with a CLR type (e.g., nvarchar, char, text), the original SQL type is retained in the CLR metadata.
To issue a query against a relational store, the DLinq implementation of the LINQ pattern translates the query from its expression tree form into a SQL expression and ADO.NET DbCommand object suitable for remote evaluation. For example, consider this simple query:
// establish a query context over ADO.NET sql connection
DataContext context = new DataContext(
"Initial Catalog=petdb;Integrated Security=sspi");
// grab variables that represent the remote tables that
// correspond to the Person and Order CLR types
Table<Person> custs = context.GetTable<Person>();
Table<Order> orders = context.GetTable<Order>();
// build the query
var query =
from c in custs
from o in orders
where o.Customer == c.Name
select new {
c.Name, o.OrderID, o.Amount, c.Age
};
// execute the query
foreach (var item in query)
Console.WriteLine("{0} {1} {2} {3}",
item.Name, item.OrderID, item.Amount, item.Age);
The DataContext type provides a lightweight translator that does the work of translating the standard query operators to SQL. DataContext uses the existing ADO.NET IDbConnection for accessing the store and can be initialized with either an established ADO.NET connection object or a connection string that can be used to create one.
The GetTable method provides IEnumerable-compatible variables that can be used in query expressions to represent the remote table or view. Calls to GetTable do not cause any interaction with the database – rather they represent the potential to interact with the remote table or view using query expressions. In our example above, the query does not get transmitted to the store until the program iterates over the query expression, in this case using the foreach statement in C#. When the program first iterates over the query, the DataContext machinery translates the expression tree into the following SQL statement that is sent to the store:
SELECT [t0].[Age], [t1].[Amount],
[t0].[Name], [t1].[OrderID]
FROM [Customers] AS [t0], [Orders] AS [t1]
WHERE [t1].[Customer] = [t0].[Name]
It’s important to note that by building query capability directly into the local programming language, developers get the full power of the relational model without having to statically bake the relationships into the CLR type. That stated, full blown object/relational mapping can also take advantage of this core query capability for users that want that functionality. DLinq provides object-relational mapping functionality with which the developer can define and navigate relationships between objects. You can refer to Orders as a property of the Customer class using mapping, so that you do not need explicit joins to tie the two together. External mapping files allow the mapping to be separated from the object model for richer mapping capabilities.
XLinq: XML Integration
XML elements and attributes are represented using XElement and XAttribute respectively. XElement and XAttribute support normal construction syntax, allowing developers to write XML expressions using a natural syntax:
var e = new XElement("Person",
new XAttribute("CanCode", true),
new XElement("Name", "Loren David"),
new XElement("Age", 31));
var s = e.ToString();
This corresponds to the following XML:
<Person CanCode="true">
<Name>Loren David</Name>
<Age>31</Age>
</Person>
Notice that no DOM-based factory pattern was needed to create the XML expression, and that the ToString implementation yielded the textual XML. XML elements can also be constructed from an existing XmlReader or from a string literal:
var e2 = XElement.Load(xmlReader);
var e1 = XElement.Parse(
@"<Person CanCode='true'>
<Name>Loren David</Name>
<Age>31</Age>
</Person>");
XElement also supports emitting XML using the existing XmlWriter type.
XElement dovetails with the query operators, allowing developers to write queries against non-XML information and produce XML results by constructing XElements in the body of a select clause:
var query = from p in people
where p.CanCode
select new XElement("Person",
new XAttribute("Age", p.Age),
p.Name);
This query returns a sequence of XElements. To allow XElements to be built out of the result of this kind of query, the XElement constructor allows sequences of elements to be passed as arguments directly:
var x = new XElement("People",
from p in people
where p.CanCode
select
new XElement("Person",
new XAttribute("Age", p.Age),
p.Name));
This XML expression results in the following XML:
<People>
<Person Age="11">Allen Frances</Person>
<Person Age="59">Connor Morgan</Person>
</People>
The statement above has a direct translation to Visual Basic. However, Visual Basic 9.0 also supports the use of XML literals, which allow query expressions to be expressed using a declarative XML syntax directly from Visual Basic. The previous example could be constructed with the Visual Basic statement:
Dim x = _
<People>
<%= From p In people __
Select <Person Age=<%= p.Age %>>p.Name</Person> _
Where p.CanCode _
%>
</People>
The examples so far have shown how to construct new XML values using language integrated query. The XElement and XAttribute types also simplify the extraction of information from XML structures. XElement provides accessor methods that allow query expressions to be applied to the traditional XPath axes. For example, the following query extracts just the names from the XElement shown above:
IEnumerable<string> justNames =
from e in x.Descendants("Person")
select e.Value;
//justNames = ["Allen Frances", "Connor Morgan"]
To extract structured values from the XML, we simply use an object initializer expression in our select clause:
IEnumerable<Person> persons =
from e in x.Descendants("Person")
select new Person {
Name = e.Value,
Age = (int)e.Attribute("Age")
};
Note that both XAttribute and XElement support explicit conversions to extract the text value as a primitive type. To deal with missing data, we can simply cast to a nullable type:
IEnumerable<Person> persons =
from e in x.Descendants("Person")
select new Person {
Name = e.Value,
Age = (int?)e.Attribute("Age") ?? 21
};
In this case, we use a default value of 21 when the Age attribute is missing.
Visual Basic 9.0 provides direct language support for the Elements, Attribute, and Descendants accessor methods of XElement, allowing XML-based data to be accessed using a more compact and direct syntax called Xml axis properties. We can use this functionality to write the preceding C# statement like this:
Dim persons = _
From e In x...<Person> _
Select new Person { _
.Name = e.Value, _
.Age = e.@Age.Value ?? 21 _
}
In Visual Basic x...<Person> gets all items in the Descendants collection of x with the name Person, while the expression e.@Age finds all the XAttributes with the name Age. The Value property gets the first attribute in the collection and calls the Value property on that attribute.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|