一、什么是Linq(what)
二、Linq的优点(why)
三、Linq查询的步骤(how)
四、查询基本操作
五、結合實例代碼(具體聯繫用linqtosql來寫的增刪改查)
一．什么是Linq(what)
.net的设计者在类库中定义了一系列的扩展方法
来方便用户操作集合对象
这些扩展方法构成了LINQ的查询操作符
这一系列的扩展方法，比如：
Where，Max，Select，Sum，Any，Average，All，Concat等
都是针对IEnumerable的对象进行扩展的
也就是说，只要实现了IEnumerable接口，就可以使用这些扩展方法 //其实引用using system.linq就好了
Linq是语言集成查询（Language Integrated Query）的简称，是visual Studio 2008和.NET Framework 3.5版本中一项突破性的创新，它在对象领域和数据领域之间架起了一座桥梁。
Linq支持各种数据源：
1、ADO.NET DataSet
2、XML文档
3、SQL Server数据库
4、更多。。。
二、Linq的优点(why)
IEnumerable接口是LINQ特性的核心接口
只有实现了IEnumerable接口的集合
才能执行相关的LINQ操作，比如select,where等
传统的SQL查询
1 select FirstName,LastName,* from Customers
2 where city = 'Shanghai'
3 order by district
简单的字符串表示，没有编译时类型检查，没有IDE的智能感知支持。
以上例子只是针对SQL，针对不同的数据源，例如XML文档、各种WEB服务等我们还要学习不同的查询方法。
• Linq查詢為什麼要把from放到前面
IDE智能感知支持(智能提示)
三、Linq查询的步骤(how)
所有的Linq查询操作都由以下三个不同的操作组成：
• 获得数据源
• 创建查询
• 执行查询
数据源
• 想要使用Linq进行查询，数据源必须支持IEnumerable或IEnumerable(T)泛型接口或派生接口（如泛型的IQueryable(T)接口。
查询执行
• 查询变量本身只是存储查询命令。实际的查询执行会延迟在foreach语句中循环访问变量时发生。此概念称为“延迟执行”。
• 强制立即执行，可以通过以下两个方法，使得Linq立即执行查询
执行聚合函数（Count、Max、Average、First）
四、查询基本操作
1、from子句
用于获取数据源
1 var queryAllCustomers=
2 from cust in Customers
3 select cust;
2、复合from子句
1 //数据源
2 IList<Student> students = new List<Student>
3 {
4 new Student{ Name="Kevin", Score=new List<int>{89,93,88,78}},
5 new Student{ Name="Jackie",Score=new List<int>{92,87,83,91}},
6 new Student{ Name="Helen",Score=new List<int>{53,76,72,62}}
7 };
8
9 //使用复合from子句查询命令
10 var getStudent =
11 from student in students
12 from score in student.Score
13 where score > 90
14 select new { Name=student.Name,Score=score};
3、使用let子句扩展范围变量
用于创建查询自身的范围变量
1 string[] strings ={
2 "I am a new Student.",
3 "You are a talent"
4 };
5
6 var query = from sentences in strings
7 let words = sentences.Split(' ')
8 from word in words
9 let w = word.ToLower()
10 where w[0] == 'a' || w[0] == 'e' || w[0] == 'i' || w[0] == 'o' ||
w[0] == 'u'
11 select word;
12
13 foreach (var word in query)
14 {
15 Console.Write(word + ",");
16 }
4、where子句
1 //数据源
2 int[] arr = { 0, 3, 2, 1, 9, 6, 8, 7, 4, 5 };
3
4 //使用Where子句查询的查询语句
5 var query = from a in arr
6 where a < 5 && a % 2 == 0
7 select a;
8
9 //执行查询
10 foreach (var a in query)
11 {
12 Console.WriteLine(a);
13 }
5、orderby子句
1 var query = from a in arr
2 where IsEven(a)
3 orderby a ascending
4 select a;
接着上个例子的演示，本例是一个升序的排序。
1 var query = from a in arr
2 where IsEven(a)
3 orderby a descending
4 select a;
本例是一个降序的排序。
6、group子句
1 //数据源
2 string[] fruits = { "apple", "banana", "peach", "orange", "melon", "lemon" };
3
4 //分组查询的查询语句
5 var query = from f in fruits
6 group f by f[0];
7
8 //执行查询
9 foreach (var letters in query)
10 {
11 Console.WriteLine("words that start with letter:" + letters.Key);
12 foreach (var word in letters)
13 {
14 Console.WriteLine(word);
15 }
16 }
1 var query = from f in fruits
2 group f by f[0] into g
3 where g.Key == 'p' || g.Key == 'b'
4 select g;
7、join子句
1）内部连接
1 var innerJoinQuery =
2 from category in categories
3 join prod in products on category.ID equals prod.CategoryID
4 select new { ProductName = prod.Name, Category = category.Name };
2）分组连接
1 var innerGroupJoinQuery =
2 from category in categories
3 join prod in products on category.ID equals prod.CategoryID
4 into prodGroup
5 select new { CategoryName = category.Name, Products = prodGroup };
3）左外部连接
1 var leftOuterJoinQuery =
2 from category in categories
3 join prod in products on category.ID equals prod.CategoryID
4 into prodGroup
5 from prod in prodGroup.DefaultIfEmpty()
6 select new { CatName = category.Name, ProdName = prod.Name };
8、equals关键字
• join子句执行同等连接。换句话说，只能基于两个键之间的相等关系进行匹配。
• 为了表明所有连接都是同等连接，join子句使用equals关键字而不是==运算符
9、select子句（选择、投影）
select子句可以指定将在执行查询时产生的值的类型。该子句的结果将基于前面所有子句的计算结果以及select子句本身中的所有表达式。
查询表达式必须以select子句或group子句结束
Linq To Sql中实现Left Join与Inner Join使用Linq语法与lambda表达式
当前有两个表，sgroup与sgroupuser，两者通过gKey关联，而sgroup表记录的是组，而sgroupuser记录是组中的用户，因此在sgroupuser中不一定有数据。需要使用Left Join获取数据：
Linq语法如下:
var sg = (from g in dc.sgroup
join gu in dc.sgroupuser on g.gKey equals gu.gKey into l
from lgu in l.DefaultIfEmpty()
select new { g, lgu }).ToList();
Lambda表达式如下：
var sg = dc.sgroup.GroupJoin(dc.sgroupuser, g => g.gKey, gu
=> gu.gKey, (g, gu) => new { g, gu
}).Select(o=>o).ToList() ;
注意:
Linq
与Lambda表达式取出的结果有所不同.Linq取出的结果的记录数与Sql中的Left
Join的结果相同,而Lambda表达式取出的记录数是sgroup表中的记录数,sgroupuser对应的记录是以对象集合存在于结果中
附:
下面是Inner Join:
Linq语法如下:
var sg = (from g in dc.sgroup
join gu in dc.sgroupuser on g.gKey equals
gu.gKey
select new { g, gu }).ToList();
Lambda表达式如下：
var sg = dc.sgroup.Join(dc.sgroupuser, g => g.gKey, gu =>
gu.gKey, (g, gu) => new { g, gu }).Select(o=>o).ToList()
;
注意:
上面最后都用到了ToList()方法 ,
用ToList()是为了一次性将数据取到本地.
闲言碎语
近期比较忙，但还是想写点什么，就分享一些基础的知识给大家看吧，希望能帮助一些linq新手，如果有其它疑问，可以进右上角群，进行交流探讨，谢谢。
开门见山
读这篇文章之前，我先说下，每一种搜索结果集，我都以三种方式变现出来，为啦更好的理解，希望不要嫌我啰嗦。
1.简单的linq语法
//1
var ss = from r in db.Am_recProScheme
select r;
//2
var ss1 = db.Am_recProScheme;
//3
string sssql = "select * from Am_recProScheme";
2.带where的查询
//1
var ss = from r in db.Am_recProScheme
where r.rpId > 10
select r;
//2
var ss1 = db.Am_recProScheme.Where(p => p.rpId > 10);
//3
string sssql = "select * from Am_recProScheme where rpid>10";
3.简单的函数计算（count，min，max，sum）
//1
////获取最大的rpId
//var ss = (from r in db.Am_recProScheme
// select r).Max(p => p.rpId);
////获取最小的rpId
//var ss = (from r in db.Am_recProScheme
// select r).Min(p => p.rpId);
//获取结果集的总数
//var ss = (from r in db.Am_recProScheme
// select r).Count();
//获取rpId的和
var ss = (from r in db.Am_recProScheme
select r).Sum(p => p.rpId);
//2
//var ss1 = db.Am_recProScheme.Max(p=>p.rpId);
//var ss1 = db.Am_recProScheme.Min(p => p.rpId);
//var ss1 = db.Am_recProScheme.Count() ;
var ss1 = db.Am_recProScheme.Sum(p => p.rpId);
Response.Write(ss);
//3
string sssql = "select max(rpId) from Am_recProScheme";
sssql = "select min(rpId) from Am_recProScheme";
sssql = "select count(1) from Am_recProScheme";
sssql = "select sum(rpId) from Am_recProScheme";
4.排序order by desc/asc
var ss = from r in db.Am_recProScheme
where r.rpId > 10
orderby r.rpId descending //倒序
// orderby r.rpId ascending //正序
select r;
//正序
var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList();
//倒序
var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList();
string sssql = "select * from Am_recProScheme where rpid>10 order by rpId [desc|asc]";
5.top(1)
//如果取最后一个可以按倒叙排列再取值
var ss = (from r in db.Am_recProScheme
select r).FirstOrDefault();
//（）linq to ef 好像不支持 Last()
var ss1 = db.Am_recProScheme.FirstOrDefault();
//var ss1 = db.Am_recProScheme.First();
string sssql = "select top(1) * from Am_recProScheme";
6.跳过前面多少条数据取余下的数据
//1
var ss = (from r in db.Am_recProScheme
orderby r.rpId descending
select r).Skip(10); //跳过前10条数据，取10条之后的所有数据
//2
var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList();
//3
string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10";
7.分页数据查询
//1
var ss = (from r in db.Am_recProScheme
where r.rpId > 10
orderby r.rpId descending
select r).Skip(10).Take(10); //取第11条到第20条数据
//2 Take(10): 数据从开始获取，获取指定数量（10）的连续数据
var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList();
//3
string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 and rowNum<=20";
8.包含，类似like '%%'
//1
var ss = from r in db.Am_recProScheme
where r.SortsText.Contains("张")
select r;
//2
var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("张")).ToList();
//3
string sssql = "select * from Am_recProScheme where SortsText like '%张%'";
9.分组group by
//1
var ss = from r in db.Am_recProScheme
orderby r.rpId descending
group r by r.recType into n
select new
{
n.Key, //这个Key是recType
rpId = n.Sum(r => r.rpId), //组内rpId之和
MaxRpId = n.Max(r => r.rpId),//组内最大rpId
MinRpId = n.Min(r => r.rpId), //组内最小rpId
};
foreach (var t in ss)
{
Response.Write(t.Key + "--" + t.rpId + "--" + t.MaxRpId + "--" + t.MinRpId);
}
//2
var ss1 = from r in db.Am_recProScheme
orderby r.rpId descending
group r by r.recType into n
select n;
foreach (var t in ss1)
{
Response.Write(t.Key + "--" + t.Min(p => p.rpId));
}
//3
var ss2 = db.Am_recProScheme.GroupBy(p => p.recType);
foreach (var t in ss2)
{
Response.Write(t.Key + "--" + t.Min(p => p.rpId));
}
//4
string sssql = "select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType";
10.连接查询
//1
var ss = from r in db.Am_recProScheme
join w in db.Am_Test_Result on r.rpId equals w.rsId
orderby r.rpId descending
select r;
//2
var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList();
//3
string sssql = "select r.* from [Am_recProScheme] as r inner join [dbo].[Am_Test_Result] as t on r.[rpId] = t.[rsId] order by r.[rpId] desc";
11.sql中的In
//1
var ss = from p in db.Am_recProScheme
where (new int?[] { 24, 25,26 }).Contains(p.rpId)
select p;
foreach (var p in ss)
{
Response.Write(p.Sorts);
}
//2
string st = "select * from Am_recProScheme where rpId in(24,25,26)";
egg:例子
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Collections;
namespace fuck
{
public delegate int del(int i);
public delegate bool Mydetext();
class Program
{
//聲明委託變量
public Mydetext mytext;
static void Main(string[] args)
{
#region 例子一
//聲明委託變量
del myDelegate = x => x * x; //lambda表達式也就是匿名委託的原理
//聲明委託變量
del myDelegatetwo = delegate(int x)
{
return x * x;
};
int i = myDelegate(5);
int j = myDelegatetwo(5); //j = 25
Console.WriteLine(i);
Console.WriteLine(j);
#endregion
#region 例子二
//聲明委託變量
if (new Program() { mytext = new Mydetext(delegate() { Console.Write("Hello "); return false; }) }.mytext())
{
Console.Write("Hello ");
}
else
{
Console.WriteLine("World");
}
if (new Func<bool>(() => { Console.Write("Hello "); return false; }).Invoke())
{
Console.Write("Hello ");
}
else
{
Console.WriteLine("World");
}
if (new Program() { mytext = new Mydetext(() => { Console.Write("Hello "); return false; }) }.mytext())
{
Console.Write("Hello ");
}
else
{
Console.WriteLine("World");
}
#endregion
#region 例子三
List<int> ls = new List<int>();
ls.Add(1);
ls.Add(9);
ls.Add(5);
ls.Add(7);
var chaojie = ls.Select((m, index) => new { m, index }).OrderByDescending(n => n.m).Take(1);
//try 下面的和上面的效果是一樣子的 //這裡是這樣子理解的聲明一個匿名委託 return new { m, index };可以理解為查詢出m,和index
var chaojietwo = ls.Select(delegate(int m,int index) { return new { index, m }; }).OrderByDescending(n => n.m).Take(1);
//在這裡其實就是ls.Select(new {}) 和例子四很像 delegate(int m,int index)的參數順序決定是值,還是索引/第一個是值,第二個是索引
foreach (var w in chaojietwo)
{
Console.WriteLine("索引" + w.index + ",值" + w.m);
}
#endregion
#region 例子四
Dictionary<int,string> alt=new Dictionary<int,string>();
alt.Add(1,"一");
alt.Add(9,"九");
alt.Add(5,"五");
alt.Add(7,"七");
var query = from qy in alt
select new {qy.Key,qy.Value };
foreach (var q in alt)
{
Console.WriteLine("鍵" + q.Key + ",值" +q.Value);
}
#endregion
#region 例子五
List<double> list = new List<double>();
list.Add(2.55);
list.Add(4.45);
list.Add(12.35);
list.Add(18.35);
list.Add(17.35);
list.Add(19.35);
list.Add(6.355);
list.Add(7.95);
list.Add(4.35);
var v = list.Select((m, index) => new { m, index }).OrderByDescending(n => n.m).Take(1);
foreach (var w in v)
{
Console.WriteLine("索引"+w.index+",值"+w.m);
}
List<double> list2 = new List<double>();
list2.Add(2.55);
list2.Add(4.45);
list2.Add(18.35);
list2.Add(17.35);
list2.Add(19.35);
list2.Add(6.355);
list2.Add(7.95);
list2.Add(4.35);
//其實這兩個是一個樣子的
//var lt2 = list2.Where(a=>a==2.55);
//lt2也可以寫成
var lt2 = list2.Where(delegate(double ls11) { return ls11==2.55;});
// var lt3 = (from z in list2
// select z).Where(y=>y>3);
//其實下面這個和上面這個也是一樣子的
var lt3 = (from z in list2
select z).Where(delegate(double y) { return y > 3; });
Console.WriteLine("條件一");
foreach (var w in lt2)
{
Console.WriteLine("值"+w);
}
Console.WriteLine("條件二");
foreach (var w in lt3)
{
Console.WriteLine("值" + w);
}
#endregion
#region 例子六
//list去除掉重复行
List<int> qls = new List<int>();
qls.Add(1);
qls.Add(1);
qls.Add(3);
qls.Add(2);
qls.Add(4);
qls.Add(3);
for (int p = 0; p < qls.Count; p++)
{
int counts = -1;
for (int q = 0; q < qls.Count; q++)
{
if (qls[p] == qls[q])
{
counts=counts+1;
}
if (counts == 1)
{
qls.Remove(qls[p]);
counts=counts-1;
}
}
}
//两行效果是一样的
var qlstry = qls.OrderBy(p=>p);//进行排序
IEnumerable<int> qlstry = qls.OrderBy(p => p);
foreach (var ww in qlstry)
{
Console.WriteLine("值" + ww);
}
#endregion
Console.ReadKey();
}
}
}
效果
在公司的讲课
關於linq的最簡單例子
例子1
var ss = from r in Am_recProScheme
select r;
string sssql = "select * from Am_recProScheme";
例子2
var ss = from r in Am_recProScheme
where r.rpId > 10
select r;
string sssql = "select * from Am_recProScheme where rpid>10";
關於linq的最簡單例子
簡單函數count min max sum 的應用
例子3 var ss = (from r in Am_recProScheme
select r).Max(p=>p.rpId);
var ss = (from r in Am_recProScheme
select r).Sum(p=>p.rpId);
var ss = (from r in Am_recProScheme
select r).Min(p=>p.rpId);
string sssql = "select Max(reId) from Am_recProScheme";
string sssql = "select Sum(reId) from Am_recProScheme";
string sssql = "select Min(reId) from Am_recProScheme";
簡單order by應用
例子4
var ss = from r in Am_recProScheme
where r.rpId > 10
order by r.reId desc
select r;
string sssql = "select * from Am_recProScheme where rpid>10 order by rpId desc"
關於linq的最簡單例子
簡單函數like,in的應用
例子3 var ss = from r in Am_recProScheme
where r.SortsText.Contains("張");
select r;
string sssql = "select * from Am_recProScheme where r.SortsText like '%張%'";
var ss = from r in Am_recProScheme
where （new int[]{24,25,26}）.Contains(r.rpId);
select r;
string sssql = "select * from Am_recProScheme where r.rpId in (24,25,26)";
簡單查詢出某幾列應用
例子4
var ss = from r in Am_recProScheme
where r.rpId > 10
order by r.rpId desc
select new {r.rpId,r.rpName};
string sssql = "select rpId,rpName from Am_recProScheme where rpid>10 order by rpId desc"
關於linq的最簡單例子
簡單函數連表查詢的應用
我们在做SQL查询的时候经常会用到Inner Join,Left Join，连接方式的概念方面我想也不用给予太多解释，
我们今天的重点是让大家熟悉LINQ是如何使用Join来实现常用的表连接的。
创建测试用类：
class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
class Product
{
public int ProductId { get; set; }
public string Name { get; set; }
public string Origin { get; set; }
}
class Order
{
public int OrderId { get; set; }
public int CustomerId { get; set; }
public List<Product> Products { get; set; }
}
我们用以下例子来熟悉 Join 关键字的用法。
1.Inner Join:
CreateEntities();
var query = from c in customers
join o in orders on c.CustomerId equals o.CustomerId
where o.OrderId == 2
select c;
foreach (var customer in query)
{
Console.WriteLine("Id:{0}, Name:{1}", customer.CustomerId, customer.Name);
}
运行结果：
Id:1, Name:CA
上面这个是常见的内连接的例子，和SQL语法也很相似，但有以下几点要注意：
(1).连接条件： c.CustomerId equals o.CustomerId 只能使用 equals 不能用 =,==,等于 等表示。
以为LINQ的设计者认为 几乎所有的连接条件都是 = 条件不会出现 >,<,!= 等情况因此使用了个关键字来描述表连接条件。
(2).条件顺序：c.CustomerId equals o.CustomerId ，range variable: c 和b之前的顺序不能颠倒。
2.Group Join:
也许大家对Group Join的概念不太了解，没关系让我们通过例子来认识它：
CreateEntities();
var query = from c in customers
join o in orders on c.CustomerId equals o.CustomerId into os
select new { c, os };
foreach (var item in query)
{
Console.WriteLine("Customer Id:{0}, Name:{1}", item.c.CustomerId, item.c.Name);
foreach (var o in item.os)
{
Console.WriteLine("--Order Id:{0}", o.OrderId);
}
}
结果：
Customer Id:1, Name:CA
--Order Id:1
--Order Id:2
Customer Id:2, Name:CB
--Order Id:4
Customer Id:3, Name:CC
--Order Id:3
Customer Id:4, Name:CD
Press any key to continue . . .
以上查询返回的结果：和Group By 返回的结果非常的相似：一个KEY对象对应一个集合。
要实现Group Join我们要引入一个关键字：into
但使用时要注意一下几点：
(1).使用into 关键字后 join 后面的 range variable：o 在后面的表达式块中就失去了作用域。
(2).range variable：os 通常情况下都是IEnumerable<T>类型的。
3.Left Join:
Left Join 我们在SQL里经常用到，让我们来看看LINQ里怎么实现它：
CreateEntities();
var query = from c in customers
join o in orders on c.CustomerId equals o.CustomerId into os
from o2 in os.DefaultIfEmpty(
new Order { OrderId = 0, CustomerId = 0, Products = new List<Product>() })
select new { c, o2 };
foreach (var item in query)
{
Console.WriteLine("Customer Id:{0}, Name:{1}--Order Id:{0}",
item.c.CustomerId, item.o2.OrderId);
}
结果：
Customer Id:1, Name:1--Order Id:1
Customer Id:1, Name:2--Order Id:1
Customer Id:2, Name:4--Order Id:2
Customer Id:3, Name:3--Order Id:3
Customer Id:4, Name:0--Order Id:4
Press any key to continue . . .
我们可以看到Left Outer Join 的语法进一步的复杂化了，结果也有细微的不同。
(1).从语法上：
from o2 in os.DefaultIfEmpty(
new Order { OrderId = 0, CustomerId = 0, Products = new List<Product>() })
主要区别在于以上者1句语句。查询方法DefaultIfEmpty 用于定义当查询记录为空时，预定义默认值。再从其集合中取出子元素。
(2).从结果上: 我们在遍历查询结果时可以发现Left Join相似于Inner Join结果都是“平面”的，然而Group Join返回的结果具有层次性。
题外：
由于C#是面向对象的，往往会通过对象与对象间的外系来实现数据间关系。有时表达2个之间的关系也可以不使用Join关键字，
因此Join关键字其实在实际LINQ查询表达式中用的不是很多。
2015-03-25
以下是linq連表的例子 相當於左連接
var ls = (from i in gt.vendor_login
join j in gt.vendor on i.vendor_id equals j.vendor_id
into AAA
from j in AAA.DefaultIfEmpty()
select new myclass { AA = i.vendor_id, BB = j != null ? j.vendor_name_full : null }).ToList();
// 相當於內連接
var ls = (from i in gt.vendor_login
fromj in gt.vendor on i.vendor_id equals j.vendor_id
select new myclass { AA = i.vendor_id, BB = j != null ? j.vendor_name_full : null }).ToList();
//類似於內連接
ls = (from i in gt.vendor_login
from j in gt.vendor
where i.vendor_id==j.vendor_id
select new { i, j }).ToList().ConvertAll<VendorLoginQuery>(w => new VendorLoginQuery { login_id = w.i.login_id, vendor_id = w.i.vendor_id, login_ipfrom = w.i.login_ipfrom, login_createdate = w.i.login_createdate, vendor_name_full = w.j.vendor_name_full }).Skip(10).Take(100).ToList();