针对数据的LINQ有两个基本的使用方式.可以编写LINQ to DataSet代码来查询DataSet中的表,也可以定义实体并使用LINQ to SQL去查询这些实体.
不等式联接必须使用where子句,而且可以包含一个或多个谓词.
class Program { static void Main(string[] args) { const string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=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 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") }; string line = new string('-', 40); foreach (var item in discontinued) { 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(); } } }左外联接是由两个序列构造:一个是分组联接,另一个是DefaultIfEmpty扩展方法.为了在LINQ to DataSet中实现左外联接,你需要解决一些使用DefaultIfEmpty方法时的小问题. 简单的说,当没有匹配的子行时,左外联接需要一个空的DataRow.可以在调用DefaultIfEmpty方法的时候使用DataTable.NewRow方法,并未子数据定义能够使用可空类型的投影.后面哪个步骤时必须的,因为空的新行会被空值初始化.
class Program { static void Main(string[] args) { const string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=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 ... from ... in 子句将分组联接平面化左外联接.如果右侧的DataRow不存在,则调用NewRow以初始化一个空的子涵,并定义支持可空类型的投影*/ into children from child in children.DefaultIfEmpty(orders.NewRow()) let OrderID = child.Field<int?>("OrderID") where OrderID == 11080 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(); } } }可以通过交换LINQ查询中范围变量的顺序来构造一个右外联接.
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") };如果有很多使用DataSet和DataTable的ADO.NET代码,很显然,你可以使用LINQ,不过不用添加映射到表的实体.如果你正在开发新系统,那么可以考虑定义对象关系映射,即将实体类映射到数据库表.这样将得到更加清洁的代码,更加简单的LINQ表达式,以及独立于ADO.NET类型的类.
等式联接
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)); } } public class Publishers : DataContext { /// <summary>pubs是一个示例数据库，他含有出版商、作者、书及以及版税等信息，不过却过度的使用了缩写</summary> private static readonly string connectionString = "Data Source=localhost;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 { /// <summary>缩写的列名通过ColumnAttribute的Name参数得以纠正</summary> [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 { /// <summary>空值字段则通过实体定义中的可空类型类进行处理</summary> [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; } }分组联接 分组联接就是一个主从关系其中，主序列的每一个元素都有其对应的子对象。下例给出了Northwind数据库中Orders表和Order Details表上的一个分组联接，并用一个嵌套的数组来显示各个元素。
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(); }); } } public class Northwind : DataContext { private static readonly string connectionString = "Data Source=localhost;Initial Catalog=pubs;Integrated Security=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; } } }左外联接 将分组联接转换为左联接，需要在into子句之后再增加一个from子句和范围变量，并再分组序列上调用DefaultIfEmpty拓展方法。
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() where order.OrderID == 11082 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(); }); } } public class Northwind : DataContext { private static readonly string connectionString = @"Data Source=localhost;Initial Catalog=northwind;Integrated Security=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; } } }视图是一段存储查询，它表示数据的一个快照。一般来说，视图时用于捕获相互关联的数据的。视图常常被看作时通过联接一个或多个表而产生的数据只读快照。虽然SQL视图本身时制度的，不过你可以编写代码从视图中获取数据然后再用另外的代码将信息更新到相关的表中。
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.Take(5).ToArray(), r => { Console.WriteLine(r); Console.WriteLine(line); Console.WriteLine(); }); } } public class Northwind : DataContext { private static readonly string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=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(); } }数据绑定的关键时IEnumerable。如果仔细看看.NET源代码或反编译的.NET程序集，就会发现，绑定先通过IEnumberable和反射来读取公共属性的名称，然后将这些属性及其值绑定到可绑定控件。 可绑定性也可以通过实现了IBindingList的类来提供。IBindingList本身就实现了IEnumberable接口。由于LINQ返回的时IE number able对象，因此他们本身就时可绑定的。
