Linq 的基本操作 之Union All/Union/Intersect 操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.Linq.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable DataTable1 = new DataTable();
        DataRow row = DataTable1.NewRow();
        DataTable1.Columns.Add("编号");
        DataTable1.Columns.Add("姓名");

        row[0] = 1;
        row[1] = "靳志远";
 
        DataTable1.Rows.Add(row);

        DataTable DataTable2 = new DataTable();
        DataRow row2 = DataTable2.NewRow();
        DataTable2.Columns.Add("编号");
        DataTable2.Columns.Add("姓名");
       
        row2[0] = 1;
        row2[1] = "靳志远";
        DataTable2.Rows.Add(row2);
        DataTable newDataTable = DataTable1.Clone();
      //  Response.Write(newDataTable.Columns.Count.ToString());  //输出 2列；
        object[] obj = new object[newDataTable.Columns.Count];
        for (int i = 0; i < DataTable1.Rows.Count; i++)
        {
            DataTable1.Rows[i].ItemArray.CopyTo(obj, 0);
            //for (int j = 0; j < DataTable1.Rows[i].ItemArray.Length; j++)
            //{
            //    Response.Write(DataTable1.Rows[i][j].ToString());
            //}
            // Response.Write("获取DataTable1.Rows[i].ItemArray.Length.ToString():" + DataTable1.Rows[i].ItemArray.Length.ToString());
            newDataTable.Rows.Add(obj);
        }
        for (int i = 0; i < DataTable2.Rows.Count; i++)
        {
            DataTable2.Rows[i].ItemArray.CopyTo(obj, 0);
            newDataTable.Rows.Add(obj);
        }

        /*  简单形式：*/
        /*Conncat(连接)*/
        /* 说明：连接不同的集合，不会自动过滤相同项；延迟。*/
    
        var data2result = (from c in DataTable1.AsEnumerable()
                          select c).Concat(
                           from cc in DataTable2.AsEnumerable()
                           select cc
                          );

        //GridView1.DataSource = data2result.CopyToDataTable<DataRow>();
        //GridView1.DataBind();
        /*  合并：相当于sql 中UNION ALL*/
        /*连接不同的集合，自动过滤相同项；延迟。即是将两个集合进行合并操作，过滤相同的项*/
 
        var data3result = (from c in DataTable1.AsEnumerable()
                           select c).Union(
                             from cc in DataTable2.AsEnumerable()
                             select cc
                             );
        //GridView1.DataSource = data3result.CopyToDataTable<DataRow>();
        //GridView1.DataBind();

        /*Intersect 相交*/
        /*取相交项；延迟。即是获取不同集合的相同项（交集）。即先遍历第一个集合，找出所有唯一的元素，然后遍历第二个集合，
         并将每个元素与前面找出的元素作对比，返回所有在连个集合内都出现的元素。
         */
     
        var data4result = (from c in DataTable1.AsEnumerable()
                           select c).Intersect(
                               from cc in DataTable2.AsEnumerable()
                               select cc
                             );

        //GridView1.DataSource = data4result.CopyToDataTable<DataRow>();
        //GridView1.DataBind();

        /*Except(与非)*/
        /*说明：排除相交项；延迟。既是从某集合中删除与另一个集合中相同的项。先遍历第一个集合，找出所有唯一的元素，然后再遍历第二个集合，
         返回第二个集合中所有未出现在前面集合中的元素。*/
      
        var data5result = (from c in DataTable1.AsEnumerable()
                           select c).Except(
                       from cc in DataTable2.AsEnumerable()
                       select cc
                     );

        //GridView1.DataSource = data5result.CopyToDataTable<DataRow>();
        //GridView1.DataBind();

        /*Top/Botton操作*/
        /*适用场景：适量的取出自己想要的数据，不是全部取出，这样性能有所加强*/

        /*Take*/
        /*说明：获取集合的前n个元素；延迟。即只返回限定数量的结果集。*/
        /*ascending是升序（默认）；descending是降序；*/
        DataClassesDataContext dcdc = new DataClassesDataContext();
        var data6result = (from c in dcdc.city orderby c.city_id ascending
                           select c ).Take(2);
        //GridView1.DataSource = data6result;
        //GridView1.DataBind();

        /*Skip*/
        /*说明：跳过集合的前n个元素；延迟。即我们跳过给定的数目返回后面的结果集。*/
        DataClassesDataContext dcdcskip = new DataClassesDataContext();
        var data7result = (from c in dcdc.city
                           orderby c.city_id ascending
                           select c).Skip(2);
        //GridView1.DataSource = data7result;
        //GridView1.DataBind();

        /*TakeWhile*/
        /*说明：直到某一条件成立就停止获取；延迟。即用其条件依次判断源序列中的 元素，
         返回符合判断条件的元素，该判断操作将在返回false或源序列的末尾结束。*/
 
        /*SkipWhile*/
        /*说明：直到某一条件成立就停止跳转；延迟。即用其条件去判断源序列中的元素并且跳过第一个符合判断条件
         的元素，一旦判断返回false,接下来将不再进行判断并返回剩下的所有元素。*/
       
        /*Paging(分页)操作*/
        /*适用场景：结合Skip和Take就可以实现对数据分页操作*/
        /*1.索引*/
        /*语句描述：使用Skip和Take运算符进行分页，跳过前3条记录，然后返回接下来的2条记录*/
        DataClassesDataContext dcdcpageing = new DataClassesDataContext();
        var data9result = (from c in dcdc.city
                           orderby c.city_id ascending
                           select c).Skip(3).Take(2);
        //GridView1.DataSource = data9result;
        //GridView1.DataBind();

        /*按唯一键排序*/
        /*语句描述：使用Where子句和Take运算符进行分页，首先筛选得到3以上的vcity_id，然后按ProductID排序，最后
         取前2条数据。*/
        DataClassesDataContext dcdcpageing1 = new DataClassesDataContext();
        var data9result1 = (from c in dcdc.city where c.city_id>3
                           orderby c.city_id ascending
                           select c).Take(2);
        //GridView1.DataSource = data9result1;
        //GridView1.DataBind();
        /*SqlMethods操作*/
        /*在LINQ to SQL语句中，为我们提供了SQLMETHODS操作，进一步为我们提供了方便，例如Like方法用于自定义通配*/
        /*Like*/
        /*自定义的通配表达式%表示零长度或任意长度的字符串；_表示一个字符；[]表示在某范围区间的一个字符；[^]表示不再某范围区间的一个字符。比如查询城市名city_name "南"开头的城市*/
        DataClassesDataContext dcdclike = new DataClassesDataContext();
        var data10result = from c in dcdclike.city
                           where SqlMethods.Like(c.city_name, "南%")
                           select c;
        GridView1.DataSource = data10result;
        GridView1.DataBind();

        /* 比如查询消费者ID没有“AXOXT”形式的消费者：

            var q = from c in db.Customers
                    where !SqlMethods.Like(c.CustomerID, "A_O_T")
                    select c;
            DateDiffDay
            说明：在两个变量之间比较。分别有：DateDiffDay、DateDiffHour、DateDiffMillisecond、DateDiffMinute、DateDiffMonth、DateDiffSecond、DateDiffYear

            var q = from o in db.Orders
                    where SqlMethods
                    .DateDiffDay(o.OrderDate, o.ShippedDate) < 10
                    select o;
            语句描述：查询在创建订单后的 10 天内已发货的所有订单。

            已编译查询操作(Compiled Query)
            说明：在之前我们没有好的方法对写出的SQL语句进行编辑重新查询，现在我们可以这样做，看下面一个例子：

            //1.创建compiled query
            NorthwindDataContext db = new NorthwindDataContext();
            var fn = CompiledQuery.Compile(
                (NorthwindDataContext db2, string city) =>
                from c in db2.Customers
                where c.City == city
                select c);
            //2.查询城市为London的消费者,用LonCusts集合表示，这时可以用数据控件绑定
            var LonCusts = fn(db, "London");
            //3.查询城市为Seattle的消费者
            var SeaCusts = fn(db, "Seattle");
            语句描述：这个例子创建一个已编译查询，然后使用它检索输入城市的客户。

         */

    }
}