Use LINQ to SQL and LINQ to XML to retrieve and transform the data all in one query. In this case, we will select the top five customers in the Northwind database whose contact is the owner and those owners who placed orders totaling more than $10,000, then create XML containing the company name, contact name, phone number, and total amount of the orders. Finally, the results are written out to the BigSpenders.xml file:
Northwind dataContext = new Northwind(Settings.Default.NorthwindConnectionString);
// Log the generated SQL to the console
dataContext.Log = Console.Out;
var bigSpenders = new XElement("BigSpenders",
from top5 in
(
from customer in
(
from c in dataContext.Customers
// get the customers where the contact is the owner
// and they placed orders
where c.ContactTitle.Contains("Owner")
&& c.Orders.Count > 0
join orderData in
(
from c in dataContext.Customers
// get the customers where the contact is the owner
// and they placed orders
where c.ContactTitle.Contains("Owner")
&& c.Orders.Count > 0
from o in c.Orders
// get the order details
join od in dataContext.OrderDetails
on o.OrderID equals od.OrderID
select new
{
c.CompanyName,
c.CustomerID,
o.OrderID,
// have to calc order value from orderdetails
//(UnitPrice*Quantity as Total)- (Total*Discount)
// as NetOrderTotal
NetOrderTotal = (
(((double)od.UnitPrice) * od.Quantity)
(((double)od.UnitPrice) * od.Quantity) * od.Discount))
}
)
on c.CustomerID equals orderData.CustomerID
into customerOrders
select new
{
c.CompanyName,
c.ContactName,
c.Phone,
// Get the total amount spent by the customer
TotalSpend = customerOrders.Sum(order => order.NetOrderTotal)
}
)
// place focus on the customers that spent > 10000
where customer.TotalSpend > 10000
orderby customer.TotalSpend descending
// only take the top five spenders
select customer).Take(5)
)
// format the data as XML
select new XElement("Customer",
new XAttribute("companyName", top5.CompanyName),
new XAttribute("contactName", top5.ContactName),
new XAttribute("phoneNumber", top5.Phone),
new XAttribute("amountSpent", top5.TotalSpend)));
using (XmlWriter writer = XmlWriter.Create("BigSpenders.xml"))
{
bigSpenders.WriteTo(writer);
}LINQ to SQL is the part of LINQ to ADO.NET that facilitates rapid database development. It is targeted at the scenarios where you want to program almost directly against the database schema. Most of these scenarios have one-to-one correlations between strongly typed classes and database tables. If you are in more of an enterprise development scenario with lots of stored procedures and databases that have moved away from "one table equals one entity" scenarios, you would want to look into LINQ to Entities.
To use LINQ to SQL, there are two design tools to help you get started, the visual designer for LINQ to SQL in Visual Studio 2008 and the command line utility SqlMetal.exe in the SDK. You can access the visual designer by adding a new or opening an existing "LINQ to SQL Classes" item (*.dbml file) to the project, which opens the designer. Both of these help you to build out the DataContext and Entity Classes for your database that can then be used with LINQ (or other programming constructs if you wish). A DataContext is analogous to an ADO.NET Connection and Command object rolled into one. You use it to establish your connection, execute queries, or access tables directly via Entity Classes. The Northwind Data Context is a strongly typed instance of a DataContext generated by SqlMetal and is partially shown here:
public partial class Northwind : System.Data.Linq.DataContext
{
private static System.Data.Linq.Mapping.MappingSource mappingSource = new
AttributeMappingSource();
#region Extensibility Method Definitions
/// removed code for extensibility points for clarity
#endregion
static Northwind()
{
}
public Northwind(string connection) :
base(connection, mappingSource)
{
OnCreated();
}
public Northwind(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
OnCreated();
}
public Northwind(string connection, System.Data.Linq.Mapping.MappingSource
mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
public Northwind(System.Data.IDbConnection connection, System.Data.Linq.
Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
public System.Data.Linq.Table<Customers> Customers
{
get
{
return this.GetTable<Customers>();
}
}
// More Table<EntityClass> definitions, one for each table in the database
}The Entity Class definitions for the Northwind database are all present in the generated code as well, with each table having an Entity Class defined for it. The Entity Classes are indicated by the Table attribute with no parameters. This means that the name of the Entity Class matches the table name:
[Table()] public partial class Customers : INotifyPropertyChanging, INotifyPropertyChanged { #region Extensibility Method Definitions partial void OnLoaded(); partial void OnValidate(); partial void OnCreated(); partial void OnCustomerIDChanging(string value); partial void OnCustomerIDChanged(); // more extensibility methods to indicate change states for each property... #endregion public event PropertyChangingEventHandler PropertyChanging; public event PropertyChangedEventHandler PropertyChanged;
The standard property change notifications are implemented via INotifyPropertyChanging and INotifyPropertyChanged and have PropertyChanging and PropertyChanged events for conveying the change to a property. There are also a set of partial methods that will report when a specific property is modified on this Entity Class if the partial method is implemented in another partial class definition for the Entity Class. If no other partial class definition is found, the compiler will remove those notifications. Partial methods enable the declaration of a method signature in one file of a partial class declaration and the implementation of the method in another. If the signature is found but the implementation is not, the signature is removed by the compiler.
The properties in the Entity Class match up to the columns in the database via the Column attribute, where the Name value is the database column name and the Storage value is the internal storage for the class of the data. Events for the property changes are wired into the setter for the property:
[Column(Name="Company Name", Storage="_CompanyName", DbType="NVarChar(40) NOT
NULL", CanBeNull=false)]
public string CompanyName
{
get
{
return this._CompanyName;
}
set
{
if ((this._CompanyName != value))
{
this.OnCompanyNameChanging(value);
this.SendPropertyChanging();
this._CompanyName = value;
this.SendPropertyChanged("CompanyName");
this.OnCompanyNameChanged();
}
}
}For a one-to-many child relationship, an EntitySet<T> of the child Entity Class is declared with an Association attribute. The Association attribute specifies the relationship information between the parent and child Entity Classes, as shown here for the Orders property on Customer:
[Association(Name="Orders_FK00", Storage="_Orders", ThisKey="CustomerID",
OtherKey="CustomerID", DeleteRule="NO ACTION")]
public EntitySet<Orders> Orders
{
get
{
return this._Orders;
}
set
{
this._Orders.Assign(value);
}
}LINQ to SQL covers much more than what has been shown here; we encourage you to investigate it more, but let's now see the other data domain we are dealing with: LINQ to XML.
LINQ to XML is not only how you perform queries against XML; it is a more developer-friendly way to work with XML. One of the main classes in LINQ to XML is XElement.XElement allows you to create XML in a manner that more closely resembles the structure of the XML itself. This may not seem like a big deal, but when you can see the XML taking shape in your code, it makes it easier to know where you are. (Ever forget which XmlWriter.WriteEndElement you were on? We have!) You can get more details and examples about using XElement in Chapter 15, so we won't go much further into it here, but as you can see, it is very easy to build up XML in a query.
The first part of the query deals with setting up the main XML element "BigSpenders", getting the initial set of customers where the contact is the owner:
var bigSpenders = new XElement("BigSpenders",
from top5 in
(
(from customer in
(
from c in dataContext.Customers
// get the customers where the contact is the owner
// and they placed orders
where c.ContactTitle.Contains("Owner")
&& c.Orders.Count > 0The middle of the query deals with joining the order and order detail information with the customer information to get the NetOrderTotal for the order. It also creates order data containing that value and the customer and order ids and the customer name. We need the NetOrderTotal in the last part of the query, so stay tuned!
join orderData in ( from c in dataContext.Customers // get the customers where the contact is the owner // and they placed orders where c.ContactTitle.Contains("Owner") && c.Orders.Count > 0 from o in c.Orders // get the order details join od in dataContext.OrderDetails on o.OrderID equals od.OrderID select new { c.CompanyName, c.CustomerID, o.OrderID, // have to calc order value from orderdetails //(UnitPrice*Quantity as Total) (Total*Discount) // as NetOrderTotalNetOrderTotal = ((((double)od.UnitPrice) * od.Quantity) -((((double)od.UnitPrice) * od.Quantity) * od.Discount))} ) on c.CustomerID equals orderData.CustomerID into customerOrders
The last part of the query determines the TotalSpend for that customer across all orders using the Sum function on NetOrderTotal for the generated customerOrders collection. The last criteria evaluated is that only the top five customers with a TotalSpend value > 10000 are selected by using the Take function. Take is the equivalent to TOP in SQL. The records are then used to construct one inner Customer element with attributes that nest inside the BigSpenders root element started in the first part of the query:
select new
{
c.CompanyName,
c.ContactName,
c.Phone,
// Get the total amount spent by the customer
TotalSpend = customerOrders.Sum(order => order. NetOrderTotal)
}
)
// only worry about customers that spent > 10000
where customer.TotalSpend > 10000
orderby customer.TotalSpend descending
// only take the top 5 spenders
select customer).Take(5)
)
// format the data as XML
select new XElement("Customer",
new XAttribute("companyName", top5.CompanyName),
new XAttribute("contactName", top5.ContactName),
new XAttribute("phoneNumber", top5.Phone),
new XAttribute("amountSpent", top5.TotalSpend)));Tip
It is much easier to build large-nested queries as individual queries first and then put them together once you are sure the inner query is working.
At this point, for all of the code here, nothing has happened yet. That's right, until the query is accessed, nothing happens through the magic of deferred execution. LINQ has constructed a query expression, but nothing has talked to the database; there is no XML in memory, nada. Once the WriteTo method is called on the bigSpenders query expression, then the query is evaluated by LINQ to SQL, and the XML is constructed. The WriteTo method writes out the constructed XML to the XmlWriter provided, and we are done:
using (XmlWriter writer = XmlWriter.Create("BigSpenders.xml"))
{
bigSpenders.WriteTo(writer);
}If you are interested in what that SQL will look like, connect the DataContext.Log property to a TextWriter (like the console):
// Log the generated SQL to the console dataContext.Log = Console.Out;
This query generates SQL that looks like this:
Generated SQL for query - output via DataContext.Log SELECT [t10].[CompanyName], [t10].[ContactName], [t10].[Phone], [t10].[TotalSpend] FROM ( SELECT TOP (5) [t0].[Company Name] AS [CompanyName], [t0].[Contact Name] AS [ContactName], [t0].[Phone], [t9].[value] AS [TotalSpend] FROM [Customers] AS [t0] OUTER APPLY ( SELECT COUNT(*) AS [value] FROM [Orders] AS [t1] WHERE [t1].[Customer ID] = [t0].[Customer ID] ) AS [t2] OUTER APPLY ( SELECT SUM([t8].[value]) AS [value] FROM ( SELECT [t3].[Customer ID], [t6].[Order ID], ([t7].[Unit Price] * (CONVERT(Decimal(29,4),[t7].[Quantity]))) - ([t7].[Unit Price] * (CONVERT(Decimal(29,4),[t7].[Quantity])) * (CONVERT(Decimal(29,4),[t7].[Discount]))) AS [value], [t7].[Order ID] AS [Order ID2], [t3].[Contact Title] AS [ContactTitle], [t5].[value] AS [value2], [t6].[Customer ID] AS [CustomerID] FROM [Customers] AS [t3] OUTER APPLY ( SELECT COUNT(*) AS [value] FROM [Orders] AS [t4] WHERE [t4].[Customer ID] = [t3].[Customer ID] ) AS [t5] CROSS JOIN [Orders] AS [t6] CROSS JOIN [Order Details] AS [t7] ) AS [t8] WHERE ([t0].[Customer ID] = [t8].[Customer ID]) AND ([t8].[Order ID] = [ t8].[Order ID2]) AND ([t8].[ContactTitle] LIKE @p0) AND ([t8].[value2] > @p1) AN D ([t8].[CustomerID] = [t8].[Customer ID]) ) AS [t9] WHERE ([t9].[value] > @p2) AND ([t0].[Contact Title] LIKE @p3) AND ([t2].[va lue] > @p4) ORDER BY [t9].[value] DESC ) AS [t10] ORDER BY [t10].[TotalSpend] DESC -- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [%Owner%] -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0] -- @p2: Input Decimal (Size = 0; Prec = 29; Scale = 4) [10000] -- @p3: Input String (Size = 0; Prec = 0; Scale = 0) [%Owner%] -- @p4: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0] -- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.20706.1
<BigSpenders> <Customer companyName="Folk och fa HB" contactName="Maria Larsson" phoneNumber="0695-34 67 21" amountSpent="39805.162472039461" /> <Customer companyName="White Clover Markets" contactName="Karl Jablonski" phoneNumber="(206) 555-4112" amountSpent="35957.604972146451" /> <Customer companyName="Bon app'" contactName="Laurence Lebihan" phoneNumber="91.24.45.40" amountSpent="22311.577472746558" /> <Customer companyName="LINO-Delicateses" contactName="Felipe Izquierdo" phoneNumber="(8) 34-56-12" amountSpent="20458.544984650609" /> <Customer companyName="Simons bistro" contactName="Jytte Petersen" phoneNumber="31 12 34 56" amountSpent="18978.777493602414" /> </BigSpenders>
Get C# 3.0 Cookbook, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
