Chapter 15
Joining Database Tables with LINQ Queries
In This Chapter
Defining Joins with LINQ to DataSet
Defining Joins with LINQ to SQL
Querying Views with LINQ
Databinding with LINQ to SQL
“In dreams begins responsibility.”
—William Butler Yeats
There is a small likelihood that you will read this chapter title and reflect that it is similar to Chapter 11, “Joining Query Results.” In fact, this chapter and Chapter 11 (also on joins) do share fundamental underpinnings; both chapters cover LINQ joins. The difference is that Chapter 11 covered joins for custom classes, and this chapter covers joins for ADO.NET objects and entity classes. There are differences.
For example, this chapter looks at joins for LINQ to DataSets and joins for LINQ to SQL. To that end, we have to write the joins to deal with nullable fields, how to provide a default type for the DefaultIfEmpty argument of a left join, LINQ to SQL for Views, and databinding.
Rather than assume you could very quickly make the leap from LINQ for objects to LINQ for data by mentally filling in these gaps, this chapter fills in the gaps for you, offers additional sample types, and includes new material for views and databinding. (Check out the section on “Defining Joins with LINQ to SQL,” which provides an example based on the sample Pubs database. This section demonstrates how to convert programmer-unfriendly column names to more readable Entity property names using the Name argument of the ColumnAttribute.)
Defining Joins with LINQ to DataSet
LINQ for data is supported in two basic ways. You can write LINQ to ADO.NET code that provides for querying tables in DataSets, and you can define entities and use LINQ to SQL to query entities—classes that map to tables. Although you can choose either approach as it suits your needs, in general, consider using LINQ to DataSets only if you are incorporating LINQ into existing code that uses ADO.NET DataSets and tables. (Coverage of that style of joins is covered in this section.) If you are writing new code, consider using LINQ to SQL and object-relational maps entities mapped to SQL tables. (Joins for LINQ to SQL are covered beginning in the section titled “Defining Joins with LINQ to SQL.”)
All the join types, such as cross joins and group joins, are not covered for both types of LINQ for data, but together most of the join types are covered in this chapter. After you go over the basic and most common join types in this chapter, you will be able to extrapolate the less common right and cross joins from the material in Chapter 11.
Coding Equijoins
Fundamentally, an equijoin for LINQ to DataSets is the same as equijoins for LINQ to objects. The key difference is that you are dealing with DataTable and DataRow objects and you will have to add the code to query the table and express the join by requesting a DataRow’s Field value. (An explanation of how to query a DataTable and request Field data is covered in Chapter 13, “Querying Relational Data with LINQ.”) The code in Listing 15.1 demonstrates how to build on the information in Chapter 13 to express an equijoin for LINQ to DataSets.
Listing 15.1 Defining an Equijoin Using the join… in…on… equals Clause for LINQ to DataSets
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace LinqToDataSetEquijoin
{
class Program
{
static void Main(string[] args)
{
const string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=” +
“”C:\Books\Sams\LINQ\Northwind\northwnd.mdf”;” +
“;Integrated Security=True;Connect Timeout=30;User Instance=True”;
DataSet dataset = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
const string SQL = “SELECT * FROM Products;SELECT * FROM SUPPLIERS”;
SqlDataAdapter adapter = new SqlDataAdapter(SQL, connection);
adapter.Fill(dataset);
}
DataTable products = dataset.Tables[0];
DataTable suppliers = dataset.Tables[1];
var inventory = from product in products.AsEnumerable()
join supplier in suppliers.AsEnumerable()
on product.Field<int>(“SupplierID”)
equals supplier.Field<int>(“SupplierID”)
orderby product.Field<string>(“ProductName”)
select new
{
Company=supplier.Field<string>(“CompanyName”),
City=supplier.Field<string>(“City”),
Phone=supplier.Field<string>(“Phone”),
Product=product.Field<string>(“ProductName”),
Price=product.Field<decimal>(“UnitPrice”),
InStock=product.Field<Int16>(“UnitsInStock”),
Discontinued=product.Field<bool>(“Discontinued”)
};
string line = new string(‘-’, 40);
foreach(var item in inventory)
{
Console.WriteLine(“Company: {0}”, item.Company);
Console.WriteLine(“City: {0}”, item.City);
Console.WriteLine(“Phone: {0}”, item.Phone);
Console.WriteLine(“Product: {0}”, item.Product);
Console.WriteLine(“Price: {0:C}”, item.Price);
Console.WriteLine(“Quantity on hand: {0}”, item.InStock);
Console.WriteLine(“Discontinued: {0}”, item.Discontinued);
Console.WriteLine(line);
Console.WriteLine();
}
Console.ReadLine();
}
}
}
In Listing 15.1, both queries are sent to the Northwind database via ADO.NET as a single, semicolon-delimited string. The equijoin is constructed by defining the first range element, product, from the products DataTable using the AsEnumerable extension method, and the second join is supplier from the suppliers DataTable using AsEnumerable to facilitate querying. The equijoin is product.SupplierID equals supplier.SupplierID. The SupplierID is extracted from the range values using the Field extension method properties and the equals clause. The rest of the code is boilerplate code that demonstrates a complete functional example.
Coding Nonequijoins
A nonequijoin is a join that uses inequality. These joins have to be expressed using a where clause and can include one or more predicates. Listing 15.2 is an excerpt that can be plugged into Listing 15.1 (with minor changes to display the output). Listing 15.2 uses a where clause and an equality predicate (==) based on the SupplierID and looks for discontinued products in the second predicate for where.
Listing 15.2 A Nonequijoin Based on Two Predicates and a where Clause
var discontinued = from product in products.AsEnumerable()
from supplier in suppliers.AsEnumerable()
where product.Field<int>(“SupplierID”)
== supplier.Field<int>(“SupplierID”)
&&
product.Field<bool>(“Discontinued”) == true
select new
{
Company=supplier.Field<string>(“CompanyName”),
City=supplier.Field<string>(“City”),
Phone=supplier.Field<string>(“Phone”),
Product=product.Field<string>(“ProductName”),
Price=product.Field<decimal>(“UnitPrice”),
InStock=product.Field<Int16>(“UnitsInStock”),
Discontinued=product.Field<bool>(“Discontinued”)
};
A classic nonequijoin using not equals (!= in LINQ and <> in SQL) are seldom used but can be employed to define a self-join. Listing 15.3 shows a self-join on the Northwind.Products table based on ProductName equality and SupplierID inequality. (You could use a key in a real-world example if the key for products were not unique as it is in the Northwind database.)
Listing 15.3 A Nonequijoin Used to Contrive a Self-Join to See If There Are Two Suppliers Providing the Same Product (By Name) at Different Prices
var productsFromMultipleSuppliers =
from product1 in products.AsEnumerable()
from product2 in products.AsEnumerable()
where product1.Field<string>(“ProductName”) ==
product2.Field<string>(“ProductName”)
&& product1.Field<int>(“SupplierID”) !=
product2.Field<int>(“SupplierID”)
select new
{
ProductName = product1.Field<string>(“ProductName”),
Supplier1 = product1.Field<int>(“SupplierID”),
Supplier1Price = product1.Field<decimal>(“UnitPrice”),
Supplier2 = product2.Field<int>(“SupplierID”),
Supplier2Price = product1.Field<decimal>(“UnitPrice”)
};
In a good relational database with constraints and unique keys, duplicates by key are less likely to occur. Self-joins are usually more prevalent when porting old, legacy database from nonrelational systems where the data technology is not enforcing uniqueness and data integrity.
Defining a Left Join and a Word about Right Joins
The left join and right join are not supported by keywords in LINQ. (And, according to a group blog from the VB team—http://blogs.msdn.com/vbteam/archive/2008/01/31/converting-sql-to-linq-part-8-left-right-outer-join-bill-horst.aspx—these features are not planned in current or near-future releases.) However, as demonstrated in Chapter 11, the left join (or left outer join) can be constructed by flattening the group join with the DefaultIfEmpty extension method.
In this section, a left join for LINQ to DataSets is demonstrated followed by a brief discussion on the infrequently used right join and how that can be constructed as well.
Defining a Left Outer Join with LINQ to DataSets
As demonstrated in Chapter 11, a left outer join is constructed with two sequences: a group join and the DefaultIfEmpty extension method. To implement a left join in LINQ to DataSets, you need to address the minor challenge of handling the DefaultIfEmpty method.
In short, the left join needs an empty child DataRow in the event there is no matching child row. You can handle DefaultIfEmpty by calling this method with the DataTable.NewRow method and defining the project—in select new—to use nullable types for child data. The latter step (shown in Listing 15.4) is necessary because empty new rows for missing children will be initialized with nulls.
Listing 15.4 Defining a Left Outer Join in LINQ to DataSets Depends on Calling DataTable.NewRow to Initialize Empty, Missing Child Data Rows and Nullable Types in the Projection Created in the select Clause
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace LinqToDataSetLeftJoin
{
class Program
{
static void Main(string[] args)
{
const string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=” +
“”C:\Books\Sams\LINQ\Northwind\northwnd.mdf”;” +
“;Integrated Security=True;Connect Timeout=30;User Instance=True”;
DataSet dataset = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
const string SQL = “SELECT * FROM CUSTOMERS;SELECT * FROM ORDERS”;
SqlDataAdapter adapter = new SqlDataAdapter(SQL, connection);
adapter.Fill(dataset);
}
DataTable customers = dataset.Tables[0];
DataTable orders = dataset.Tables[1];
var customersWithoutOrders = from customer in customers.AsEnumerable()
join order in orders.AsEnumerable()
on customer.Field<string>(“CustomerID”) equals
order.Field<string>(“CustomerID”) into children
from child in children.DefaultIfEmpty(orders.NewRow())
let OrderID = child.Field<int?>(“OrderID”)
where OrderID == null
select new
{
Company=customer.Field<string>(“CompanyName”),
City=customer.Field<string>(“City”),
Phone=customer.Field<string>(“Phone”),
OrderID=child.Field<int?>(“OrderID”),
OrderDate=child.Field<DateTime?>(“OrderDate”),
RequiredDate=child.Field<DateTime?>(“RequiredDate”),
ShipCity=child.Field<string>(“ShipCity”)
};
string line = new string(‘-’, 40);
foreach(var item in customersWithoutOrders)
{
Console.WriteLine(“Company: {0}”, item.Company);
Console.WriteLine(“City: {0}”, item.City);
Console.WriteLine(“Phone: {0}”, item.Phone);
Console.WriteLine(“Order ID: {0}”, item.OrderID);
Console.WriteLine(“Order Date: {0}”, item.OrderDate);
Console.WriteLine(“Required Date: {0}”, item.RequiredDate);
Console.WriteLine(“Ship to: {0}”, item.ShipCity);
Console.WriteLine(line);
Console.WriteLine();
}
Console.ReadLine();
}
}
}
The into… from…in clause flattens a group join into a left join. If a right DataRow isn’t available, invoke NewRow to initialize the empty, or missing, child row, and define the projection to support nullable types for the missing child row data (shown in the select new clause in Listing 15.4). For instance, child.Field<int?>(“OrderID”) makes it permissible to initialize the projection without an actual OrderID in the OrderDetails data row.
Considering Right Joins
The right outer join is not supported in LINQ to DataSets or LINQ to SQL. A right outer join returns all of the child rows and only the parent rows that have correlated data. A right join will find children with parents and orphaned children. (Orphaned children occur when the database designer or SQL author forgets to cascade deletes.)
You can construct a right join by reversing the order of the range arguments in a LINQ query. For example, if you switch the order of the range variables customer and order in Listing 15.4, the result is a left join from Orders to Customers, which yields the same result as a right join. The implication is also that the grouping behavior occurs on Customers and not Orders.
Right joins are pretty rare. Again, you are likely to see orphaned children in legacy data where cascaded deletes were unsupported. If you remember to enable cascaded deletes in your SQL databases and literally delete children with LINQ, orphaned data should not exist. Listing 15.5 shows a RIGHT OUTER JOIN in SQL, and Listing 15.6 shows an implicit right join constructed with LINQ by reversing the order of the range and sequence values from Listing 15.4.
Listing 15.5 A RIGHT OUTER JOIN in SQL
SELECT C.CustomerID, C.CompanyName, C.ContactName, C.ContactTitle,
C.Address, C.City,
C.Region, C.PostalCode, C.Country, C.Phone, C.Fax, O.OrderID,
O.CustomerID AS Expr1, O.EmployeeID, O.OrderDate, O.RequiredDate, O.ShippedDate,
O.ShipVia,
O.Freight, O.ShipName, O.ShipAddress, O.ShipCity, O.ShipRegion, O.ShipPostalCode,
O.ShipCountry
FROM Customers AS C RIGHT OUTER JOIN Orders AS O ON C.CustomerID = O.CustomerID
Listing 15.6 A Modified Version of Listing 15.4 That Reverses the Order of the Customers and Orders Clauses and Groups on Customers Rather Than Orders, Yielding the Same Result as a RIGHT OUTER JOIN
var orphanedOrders = from order in orders.AsEnumerable()
join customer in customers.AsEnumerable()
on order.Field<string>(“CustomerID”) equals
customer.Field<string>(“CustomerID”)
into parent
from p in parent.DefaultIfEmpty(customers.NewRow())
select new
{
CustomerID = p.Field<string>(“CustomerID”),
Company = p.Field<string>(“CompanyName”),
City = p.Field<string>(“City”),
Phone = p.Field<string>(“Phone”),
OrderID = order.Field<int?>(“OrderID”),
OrderDate = order.Field<DateTime?>(“OrderDate”),
RequiredDate = order.Field<DateTime?>(“RequiredDate”),
ShipCity = order.Field<string>(“ShipCity”)
};
The key is that well-constructed databases shouldn’t create orphans. For example, if a customer is deleted, the customer’s orders should be deleted too. Legacy database (and data) often don’t support features found in modern relational systems; however, clearly it is possible to get a result that looks like orphans exist as demonstrated by primary key strings mismatched by case.
Defining Joins with LINQ to SQL
If you have a lot of existing ADO.NET code that uses DataSets and DataTables, clearly you can use LINQ without adding a lot of entities mapped to tables. If you are defining new systems, consider defining object-relational mappings, mapping entity classes to database tables. The result is much cleaner code, simpler LINQ expressions, and classes that are independent of ADO.NET types.
This part of the chapter demonstrates the most common joins, including the equijoin, group join, and left join for LINQ to SQL. Clearly, you can implement the other joins shown earlier in this chapter and in Chapter 11, but it isn’t necessary to cover each kind of join again once you have a basic understanding of the differences between LINQ to SQL joins and joins on other kinds of objects.
Coding Equijoins
The equijoin is the most common join. Equijoins are also directly supported. Because that is the case, this section covers an equijoin and a new database is included to keep the material fresh as well as to demonstrate how to correct poorly named database columns.
The example in Listing 15.7 demonstrates an equijoin on the pubs database. Pubs is a sample database that contains publishers, authors, books, and royalty information but uses abbreviations to excess. In Listing 15.7, the abbreviated column names are corrected with the Name argument to the ColumnAttribute and null fields are handled with nullable types in the entity definition. (Nullable types for entities are introduced in Chapter 16, “Updating Anonymous Relational Data.”)
Listing 15.7 An Equijoin on the Titles Table, the TitleAuthor Linking Table—to Support Multiple Book Authors—and the Authors Table of the Pubs Sample Database
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqToSqlEquijoin
{
class Program
{
static void Main(string[] args)
{
Publishers publishers = new Publishers();
var titles = from title in publishers.Titles
join titleAuthor in publishers.TitleAuthors on
title.TitleID equals titleAuthor.TitleID
join author in publishers.Authors on
titleAuthor.AuthorID equals author.AuthorID
select new
{
Author=author.FirstName + ‘ ‘ + author.LastName,
Book=title.BookTitle
};
Array.ForEach(titles.ToArray(), b=>
Console.WriteLine(“Author: {0}, Book: {1}”, b.Author, b.Book));
Console.ReadLine();
}
}
public class Publishers : DataContext
{
private static readonly string connectionString =
“Data Source=BUTLER;Initial Catalog=pubs;Integrated Security=True”;
public Publishers() : base(connectionString)
{
Log = Console.Out;
}
public Table<Author> Authors
{
get{ return this.GetTable<Author>(); }
}
public Table<TitleAuthor> TitleAuthors
{
get{ return this.GetTable<TitleAuthor>(); }
}
public Table<Title> Titles
{
get{ return this.GetTable<Title>(); }
}
}
[Table(Name=”authors”)]
public class Author
{
[Column(Name=”au_id”, IsPrimaryKey=true)]
public int AuthorID{ get; set; }
[Column(Name=”au_lname”)]
public string LastName{ get; set; }
[Column(Name=”au_fname”)]
public string FirstName{ get; set; }
[Column(Name=”phone”)]
public string Phone{ get; set; }
[Column(Name=”address”)]
public string Address{ get; set; }
[Column(Name=”city”)]
public string City{ get; set; }
[Column(Name=”state”)]
public string State{ get; set; }
[Column(Name=”zip”)]
public string ZipCode{ get; set; }
[Column(Name=”contract”)]
public bool? Contract{ get; set; }
}
[Table(Name=”titleauthor”)]
public class TitleAuthor
{
[Column(Name=”au_id”)]
public int? AuthorID{ get; set; }
[Column(Name=”title_id”)]
public int? TitleID{ get; set; }
[Column(Name=”au_ord”)]
public Int16? AuthorOrder{ get; set; }
[Column(Name=”royaltyper”)]
public int? RoyaltyPercentage{ get; set; }
}
[Table(Name=”titles”)]
public class Title
{
[Column(Name=”title_id”, IsPrimaryKey=true)]
public int? TitleID{ get; set; }
[Column(Name=”title”)]
public string BookTitle{ get; set; }
[Column(Name=”type”)]
public string Type{ get; set; }
[Column(Name=”pub_id”)]
public string PublisherID{ get; set; }
[Column(Name=”price”)]
public decimal? Price{ get; set; }
[Column(Name=”advance”)]
public decimal? Advance{ get; set; }
[Column(Name=”royalty”)]
public int? Royalty{ get; set; }
[Column(Name=”ytd_sales”)]
public int? YearToDateSales{ get; set; }
[Column(Name=”notes”)]
public string Notes{ get; set; }
[Column(Name=”pubdate”)]
public DateTime? PublicationDate{ get; set; }
}
}
The LINQ query in Listing 15.7 clearly demonstrates that you can define an equijoin on multiple tables using additional join clauses. (Pubs uses a linking table TitleAuthor to map multiple authors to a single title.) The projection defines a composite field Author based on FirstName and LastName and nullable types for value types in the entity classes. (Nullable types are defined with the question mark (?) after the data type.)
Poorly named columns like ytd_sales are corrected by using the Name argument to the ColumnAttribute. For example, ytd_sales is mapped to YearToDateSales in the Title class.
Implementing the Group Join
There is actually a GroupJoin extension method. In VB .NET the GroupJoin is explicit. In C#, the group join is emitted to (Microsoft Intermediate Language) MSIL when you use the join…on…equals…into keywords collectively.
A group join is a master-detail relationship where for each element in the master sequence there is a contained association that includes the child objects. Listing 15.8 shows a group join on the Northwind Orders and Order Details tables, and a nested array displaying each element. Listing 15.9 shows the disassembled code showing how the LINQ statement is expressed as an extension method, clearly showing the GroupJoin (and how much more complicated it would be to use extension methods instead of queries had Microsoft stopped at extension methods).
Listing 15.8 A Group Join Uses the join… on…equals… into and the GroupJoin Extension Method Is Emitted
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqToSqlGroupJoin
{
class Program
{
static void Main(string[] args)
{
Northwind northwind = new Northwind();
var orderInformation = from order in northwind.Orders
join detail in northwind.Details
on order.OrderID equals detail.OrderID
into children
select new
{
CustomerID = order.CustomerID,
OrderDate = order.OrderDate,
RequiredDate = order.RequiredDate,
Details = children
};
string line = new string(‘-’, 40);
Array.ForEach(orderInformation.ToArray(), r =>
{
Console.WriteLine(“Customer ID: {0}”, r.CustomerID);
Console.WriteLine(“Order Date: {0}”, r.OrderDate
.GetValueOrDefault().ToShortDateString());
Console.WriteLine(“Required Date: {0}”, r.RequiredDate
.GetValueOrDefault().ToShortDateString());
Console.WriteLine(“-- -- -- -- -Order Details-- -- -- -- -”);
Array.ForEach(r.Details.ToArray(), d =>
{
Console.WriteLine(“Product ID: {0}”, d.ProductID);
Console.WriteLine(“Unit Price: {0}”, d.UnitPrice);
Console.WriteLine(“Quantity: {0}”, d.Quantity);
Console.WriteLine();
});
Console.WriteLine(line);
Console.WriteLine();
});
Console.ReadLine();
}
}
public class Northwind : DataContext
{
private static readonly string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=”C:\Books\Sams\” +
“LINQ\Northwind\northwnd.mdf”;” +
“Integrated Security=True;Connect Timeout=30;User Instance=True”;
//”Data Source=.\SQLEXPRESS;AttachDbFilename=c:\temp\northwnd.mdf;” +
//”Integrated Security=True;Connect Timeout=30;User Instance=True”;
public Northwind()
: base(connectionString)
{
Log = Console.Out;
}
public Table<Order> Orders
{
get{ return this.GetTable<Order>(); }
}
public Table<OrderDetail> Details
{
get{ return GetTable<OrderDetail>(); }
}
}
[Table(Name = “dbo.Order Details”)]
public partial class OrderDetail
{
private int _OrderID;
private int _ProductID;
private decimal _UnitPrice;
private short _Quantity;
private float _Discount;
public OrderDetail()
{
}
[Column(Storage = “_OrderID”, DbType = “Int NOT NULL”, IsPrimaryKey = true)]
public int OrderID
{
get
{
return this._OrderID;
}
set
{
this._OrderID = value;
}
}
[Column(Storage = “_ProductID”, DbType = “Int NOT NULL”, IsPrimaryKey = true)]
public int ProductID
{
get
{
return this._ProductID;
}
set
{
this._ProductID = value;
}
}
[Column(Storage = “_UnitPrice”, DbType = “Money NOT NULL”)]
public decimal UnitPrice
{
get
{
return this._UnitPrice;
}
set
{
this._UnitPrice = value;
}
}
[Column(Storage = “_Quantity”, DbType = “SmallInt NOT NULL”)]
public short Quantity
{
get
{
return this._Quantity;
}
set
{
this._Quantity = value;
}
}
[Column(Storage = “_Discount”, DbType = “Real NOT NULL”)]
public float Discount
{
get
{
return this._Discount;
}
set
{
this._Discount = value;
}
}
}
[Table(Name = “dbo.Orders”)]
public partial class Order
{
private int _OrderID;
private string _CustomerID;
private System.Nullable<int> _EmployeeID;
private System.Nullable<System.DateTime> _OrderDate;
private System.Nullable<System.DateTime> _RequiredDate;
private System.Nullable<System.DateTime> _ShippedDate;
private System.Nullable<int> _ShipVia;
private System.Nullable<decimal> _Freight;
private string _ShipName;
private string _ShipAddress;
private string _ShipCity;
private string _ShipRegion;
private string _ShipPostalCode;
private string _ShipCountry;
public Order()
{
}
[Column(Storage = “_OrderID”, AutoSync = AutoSync.OnInsert,
DbType = “Int NOT NULL IDENTITY”, IsPrimaryKey = true, IsDbGenerated = true)]
public int OrderID
{
get
{
return this._OrderID;
}
set
{
this._OrderID = value;
}
}
[Column(Storage = “_CustomerID”, DbType = “NChar(5)”)]
public string CustomerID
{
get
{
return this._CustomerID;
}
set
{
this._CustomerID = value;
}
}
[Column(Storage = “_EmployeeID”, DbType = “Int”)]
public System.Nullable<int> EmployeeID
{
get
{
return this._EmployeeID;
}
set
{
this._EmployeeID = value;
}
}
[Column(Storage = “_OrderDate”, DbType = “DateTime”)]
public System.Nullable<System.DateTime> OrderDate
{
get
{
return this._OrderDate;
}
set
{
this._OrderDate = value;
}
}
[Column(Storage = “_RequiredDate”, DbType = “DateTime”)]
public System.Nullable<System.DateTime> RequiredDate
{
get
{
return this._RequiredDate;
}
set
{
this._RequiredDate = value;
}
}
[Column(Storage = “_ShippedDate”, DbType = “DateTime”)]
public System.Nullable<System.DateTime> ShippedDate
{
get
{
return this._ShippedDate;
}
set
{
this._ShippedDate = value;
}
}
[Column(Storage = “_ShipVia”, DbType = “Int”)]
public System.Nullable<int> ShipVia
{
get
{
return this._ShipVia;
}
set
{
this._ShipVia = value;
}
}
[Column(Storage = “_Freight”, DbType = “Money”)]
public System.Nullable<decimal> Freight
{
get
{
return this._Freight;
}
set
{
this._Freight = value;
}
}
[Column(Storage = “_ShipName”, DbType = “NVarChar(40)”)]
public string ShipName
{
get
{
return this._ShipName;
}
set
{
this._ShipName = value;
}
}
[Column(Storage = “_ShipAddress”, DbType = “NVarChar(60)”)]
public string ShipAddress
{
get
{
return this._ShipAddress;
}
set
{
this._ShipAddress = value;
}
}
[Column(Storage = “_ShipCity”, DbType = “NVarChar(15)”)]
public string ShipCity
{
get
{
return this._ShipCity;
}
set
{
this._ShipCity = value;
}
}
[Column(Storage = “_ShipRegion”, DbType = “NVarChar(15)”)]
public string ShipRegion
{
get
{
return this._ShipRegion;
}
set
{
this._ShipRegion = value;
}
}
[Column(Storage = “_ShipPostalCode”, DbType = “NVarChar(10)”)]
public string ShipPostalCode
{
get
{
return this._ShipPostalCode;
}
set
{
this._ShipPostalCode = value;
}
}
[Column(Storage = “_ShipCountry”, DbType = “NVarChar(15)”)]
public string ShipCountry
{
get
{
return this._ShipCountry;
}
set
{
this._ShipCountry = value;
}
}
}
}
Listing 15.9 The Disassembled Code (from Reflector) Showing the Very Long Chain of Extension Methods, Including the GroupJoin That Ultimately Provides the Behavior of the LINQ Query
private static void Main(string[] args)
{
ParameterExpression CS$0$0000;
ParameterExpression CS$0$0002;
Northwind northwind = new Northwind();
var orderInformation = northwind.Orders.GroupJoin(northwind.Details,
Expression.Lambda<Func<Order, int>>(Expression.Property(CS$0$0000 =
Expression.Parameter(typeof(Order), “order”), (MethodInfo)
methodof(Order.get_OrderID)), new ParameterExpression[] { CS$0$0000 }),
Expression.Lambda<Func<OrderDetail, int>>(Expression.Property(CS$0$0000 =
Expression.Parameter(typeof(OrderDetail), “detail”), (MethodInfo)
methodof(OrderDetail.get_OrderID)), new ParameterExpression[] { CS$0$0000 }),
Expression.Lambda(Expression.New((ConstructorInfo) methodof
→(<>f__AnonymousType0<string,
DateTime?, DateTime?, IEnumerable<OrderDetail>>..ctor,
→<>f__AnonymousType0<string,
DateTime?, DateTime?, IEnumerable<OrderDetail>>), new Expression[] {
Expression.Property(CS$0$0000 = Expression.Parameter(typeof(Order), “order”),
(MethodInfo) methodof(Order.get_CustomerID)), Expression.Property(CS$0$0000,
(MethodInfo) methodof(Order.get_OrderDate)), Expression.Property(CS$0$0000,
(MethodInfo) methodof(Order.get_RequiredDate)), CS$0$0002 =
Expression.Parameter(typeof(IEnumerable<OrderDetail>), “children”) },
→new MethodInfo[]
{ (MethodInfo) methodof(<>f__AnonymousType0<string, DateTime?, DateTime?,
IEnumerable<OrderDetail>>.get_CustomerID, <>f__AnonymousType0<string,
→DateTime?,
DateTime?, IEnumerable<OrderDetail>>), (MethodInfo)
methodof(<>f__AnonymousType0<string, DateTime?, DateTime?,
IEnumerable<OrderDetail>>.get_OrderDate, <>f__AnonymousType0<string, DateTime?,
DateTime?, IEnumerable<OrderDetail>>), (MethodInfo)
methodof(<>f__AnonymousType0<string, DateTime?, DateTime?,
IEnumerable<OrderDetail>>.get_RequiredDate, <>f__AnonymousType0<string,
→DateTime?,
DateTime?, IEnumerable<OrderDetail>>), (MethodInfo)
methodof(<>f__AnonymousType0<string, DateTime?, DateTime?,
IEnumerable<OrderDetail>>.get_Details, <>f__AnonymousType0<string, DateTime?,
DateTime?, IEnumerable<OrderDetail>>) }), new ParameterExpression[] {
→CS$0$0000,
CS$0$0002 }));
string line = new string(‘-’, 40);
Array.ForEach(orderInformation.ToArray(), delegate (<>f__
AnonymousType0<string, DateTime?, DateTime?, IEnumerable<OrderDetail>> r) {
Console.WriteLine(“Customer ID: {0}”, r.CustomerID);
Console.WriteLine(“Order Date: {0}”,
r.OrderDate.GetValueOrDefault().ToShortDateString());
Console.WriteLine(“Required Date: {0}”,
r.RequiredDate.GetValueOrDefault().ToShortDateString());
Console.WriteLine(“-- -- -- -- -Order Details-- -- -- -- -”);
if (<>c__DisplayClass3.CS$<>9__CachedAnonymousMethodDelegate5 == null)
{
<>c__DisplayClass3.CS$<>9__CachedAnonymousMethodDelegate5 = delegate
(OrderDetail d) {
Console.WriteLine(“Product ID: {0}”, d.ProductID);
Console.WriteLine(“Unit Price: {0}”, d.UnitPrice);
Console.WriteLine(“Quantity: {0}”, d.Quantity);
Console.WriteLine();
};
}
Array.ForEach<OrderDetail>(r.Details.ToArray<OrderDetail>(),
<>c__DisplayClass3.CS$<>9__CachedAnonymousMethodDelegate5);
Console.WriteLine(line);
Console.WriteLine();
});
Console.ReadLine();
}
Implementing a Left Join
Because we are using entity classes with LINQ to SQL, constructing the LEFT JOIN uses the same code as constructing a left join in Chapter 11. To convert the group join to a left join, you add an additional from clause and range variable after the into clause and invoke the DefaultIfEmpty method in the group sequence. Listing 15.10 just contains the LINQ statement with the DefaultIfEmpty called on the children group. Remember with the left join, all of the parent information is repeated for each child, so a nested array is no longer needed.
Listing 15.10 The Group Join Is Easily Converted to a Left Join Construction By Adding an Additional from Clause on the Group Sequence and Calling DefaultIfEmpty
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqToSqlLeftJoin
{
class Program
{
static void Main(string[] args)
{
Northwind northwind = new Northwind();
var orderInformation = from order in northwind.Orders
join detail in northwind.Details
on order.OrderID equals detail.OrderID
into children
from child in children.DefaultIfEmpty()
select new
{
order.CustomerID,
order.OrderDate,
order.RequiredDate,
child.ProductID,
child.UnitPrice,
child.Quantity,
child.Discount
};
string line = new string(‘-’, 40);
Array.ForEach(orderInformation.ToArray(), r =>
{
Console.WriteLine(“Customer ID: {0}”, r.CustomerID);
Console.WriteLine(“Order Date: {0}”, r.OrderDate
.GetValueOrDefault().ToShortDateString());
Console.WriteLine(“Required Date: {0}”, r.RequiredDate
.GetValueOrDefault().ToShortDateString());
Console.WriteLine(“Product ID: {0}”, r.ProductID);
Console.WriteLine(“Unit Price: {0:C}”, r.UnitPrice);
Console.WriteLine(“Quantity: {0}”, r.Quantity);
Console.WriteLine(“Discount: {0}”, r.Discount);
Console.WriteLine();
});
Console.ReadLine();
}
}
public class Northwind : DataContext
{
private static readonly string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=”C:\Books\Sams\” +
“LINQ\Northwind\northwnd.mdf”;” +
“Integrated Security=True;Connect Timeout=30;User Instance=True”;
public Northwind()
: base(connectionString)
{
Log = Console.Out;
}
public Table<Order> Orders
{
get{ return this.GetTable<Order>(); }
}
public Table<OrderDetail> Details
{
get{ return GetTable<OrderDetail>(); }
}
}
[Table(Name = “dbo.Order Details”)]
public partial class OrderDetail
{
private int? _OrderID;
private int? _ProductID;
private decimal? _UnitPrice;
private short? _Quantity;
private float? _Discount;
public OrderDetail()
{
}
[Column(Storage = “_OrderID”, DbType = “Int NOT NULL”, IsPrimaryKey = true)]
public int? OrderID
{
get
{
return this._OrderID;
}
set
{
this._OrderID = value;
}
}
[Column(Storage = “_ProductID”, DbType = “Int NOT NULL”, IsPrimaryKey = true)]
public int? ProductID
{
get
{
return this._ProductID;
}
set
{
this._ProductID = value;
}
}
[Column(Storage = “_UnitPrice”, DbType = “Money NOT NULL”)]
public decimal? UnitPrice
{
get
{
return this._UnitPrice;
}
set
{
this._UnitPrice = value;
}
}
[Column(Storage = “_Quantity”, DbType = “SmallInt NOT NULL”)]
public short? Quantity
{
get
{
return this._Quantity;
}
set
{
this._Quantity = value;
}
}
[Column(Storage = “_Discount”, DbType = “Real NOT NULL”)]
public float? Discount
{
get
{
return this._Discount;
}
set
{
this._Discount = value;
}
}
}
[Table(Name = “dbo.Orders”)]
public partial class Order
{
private int _OrderID;
private string _CustomerID;
private System.Nullable<int> _EmployeeID;
private System.Nullable<System.DateTime> _OrderDate;
private System.Nullable<System.DateTime> _RequiredDate;
private System.Nullable<System.DateTime> _ShippedDate;
private System.Nullable<int> _ShipVia;
private System.Nullable<decimal> _Freight;
private string _ShipName;
private string _ShipAddress;
private string _ShipCity;
private string _ShipRegion;
private string _ShipPostalCode;
private string _ShipCountry;
[Column(Storage = “_OrderID”, AutoSync = AutoSync.OnInsert,
DbType = “Int NOT NULL IDENTITY”, IsPrimaryKey = true, IsDbGenerated = true)]
public int OrderID
{
get
{
return this._OrderID;
}
set
{
this._OrderID = value;
}
}
[Column(Storage = “_CustomerID”, DbType = “NChar(5)”)]
public string CustomerID
{
get
{
return this._CustomerID;
}
set
{
this._CustomerID = value;
}
}
[Column(Storage = “_EmployeeID”, DbType = “Int”)]
public System.Nullable<int> EmployeeID
{
get
{
return this._EmployeeID;
}
set
{
this._EmployeeID = value;
}
}
[Column(Storage = “_OrderDate”, DbType = “DateTime”)]
public System.Nullable<System.DateTime> OrderDate
{
get
{
return this._OrderDate;
}
set
{
this._OrderDate = value;
}
}
[Column(Storage = “_RequiredDate”, DbType = “DateTime”)]
public System.Nullable<System.DateTime> RequiredDate
{
get
{
return this._RequiredDate;
}
set
{
this._RequiredDate = value;
}
}
[Column(Storage = “_ShippedDate”, DbType = “DateTime”)]
public System.Nullable<System.DateTime> ShippedDate
{
get
{
return this._ShippedDate;
}
set
{
this._ShippedDate = value;
}
}
[Column(Storage = “_ShipVia”, DbType = “Int”)]
public System.Nullable<int> ShipVia
{
get
{
return this._ShipVia;
}
set
{
this._ShipVia = value;
}
}
[Column(Storage = “_Freight”, DbType = “Money”)]
public System.Nullable<decimal> Freight
{
get
{
return this._Freight;
}
set
{
this._Freight = value;
}
}
[Column(Storage = “_ShipName”, DbType = “NVarChar(40)”)]
public string ShipName
{
get
{
return this._ShipName;
}
set
{
this._ShipName = value;
}
}
[Column(Storage = “_ShipAddress”, DbType = “NVarChar(60)”)]
public string ShipAddress
{
get
{
return this._ShipAddress;
}
set
{
this._ShipAddress = value;
}
}
[Column(Storage = “_ShipCity”, DbType = “NVarChar(15)”)]
public string ShipCity
{
get
{
return this._ShipCity;
}
set
{
this._ShipCity = value;
}
}
[Column(Storage = “_ShipRegion”, DbType = “NVarChar(15)”)]
public string ShipRegion
{
get
{
return this._ShipRegion;
}
set
{
this._ShipRegion = value;
}
}
[Column(Storage = “_ShipPostalCode”, DbType = “NVarChar(10)”)]
public string ShipPostalCode
{
get
{
return this._ShipPostalCode;
}
set
{
this._ShipPostalCode = value;
}
}
[Column(Storage = “_ShipCountry”, DbType = “NVarChar(15)”)]
public string ShipCountry
{
get
{
return this._ShipCountry;
}
set
{
this._ShipCountry = value;
}
}
}
}
Note that because we are using mapped entity classes, yet really just classes, the LINQ statement doesn’t make any special allowances for the data coming from the database. However, the entity classes are written a little differently. In addition to using the TableAttribute and ColumnAttribute in the entity classes, you have to allow for nulls in the underlying table itself. For this reason, use nullable types in your entity classes for value types, like int and DateTime.
Querying Views with LINQ
Views don’t seem to be used in sample code on the Internet, in articles, or even in books that much. However, in real-world applications, the “view” is a very useful concept.
A view is a stored SQL statement that represents a snapshot of data. Generally, a view is created to capture some coherent representation of data, such as detailed customer order information. Often, views are thought of as read-only snapshots of data representing a join on one or more tables. The SQL view itself is read-only, but you can write code that gets data from a view and then separate code that uses that information to update the associated tables independently.
The first part of this section walks you through designing a view in Microsoft Visual Studio (which you can skip if you are comfortable with using Visual studio to design views), and the second part of this section demonstrates that a view can be mapped to an entity in C# and then queried using LINQ to SQL much as you would a table.
Building a View in SQL Server
To create a view based on two tables, you can start in Server Explorer in Visual Studio. To demonstrate, let’s use the Orders and Order Details table of the Northwind database. Here are the numbered steps:
1. In Visual Studio, select View, Server Explorer.
2. Expand the Data Connections table (see Figure 15.1) and right-click on the Views node of the Northwind database.
Figure 15.1 Right-click the Views node of Server Explorer and select Add View to design a view in Visual Studio.
3. The Add Table dialog box is displayed. Click the Orders table and the Order Details table, and click the Add button in the dialog box (refer to Figure 15.2). Close the Add Table dialog box.
Figure 15.2 After the Orders and Order Details tables have been added to the diagram pane of the visual designer (for views).
4. When you have added the tables, the Visual Studio designer expresses the relationships based on natural primary and foreign key relationships. (You can use the automatically added relationship or define others by dragging and dropping columns between tables.)
5. Next, select the columns to be part of the resultset. For our example, select all columns from Orders and every column except the OrderID from Order Details.
6. Save and close the view, providing a meaningful name.
Listing 15.11 shows the SQL representing the view created in the preceding set of numbered steps.
Listing 15.11 The SQL Representing the View Based on Northwind’s Orders and Order Details as Generated from the Preceding Set of Numbered Steps
SELECT
dbo.Orders.*,
dbo.[Order Details].ProductID,
dbo.[Order Details].UnitPrice,
dbo.[Order Details].Quantity,
dbo.[Order Details].DiscountFROM
dbo.Orders
INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
Querying a View with LINQ to SQL
After defining a view, or from an existing view, you can write SELECT statements against the view as if it were a table. By defining an object-relational mapping, you can write LINQ queries against the view, too. The key here is to use the view name for the Name argument of the TableAttribute. Writing a LINQ select statement against an ORM, mapped to a view, looks just like the code for querying against a table. The example coded against the “Alphabetical list of products” view in the Northwind database is shown in Listing 15.12.
Listing 15.12 Using the TableAttribute and ColumnAttribute as You Would for a Database Table to Map an Entity Class to a View in Your Database
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;
namespace LinqtoSqlView
{
class Program
{
static void Main(string[] args)
{
Northwind northwind = new Northwind();
var products = from product in northwind.Products
select product;
string line = new string(‘-’, 40);
Array.ForEach(products.ToArray(), r =>
{
Console.WriteLine(r);
Console.WriteLine(line);
Console.WriteLine();
});
Console.ReadLine();
}
}
public class Northwind : DataContext
{
private static readonly string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=”” +
“C:\Books\Sams\LINQ\Northwind\northwnd.mdf”;” +
“Integrated Security=True;Connect Timeout=30;User Instance=True”;
public Northwind()
: base(connectionString)
{
Log = Console.Out;
}
public Table<ProductList> Products
{
get { return this.GetTable<ProductList>(); }
}
}
[Table(Name = “Alphabetical list of products”)]
public class ProductList
{
[Column()]
public int ProductID { get; set; }
[Column()]
public string ProductName{ get; set; }
[Column()]
public int SupplierID { get; set; }
[Column()]
public int CategoryID { get; set; }
[Column()]
public string QuantityPerUnit { get; set; }
[Column()]
public decimal UnitPrice { get; set; }
[Column()]
public Int16 UnitsInStock { get; set; }
[Column()]
public Int16 UnitsOnOrder { get; set; }
public override string ToString()
{
StringBuilder builder = new StringBuilder();
PropertyInfo[] info = this.GetType().GetProperties();
Array.ForEach(info, i =>
{
Console.WriteLine(“Name: {0}, Value: {1}”,
i.Name,
i.GetValue(this, null) == null ? “none” :
i.GetValue(this, null));
});
return builder.ToString();
}
}
}
LINQ to SQL can determine by the Name argument of the TableAttribute that it is working with a view, and LINQ generates the code to query the view. The output from the code, including the LINQ output sent to the console, shows the query treats the view as it would a table by placing the view name in the from clause of the generated SQL (refer to Figure 15.3).
Figure 15.3 The LINQ to SQL provider properly uses the view as the source for the from clause of the SQL statement.
Databinding with LINQ to SQL
The key to databinding is IEnumerable. If you trace through the .NET source or disassembled .NET assemblies far enough, you will see that binding uses IEnumerable and reflection to read public property names and bind these and their values to bindable controls, like a GridView.
Bindability is also supported by classes that implement IBindingList. IBindingList, in turn, implements the IEnumerable interface. For instance, LINQ sequences return IEnumerable<T> objects, so these are automatically bindable. EntitySets, as described in Chapter 14, “Creating Better Entities and Mapping Inheritance and Aggregation,” are used for properties of entities that themselves represent mapped relationships. EntitySets also implement IEnumerable, so these are bindable, too.
Listing 15.13 represents the code behind of a web page. Most of the code is an ORM representing the “Alphabetical list of products” view, and the Page_Load method shows that with the DataSource and DataBind properties of bindable controls (GridView, in the example), you can bind the output from a LINQ query right to a bindable control. (To implement the example, simply place a GridView on a web page and add the code from Listing 15.13.)
Listing 15.13 Bindability Is Supported By IEnumerable; IEnumerable Is at the Heart of All of the Sequences Returned from LINQ So Binding Is Directly Supported Without Any Fanfare
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqToSqlDatabinding
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Northwind northwind = new Northwind();
var products = from product in northwind.Products
select product;
GridView1.DataSource = products;
GridView1.DataBind();
}
}
public class Northwind : DataContext
{
private static readonly string connectionString =
“Data Source=.\SQLEXPRESS;AttachDbFilename=c:\temp\northwnd.mdf;” +
“Integrated Security=True;Connect Timeout=30;User Instance=True”;
public Northwind()
: base(connectionString)
{
Log = Console.Out;
}
public Table<ProductList> Products
{
get { return this.GetTable<ProductList>(); }
}
}
[Table(Name = “Alphabetical list of products”)]
public class ProductList
{
[Column()]
public int ProductID { get; set; }
[Column()]
public string ProductName{ get; set; }
[Column()]
public int SupplierID { get; set; }
[Column()]
public int CategoryID { get; set; }
[Column()]
public string QuantityPerUnit { get; set; }
[Column()]
public decimal UnitPrice { get; set; }
[Column()]
public Int16 UnitsInStock { get; set; }
[Column()]
public Int16 UnitsOnOrder { get; set; }
}
}
Summary
There are two ways to use LINQ with ADO.NET (so far). You can use LINQ to DataSets and query DataTables. This approach is great for existing code and works because ADO.NET DataTables are associated with extension methods that provide access to enumerability for DataTables and underlying field values. After you have enumerability and access to field values, you can define join relationships. The second way ADO.NET is supported is through LINQ to SQL. LINQ to SQL depends on mapping entity classes to database tables, but after being mapped, these entity classes are just classes with a persistence layer and LINQ behaviors are supported behaviors like joins.
The biggest difference where LINQ, ADO.NET, and joins are concerned is that the column in databases can contain nulls. To account for the possibility of a null, define your entities as nullable types. (Also, define fields in projections as nullable types.) After you have dealt with nullable fields, querying databases via LINQ is consistent with querying custom objects.
The key to success is not to rewrite existing code to support LINQ; rather use LINQ to work in conjunction with the style of code you have or will have. And, you always have the option to define joins in SQL, too. The choice is not an either/or choice. Choosing LINQ joins or SQL joins is a matter of suitability to the problem at hand, which, in turn, is a matter of preference.