Visual Studio 2008开发新特性系列课程(4)：VS2008新特性之LINQ实战

1.linq 框架
---------------------------------------------------------
C# VB
---------------------------------------------------------
.Net Language INtegrated Query(LINQ)
---------------------------------------------------------
Linq enabled data sources
1.linq to objects
2.Linq enabled ADO.NET
----linq to DataSet
----linq to SQL
----linq to Entities
3.linq to XML
---------------------------------------------------------
Data
Objects,Relations,XML
---------------------------------------------------------

 

2.LINQ for SQL
? 把.NET 类和SQL 数据通过关系进行映射(对象<--->关系的相互映射)
? 把LINQ 查询转化为SQL 语言进行执行
? 支持对插入,更新,删除操作进行跟踪.
? 支持实体级别的验证规则
? 构建于ADO.NET之上并且集成连接池和事ADO.务处理(ADO.NET之上)

举例1：（查询实例）
  NorthWindDataContext dc = new NorthWindDataContext();

            var query = from P in dc.Products
                        where P.CategoryID.Value>6
                        select P;
            this.GridView1.DataSource = query;
            this.GridView1.DataBind();

举例2：（修改单个记录实例）
NorthWindDataContext dc = new NorthWindDataContext();
            Product product = dc.Products.Single(p => p.ProductName == "Tofu");
    
            product.UnitPrice = 1111111111;
            product.UnitsInStock = 55;
            dc.SubmitChanges();

3.数据表映射
? 映射数据表和实体类之间的关系
? 使用数据库中典型的主/外键进行表示
? 支持灵活的关系查询并且不用写任务的SQL
? 代码就可以执行处理过程

举例3：
-----产品类型 Categories<1>-----> <n>产品Products <n>
-----产品Products <n> <----<1>提供商 Suppliers

Table Suplier
-------------------------------------------------------
SuplierID
CompanyName
.....

Table Category
-------------------------------------------------------
CategoryID
CanegoryName
....

Table Product
-------------------------------------------------------
ProductID
ProductName
....
SuplierID
CategoryID

模型中的代码关联如下：
public partial class Product {
 public int ProductID;
 public string ProductName;
 public Nullable<int> SupplierID;
 public Nullable<int> CategoryID;

 public Supplier Supplier; //两者组合以来，一对一
 public Category Category;  //两者组合以来，一对一
}
public partial class Supplier {
 public int SupplierID;
 public string CompanyName;

 public EntitySet<Product> Products; //一对多
}
 public partial class Category {
 public int CategoryID;

 public EntitySet<Product> Products; //一对多
}
我们可以这样理解：在对Product进行数据访问的时候，可以直接访问Supplier和Category的的单个对象；在对Supplier访问时，可以访问对于的一组Products对象；在对Category访问时，可以访问对于的一组Products对象；

举例查询1：（在Supplier里面直接访问Product）
            NorthWindDataContext db = new NorthWindDataContext();

            GridView1.DataSource = from s in db.Suppliers
                                   select new
                                   {
                                       Name = s.CompanyName,
                                       Country = s.Country,
                                       Products = from p in s.Products //在Supplier里面直接访问Products
                                                  select new
                                                  {
                                                      ProductName = p.ProductName
                                                  }
                                   };

            GridView1.DataBind();
        }

举例2：（在Supplier里面访问Product）
NorthwindDataContext db = new NorthwindDataContext();
var suppliers = from s in db.Suppliers
  where s.Products.Count > 2
  select s;

foreach (Supplier supplier in suppliers) {
 Response.Write("<h3>" + supplier.CompanyName + "</h3>");
 foreach (Product product in supplier.Products) {
  Response.Write("-- ");
  Response.Write(product.ProductName);
  Response.Write("<br/>");
 }
}

举例3：（使用一些函数）
NorthwindDataContext db = new NorthwindDataContext();
SupplierList.DataSource = from s in db.Suppliers
     where s.Products.Count > 2
     select s;
SupplierList.DataBind();

