【转载】C# LINQ to SQL

1、Concat（连接不同的集合不会自动过滤相同项。会延迟计算）

var q = (from c in db.Customers
        select c.Phone
            ).Concat(
            from e in db.Employees
            select e.HomePhone);
   var q = (from c in db.Customers
            select new
            {
               Name = c.CustomerName,
               Phone = c.Phone
             }).Concat(
            from e in db.Employees
            select new
            {
               Name = e.EmployeeName,
               Phone = e.HomePhone
             });

 

2、Union（合并，自动过滤相同的项。会延迟计算）

var q = (from c in db.Customers
            select c.Country).Union(
            from e in db.Employees
            select e.Country);

 

3、Intersect（交。会延迟计算）

var q = (from c in db.Customers
            select c.Country).Intersect(
            from e in db.Employees
            select e.Country);

4、Except（差，A-B。从A集合排除A交B。会延迟计算)

var q = (from c in db.Customers
            select c.Country).Except(
            from e in db.Employees
            select e.Country);

 

5、Top、Bottom（取出指定数量的数据。会延迟计算）

 

6、Take（获取集合的前n个数据。会延迟计算）

var q = (from e in db.Employees
            orderby e.HireDate
            selct e).Take(5);

 

7、Skip（跳过集合的前n个数据。会延迟计算）

var q = (from p in db.Products
            orderby p.UnitPrice descending
            select p).Skip(10);

      选择10种最贵的产品之外的所有产品

 

8、TakeWhile（直到某一条件不成立才停止获取。会延迟计算）
   即用其条件去依次判断源序列中的元素，返回符合判断条件的元素，该判断操作将在返回false或源序列的末尾结束

 

9、SkipWhile（顾名思义，同上）

 

10、Paging（分页操作）

var q = (from c in db.Customers
             orderby c.CustomerName
             select c).Skip(50).Take(10);

 

11、Like

var q = from c in db.Customers
　　　　    where SqlMethods.Like(c.CustomerID, "C%")
　　　　    select c;

    查询消费者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 SqlMethod.DateDiffDay(o.OrderDate, o.ShippedDate) < 10
　　　　    select o;

    查询在创建订单后10天内发货的所有订单

 

12、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);
    var londonCusts = fn(db, "London");
    var seaCusts = fn(db, "Seattle");