NorthwindDataContext db = new NorthwindDataContext();
GridView1.DataSource = (from s in db.Suppliers
   where s.Products.Count > 4
   select s).Including(s => s.Products)
GridView1.DataBind();

 

4.联合查询
举例1：两个表的join操作。

NorthWindDataContext db = new NorthWindDataContext();
var custTotalOrders = from c in db.Customers
                      join o in db.Orders
                       on c.CustomerID equals o.CustomerID into custOrders   //存放早custOrders的临时结果集中
                       from o in custOrders //从这个结果集中继续查询
                       select new
                       {
                          Customer = c.CompanyName,
                          OrderDate = o.OrderDate,
                           OrderTotal = o.Order_Details.Sum(d => d.UnitPrice * d.Quantity)
                        };
GridView1.DataSource = custTotalOrders;
GridView1.DataBind();

举例2：（直接查询）
var innerJoinQuery =
    from category in categories
    join prod in products on category.ID equals prod.CategoryID
    select new { ProductName = prod.Name, Category = category.Name };

 

5.数据分页
相对于以前的TSQL语句而言，不是一次把所有的数据都取出来，而是根据需要从数据库中读取数据。
int startRow = Convert.ToInt32(Request.QueryString["startRow"]);
NorthwindDataContext db = new NorthwindDataContext();
var results = from c in db.Customers join o in db.Orders
       on c.CustomerID equals o.CustomerID into custOrders
       from o in custOrders
       select new {
          Customer = c.CompanyName,
         OrderDate = o.OrderDate,
        OrderTotal = o.OrderDetails.Sum(d=>d.UnitPrice)
       };

GridView1.DataSource = results.Skip(startRow).Take(10);//分页
GridView1.DataBind();

5.更新一个特定的产品
NorthwindDataContext db = new NorthwindDataContext();
Product product = db.Products.Single(p => p.ProductName== "Chai");
product.UnitsInStock = 11;
product.ReorderLevel = 10;
product.UnitsOnOrder = 2;
db.SubmitChanges();

6.插入记录
Order ord = new Order
{
    OrderID = 12000,
    ShipCity = "Seattle",
    OrderDate = DateTime.Now
    // …
};

// Add the new object to the Orders collection.
db.Orders.InsertOnSubmit(ord);

// Submit the change to the database.
try
{
    db.SubmitChanges();
}
catch (Exception e)
{
    Console.WriteLine(e);
    // Make some adjustments.
    // ...
    // Try again.
    db.SubmitChanges();
}

 

7.删除记录
var deleteOrderDetails =
    from details in db.OrderDetails
    where details.OrderID == 11000
    select details;

foreach (var detail in deleteOrderDetails)
{
    db.OrderDetails.DeleteOnSubmit(detail);
}

try
{
    db.SubmitChanges();
}
catch (Exception e)
{
    Console.WriteLine(e);
    // Provide for exceptions.
}

 

8.其它的LINQ for SQL 功能
? 能够执行实体/属性验证
? 可以使用存储过程和视图
? 可以清晰地定义和参与内部的TransactionScope()操作(简单分布式事务的写法，可以指定事务是否包含在上一层事务中，通过DataContex制定)
? 实体类型的保存可以使用同一个DataContext进行多种数据查询

 

9.LINQ to XML
以前的操作方式：流的方式、结点的方式

? XML 文档的更好的操作
? 支持language integrated queries
? 更方便,更快速更智能更简单的XML API

NorthwindDataContext db = new NorthwindDataContext();
 XElement rssRoot = new XElement("rss",
 new XAttribute("version", "2.0"),
 new XElement("channel"
  new XElement("title", "My RSS Feed"),
  new XElement("link", "http://weblogs.asp.net"),
  new XElement("description", "Northwind Products Feed"),
 from product in db.Products
 orderby product.ProductName descending
 select new XElement("item",
  new XElement("title", product.ProductName),
  new XElement("link", “p.aspx?id="+product.ProductID),
  new XElement("description", "Supplier: " +
  product.Supplier.CompanyName)
 )
 )
);
Response.Write(rssRoot.ToString());