LINQ to SQL语句(1)之Where

Where操作

适用场景：实现过滤，查询等功能。

说明：与SQL命令中的Where作用相似，都是起到范围限定也就是过滤作用的 ，而判断条件就是它后面所接的子句。

Where操作包括3种形式，分别为简单形式、关系条件形式、First()形式。下 面分别用实例举例下：

1.简单形式：

例如：使用where筛选在伦敦的客户

var q =

　　from c in db.Customers

　　where c.City == "London"

　　select c;

再如：筛选1994 年或之后雇用的雇员：

var q =

　　from e in db.Employees

　　where e.HireDate >= new DateTime(1994, 1, 1)

　　select e;

2.关系条件形式：

筛选库存量在订货点水平之下但未断货的产品：

var q =

　　from p in db.Products

　　where p.UnitsInStock <= p.ReorderLevel && ! p.Discontinued

　　select p;

筛选出UnitPrice 大于10 或已停产的产品：

var q =

　　from p in db.Products

　　where p.UnitPrice > 10m || p.Discontinued

　　select p;

下面这个例子是调用两次where以筛选出UnitPrice大于10且已停产的产品。

var q =

　　db.Products.Where(p=>p.UnitPrice > 10m).Where (p=>p.Discontinued);

3.First()形式：

返回集合中的一个元素，其实质就是在SQL语句中加TOP (1)。

简单用法：选择表中的第一个发货方。

Shipper shipper = db.Shippers.First();

元素：选择CustomerID 为“BONAP”的单个客户

Customer cust = db.Customers.First(c => c.CustomerID == "BONAP");

条件：选择运费大于 10.00 的订单：

Order ord = db.Orders.First(o => o.Freight > 10.00M);

LINQ to SQL语句(2)之Select/Distinct

[1] Select介绍1

[2] Select介绍2

[3] Select介绍3和 Distinct介绍

Select/Distinct操作符

适用场景：o(∩_∩) o… 查询呗。

说明：和SQL命令中的select作用相似但位置不同， 查询表达式中的select及所接子句是放在表达式最后并把子句中的变量也就是结 果返回回来；延迟。

Select/Distinct操作包括9种形式，分别为简单用 法、匿名类型形式、条件形式、指定类型形式、筛选形式、整形类型形式、嵌套 类型形式、本地方法调用形式、Distinct形式。

1.简单用法：

这个示 例返回仅含客户联系人姓名的序列。

var q =

　　from c in db.Customers

　　select c.ContactName;

注意：这个语句 只是一个声明或者一个描述，并没有真正把数据取出来，只有当你需要该数据的 时候，它才会执行这个语句，这就是延迟加载(deferred loading)。如果，在声 明的时候就返回的结果集是对象的集合。你可以使用ToList() 或ToArray()方法 把查询结果先进行保存，然后再对这个集合进行查询。当然延迟加载(deferred loading)可以像拼接SQL语句那样拼接查询语法，再执行它。

2.匿名类型 形式：

说明：匿名类型是C#3.0中新特性。其实质是编译器根据我们自定义自 动产生一个匿名的类来帮助我们实现临时变量的储存。匿名类型还依赖于另外一 个特性：支持根据property来创建对象。比如，var d = new { Name = "s" };编译器自动产生一个有property叫做Name的匿名类，然后按这 个类型分配内存，并初始化对象。但是var d = new {"s"};是编译不 通过的。因为，编译器不知道匿名类中的property的名字。例如string c = "d";var d = new { c}; 则是可以通过编译的。编译器会创建一个叫 做匿名类带有叫c的property。

例如下例：new {c,ContactName,c.Phone};ContactName和Phone都是在映射文件中定义与表中字 段相对应的property。编译器读取数据并创建对象时，会创建一个匿名类，这个 类有两个属性，为ContactName和Phone，然后根据数据初始化对象。另外编译器 还可以重命名property的名字。

var q =

　　from c in db.Customers

　　select new {c.ContactName, c.Phone};

上 面语句描述：使用 SELECT 和匿名类型返回仅含客户联系人姓名和电话号码的序 列

var q =

　　from e in db.Employees

　　select new

　　{

　　　　Name = e.FirstName + " " + e.LastName,

　　　　Phone = e.HomePhone

　　};

上面 语句描述：使用SELECT和匿名类型返回仅含雇员姓名和电话号码的序列，并将 FirstName和LastName字段合并为一个字段“Name”，此外在所得的 序列中将HomePhone字段重命名为Phone。

var q =

　　from p in db.Products

　　select new

　　{

　　　　 p.ProductID,

　　　　HalfPrice = p.UnitPrice / 2

　 　};

上面语句描述：使用SELECT和匿名类型返回所有产品的ID以及 HalfPrice(设置为产品单价除以2所得的值)的序列。

3.条件形式：

说 明：生成SQL语句为：case when condition then else。

var q =

　　from p in db.Products

　　select new

　　{

　　　　p.ProductName,

　　　　Availability =

　　　　 p.UnitsInStock - p.UnitsOnOrder < 0 ?

　　　　"Out Of Stock" : "In Stock"

　　};

上面语句描述： 使用SELECT和条件语句返回产品名称和产品供货状态的序列。

4.指定类 型形式：

说明：该形式返回你自定义类型的对象集。

var q =

　　from e in db.Employees

　　select new Name

　　 {

　　　　FirstName = e.FirstName,

　　　　LastName = e.LastName

　　};

上面语句描述：使用SELECT和已知类型返回 雇员姓名的序列。

5.筛选形式：

说明：结合where使用，起到过滤作 用。

var q =

　　from c in db.Customers

　　 where c.City == "London"

　　select c.ContactName;

上面语句描述：使用SELECT和WHERE返回仅含伦敦客 户联系人姓名的序列。

6.shaped形式(整形类型)：

说明：其select操 作使用了匿名对象，而这个匿名对象中，其属性也是个匿名对象。

var q =

　　from c in db.Customers

　　select new {

　　　　c.CustomerID,

　　　　CompanyInfo = new {c.CompanyName, c.City, c.Country},

　　　　ContactInfo = new {c.ContactName, c.ContactTitle}

　　};

语句描述：使用 SELECT 和匿名类型返回有关客户的数据的整形子集。查询顾客的ID和公司信息 （公司名称，城市，国家）以及联系信息（联系人和职位）。

7.嵌套类型形式：

说明：返回的对象集中的每个对象DiscountedProducts属性中，又 包含一个集合。也就是每个对象也是一个集合类。

var q =

　　from o in db.Orders

　　select new {

　　　　 o.OrderID,

　　　　DiscountedProducts =

　　　　　　from od in o.OrderDetails

　　　　　　where od.Discount > 0.0

　 　　　　　select od,

　　　　FreeShippingDiscount = o.Freight

　　};

语句描述：使用嵌套查询返回所有订单及其OrderID 的序列 、打折订单中项目的子序列以及免送货所省下的金额。

8.本地方法调用 形式(LocalMethodCall)：

这个例子在查询中调用本地方法 PhoneNumberConverter将电话号码转换为国际格式。

var q = from c in db.Customers

　　　　 where c.Country == "UK" || c.Country == "USA"

　　　　 select new

　　　　 {

　　　　　　 c.CustomerID,

　　　　　　 c.CompanyName,

　　　　　　 Phone = c.Phone,

　　　　　　 InternationalPhone =

　　　　　　 PhoneNumberConverter(c.Country, c.Phone)

　　 　　 };

PhoneNumberConverter方法如下：

public string PhoneNumberConverter(string Country, string Phone)

{

　　Phone = Phone.Replace(" ", "").Replace (")", ")-");

　　switch (Country)

　　 {

　　　　case "USA":

　　　　　　return "1- " + Phone;

　　　　case "UK":

　　　　　　 return "44-" + Phone;

　　　　default:

　　　　　 　return Phone;

　　}

}

下面也是使用了这个方法将电 话号码转换为国际格式并创建XDocument

XDocument doc = new XDocument(

　　new XElement("Customers", from c in db.Customers

　　　　　　　where c.Country == "UK" || c.Country == "USA"

　　　　　　　select (new XElement ("Customer",

　　　　　　　　　　　new XAttribute ("CustomerID", c.CustomerID),

　　　　　　　　　　　new XAttribute("CompanyName", c.CompanyName),

　　　　　　 　　　　　new XAttribute("InterationalPhone",

　　　　 　　　　　　　 PhoneNumberConverter(c.Country, c.Phone))

　　　　 　　　　　　 ))));

9.Distinct形式：

说明：筛选字段中不相同的值 。用于查询不重复的结果集。生成SQL语句为：SELECT DISTINCT [City] FROM [Customers]

var q = (

　　from c in db.Customers

　　select c.City )

　　.Distinct();

语句描述：查询顾 客覆盖的国家。

LINQ to SQL语句(3)之Count/Sum/Min/Max/Avg

[1] Count/Sum讲解

[2] Min讲解

[3] Max讲解

[4] Average和Aggregate讲解

Count/Sum/Min/Max/Avg操作符

适用场景： 统计数据吧，比如统计一些数据的个数，求和，最小值，最大值，平均数。

Count

说明：返回集合中的元素个数，返回INT类型；不延迟。生成 SQL语句为：SELECT COUNT(*) FROM

1.简单形式：

得到数据库中客户 的数量：

var q = db.Customers.Count();

2.带条件形 式：

得到数据库中未断货产品的数量：

var q = db.Products.Count(p => !p.Discontinued);

LongCount

说明 ：返回集合中的元素个数，返回LONG类型；不延迟。对于元素个数较多的集合可 视情况可以选用LongCount来统计元素个数，它返回long类型，比较精确。生成 SQL语句为：SELECT COUNT_BIG(*) FROM

var q = db.Customers.LongCount();

Sum

说明：返回集合中数值类型元素 之和，集合应为INT类型集合；不延迟。生成SQL语句为：SELECT SUM(…) FROM

1.简单形式：

得到所有订单的总运费：

var q = db.Orders.Select(o => o.Freight).Sum();

2.映射形式：

得 到所有产品的订货总数：

var q = db.Products.Sum(p => p.UnitsOnOrder);

Min

说明：返回集合中元素的最小值；不延迟。 生成SQL语句为：SELECT MIN(…) FROM

1.简单形式：

查找任 意产品的最低单价：

var q = db.Products.Select(p => p.UnitPrice).Min();

2.映射形式：

查找任意订单的最低运费：

var q = db.Orders.Min(o => o.Freight);

3.元素 ：

查找每个类别中单价最低的产品：

var categories =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　CategoryID = g.Key,

　　　　 CheapestProducts =

　　　　　　from p2 in g

　　　　　　 where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)

　　　　　　 select p2

　　};

Max

说明：返回集合中元素的最大值；不延 迟。生成SQL语句为：SELECT MAX(…) FROM

1.简单形式：

查 找任意雇员的最近雇用日期：

var q = db.Employees.Select(e => e.HireDate).Max();

2.映射形式：

查找任意产品的最大库存量：

var q = db.Products.Max(p => p.UnitsInStock);

3.元素：

查找每个类别中单价最高的产品：

var categories =

　　from p in db.Products

　　 group p by p.CategoryID into g

　　select new {

　　　　 g.Key,

　　　　MostExpensiveProducts =

　　　　　　from p2 in g

　　　　　　where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)

　　　　　　select p2

　　};

Average

说明：返回集合中的数值类型元素的平均值。集合应为数字类型集合，其返回 值类型为double；不延迟。生成SQL语句为：SELECT AVG(…) FROM

1.简单形式：

得到所有订单的平均运费：

var q = db.Orders.Select(o => o.Freight).Average();

2.映射形式：

得到所有产品的平均单价：

var q = db.Products.Average(p => p.UnitPrice);

3.元素：

查找每个类别中单价高于该类别平 均单价的产品：

var categories =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　ExpensiveProducts =

　　　 　　　from p2 in g

　　　　　　where p2.UnitPrice > g.Average (p3 => p3.UnitPrice)

　　　　　　select p2

　 　};

Aggregate

说明：根据输入的表达式获取聚合值；不延迟。即 是说：用一个种子值与当前元素通过指定的函数来进行对比来遍历集合中的元素 ，符合条件的元素保留下来。如果没有指定种子值的话，种子值默认为集合的第 一个元素。

LINQ to SQL语句(4)之Join

Join操作符

适用场景：在我们表关系中有一对一关系，一对多关系， 多对多关系等。对各个表之间的关系，就用这些实现对多个表的操作。

说明：在Join操作中，分别为Join(Join查询), SelectMany(Select一对多选择) 和GroupJoin(分组Join查询)。

该扩展方法对两个序列中键匹配的元素进 行inner join操作

SelectMany

说明：我们在写查询语句时，如果 被翻译成SelectMany需要满足2个条件。1：查询语句中没有join和into，2：必 须出现EntitySet。在我们表关系中有一对一关系，一对多关系，多对多关系等 ，下面分别介绍一下。

1.一对多关系(1 to Many)：

var q =

　　from c in db.Customers

　　from o in c.Orders

　 　where c.City == "London"

　　select o;

语句描述：Customers与Orders是一对多关系。即Orders在Customers类中以 EntitySet形式出现。所以第二个from是从c.Orders而不是db.Orders里进行筛选 。这个例子在From子句中使用外键导航选择伦敦客户的所有订单。

var q =

　　from p in db.Products

　　where p.Supplier.Country == "USA" && p.UnitsInStock == 0

　　select p;

语句描述：这一句使用了 p.Supplier.Country条件，间接关联了Supplier表。这个例子在Where子句中使 用外键导航筛选其供应商在美国且缺货的产品。生成SQL语句为：

SELECT [t0].[ProductID], [t0].[ProductName], [t0]. [SupplierID],

[t0].[CategoryID],[t0].[QuantityPerUnit],[t0]. [UnitPrice],

[t0].[UnitsInStock], [t0].[UnitsOnOrder],[t0]. [ReorderLevel],

[t0].[Discontinued] FROM [dbo].[Products] AS [t0]

LEFT OUTER JOIN [dbo].[Suppliers] AS [t1] ON

[t1]. [SupplierID] = [t0].[SupplierID]

WHERE ([t1].[Country] = @p0) AND ([t0].[UnitsInStock] = @p1)

-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]

-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]

2.多对多关系(Many to Many)：

var q =

　　from e in db.Employees

　　from et in e.EmployeeTerritories

　　where e.City == "Seattle"

　　select new

　　{

　　　　e.FirstName,

　　　　e.LastName,

　　　　et.Territory.TerritoryDescription

　 　};

说明：多对多关系一般会涉及三个表(如果有一个表是自关 联的，那有可能只有2个表)。这一句语句涉及Employees, EmployeeTerritories, Territories三个表。它们的关系是1：M：1。Employees 和Territories没有很明确的关系。

语句描述：这个例子在From子句中使 用外键导航筛选在西雅图的雇员，同时列出其所在地区。这条生成SQL语句为：

SELECT [t0].[FirstName], [t0].[LastName], [t2]. [TerritoryDescription]

FROM [dbo].[Employees] AS [t0] CROSS JOIN [dbo].[EmployeeTerritories]

AS [t1] INNER JOIN [dbo]. [Territories] AS [t2] ON

[t2].[TerritoryID] = [t1].[TerritoryID]

WHERE ([t0].[City] = @p0) AND ([t1].[EmployeeID] = [t0]. [EmployeeID])

-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Seattle]

3.自联接关系：

var q =

　　from e1 in db.Employees

　　from e2 in e1.Employees

　　where e1.City == e2.City

　　select new {

　　　　FirstName1 = e1.FirstName, LastName1 = e1.LastName,

　　　　FirstName2 = e2.FirstName, LastName2 = e2.LastName,

　　　　e1.City

　 　};

语句描述：这个例子在select 子句中使用外键导航筛选成 对的雇员，每对中一个雇员隶属于另一个雇员，且两个雇员都来自相同城市。生 成SQL语句为：

SELECT [t0].[FirstName] AS [FirstName1], [t0].[LastName] AS

[LastName1],[t1].[FirstName] AS [FirstName2], [t1].[LastName] AS

[LastName2],[t0].[City] FROM [dbo].[Employees] AS [t0],

[dbo].[Employees] AS [t1] WHERE ([t0].[City] = [t1]. [City]) AND

([t1].[ReportsTo] = [t0].[EmployeeID])

GroupJoin

像上面所说的，没有join和into，被翻译成 SelectMany，同时有join和into时，那么就被翻译为GroupJoin。在这里into的 概念是对其结果进行重新命名。

1.双向联接(Two way join)：

此 示例显式联接两个表并从这两个表投影出结果：

var q =

　　 from c in db.Customers

　　join o in db.Orders on c.CustomerID

　　equals o.CustomerID into orders

　　select new

　　 {

　　　　c.ContactName,

　　　　OrderCount = orders.Count ()

　　};

说明：在一对多关系中，左边是1，它每条记录 为c（from c in db.Customers），右边是Many，其每条记录叫做o ( join o in db.Orders )，每对应左边的一个c，就会有一组o，那这一组o，就叫做orders， 也就是说，我们把一组o命名为orders，这就是into用途。这也就是为什么在 select语句中，orders可以调用聚合函数Count。在T-SQL中，使用其内嵌的T- SQL返回值作为字段值。如图所示：

生成SQL语句为：

SELECT [t0].[ContactName], (

　　SELECT COUNT(*)

　　FROM [dbo].[Orders] AS [t1]

　　 WHERE [t0].[CustomerID] = [t1].[CustomerID]

) AS [OrderCount]

FROM [dbo].[Customers] AS [t0]

2.三向联接(There way join)：

此示例显式联接三个表并分别从每个表投影出结果：

var q =

　　from c in db.Customers

　　join o in db.Orders on c.CustomerID

　　equals o.CustomerID into ords

　　join e in db.Employees on c.City

　　equals e.City into emps

　　select new

　　{

　　　　c.ContactName,

　　　　ords = ords.Count(),

　　　　emps = emps.Count()

　 　};

生成SQL语句为：

SELECT [t0]. [ContactName], (

　　SELECT COUNT(*)

　　FROM [dbo].[Orders] AS [t1]

　　WHERE [t0].[CustomerID] = [t1].[CustomerID]

) AS [ords], (

SELECT COUNT(*)

　　FROM [dbo].[Employees] AS [t2]

　　WHERE [t0].[City] = [t2].[City]

) AS [emps]

FROM [dbo].[Customers] AS [t0]

3.左外部联接(Left Outer Join)：

此示例说明如何通过使用 此示例说明如何通过使用 DefaultIfEmpty() 获取左外部联接。在雇员没有订单时，DefaultIfEmpty()方 法返回null：

var q =

　　from e in db.Employees

　　 join o in db.Orders on e equals o.Employee into ords

　　from o in ords.DefaultIfEmpty()

　　select new

　　{

　　　　 e.FirstName,

　　　　e.LastName,

　　　　Order = o

　　 };

说明：以Employees左表，Orders右表，Orders 表中为空时， 用null值填充。Join的结果重命名ords，使用DefaultIfEmpty()函数对其再次查 询。其最后的结果中有个Order，因为from o in ords.DefaultIfEmpty() 是对 ords组再一次遍历，所以，最后结果中的Order并不是一个集合。但是，如果没 有from o in ords.DefaultIfEmpty() 这句，最后的select语句写成select new { e.FirstName, e.LastName, Order = ords }的话，那么Order就是一个集合。

4.投影的Let赋值(Projected let assignment)：

说明：let语句 是重命名。let位于第一个from和select语句之间。

这个例子从联接投影 出最终“Let”表达式：

var q =

　　from c in db.Customers

　　join o in db.Orders on c.CustomerID

　　 equals o.CustomerID into ords

　　let z = c.City + c.Country

　　from o in ords

　　select new

　　{

　　　　 c.ContactName,

　　　　o.OrderID,

　　　　z

　 　};

5.组合键(Composite Key)：

这个例子显示带有组合 键的联接：

var q =

　　from o in db.Orders

　　from p in db.Products

　　join d in db.OrderDetails

　　　　on new

　　　　{

　　　　　　o.OrderID,

　　　　　　 p.ProductID

　　　　} equals

　　　　　　new

　　　　　 　{

　　　　　　　　d.OrderID,

　　　　　　　　 d.ProductID

　　　　　　}

　　　　into details

　　from d in details

　　select new

　　{

　　　　o.OrderID,

　　　　p.ProductID,

　　　　d.UnitPrice

　 　};

说明：使用三个表，并且用匿名类来说明：使用三个表，并 且用匿名类来表示它们之间的关系。它们之间的关系不能用一个键描述清楚，所 以用匿名类，来表示组合键。还有一种是两个表之间是用组合键表示关系的，不 需要使用匿名类。

6.可为null/不可为null的键关系 (Nullable/Nonnullable Key Relationship)：

这个实例显示如何构造一 侧可为 null 而另一侧不可为 null 的联接：

var q =

　　 from o in db.Orders

　　join e in db.Employees

　　　　on o.EmployeeID equals

　　　　(int?)e.EmployeeID into emps

　 　from e in emps

　　select new

　　{

　　　　 o.OrderID,

　　　　e.FirstName

　　};

LINQ to SQL语句(5)之Order By

Order By操作

适用场景：对查询出的语句进行排序，比如按时间排序 等等。

说明：按指定表达式对集合排序；延迟，：按指定表达式对集合 排序；延迟，默认是升序，加上descending表示降序，对应的扩展方法是 OrderBy和OrderByDescending

1.简单形式

这个例子使用 orderby 按雇用日期对雇员进行排序：

var q =

　　from e in db.Employees

　　orderby e.HireDate

　　select e;

说明：默认为升序

2.带条件形式

注意：Where 和Order By的顺序并不重要。而在T-SQL中，Where和Order By有严格的位置限制 。

var q =

　　from o in db.Orders

　　where o.ShipCity == "London"

　　orderby o.Freight

　　 select o;

语句描述：使用where和orderby按运费进行排序。

3.降序排序

var q =

　　from p in db.Products

　　orderby p.UnitPrice descending

　　select p;

4.ThenBy

语句描述：使用复合的 orderby 对客户进行 排序，进行排序：

var q =

　　from c in db.Customers

　　orderby c.City, c.ContactName

　　select c;

说明：按多个表达式进行排序，例如先按City排序，当City相 同时，按ContactName排序。这一句用Lambda表达式像这样写：

var q =

　　.OrderBy(c => c.City)

　 　.ThenBy(c => c.ContactName).ToList();

在T-SQL中没有 ThenBy语句，其依然翻译为OrderBy，所以也可以用下面语句来表达：

var q =

　　db.Customers

　　.OrderBy(c => c.ContactName)

　　.OrderBy(c => c.City).ToList ();

所要注意的是，多个OrderBy操作时，级连方式是按逆序。对 于降序的，用相应的降序操作符替换即可。

var q =

　　 db.Customers

　　.OrderByDescending(c => c.City)

　 　.ThenByDescending(c => c.ContactName).ToList();

需要 说明的是，OrderBy操作，不支持按type排序，也不支持匿名类。比如

var q =

　　db.Customers

　　.OrderBy(c => new

　　{

　　　　c.City,

　　　　c.ContactName

　 　}).ToList();

会被抛出异常。错误是前面的操作有匿名类，再 跟OrderBy时，比较的是类别。比如

var q =

　　 db.Customers

　　.Select(c => new

　　{

　　　　 c.City,

　　　　c.Address

　　})

　　.OrderBy(c => c).ToList();

如果你想使用OrderBy(c => c)，其前提条件是 ，前面步骤中，所产生的对象的类别必须为C#语言的基本类型。比如下句，这里 City为string类型。

var q =

　　db.Customers

　　 .Select(c => c.City)

　　.OrderBy(c => c).ToList ();

5.ThenByDescending

这两个扩展方式都是用在 OrderBy/OrderByDescending之后的，第一个ThenBy/ThenByDescending扩展方法 作为第二位排序依据，第二个ThenBy/ThenByDescending则作为第三位排序依据 ，以此类推

var q =

　　from o in db.Orders

　　 where o.EmployeeID == 1

　　orderby o.ShipCountry, o.Freight descending

　　select o;

语句描述：使用orderby先按发 往国家再按运费从高到低的顺序对 EmployeeID 1 的订单进行排序。

6. 带GroupBy形式

var q =

　　from p in db.Products

　　 group p by p.CategoryID into g

　　orderby g.Key

　　select new {

　　　　g.Key,

　　　　MostExpensiveProducts =

　 　　　　　from p2 in g

　　　　　　where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)

　　　　　　select p2

　 　};

语句描述：使用orderby、Max 和 Group By 得出每种类别 中单价最高的产品，并按 CategoryID 对这组产品进行排序。

LINQ to SQL语句(6)之Group By/Having

Group By/Having操作符

适用场景：分组数据，为我们查找数据缩小 范围。

说明：分配并返回对传入参数进行分组操作后的可枚举对象。分 组；延迟

1.简单形式：

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select g;

语句描述：使用Group By按CategoryID划分产品。

说 明：from p in db.Products 表示从表中将产品对象取出来。group p by p.CategoryID into g表示对p按CategoryID字段归类。其结果命名为g，一旦重 新命名，p的作用域就结束了，所以，最后select时，只能select g。当然，也 不必重新命名可以这样写：

var q =

　　from p in db.Products

　　group p by p.CategoryID;

我们用示意 图表示：

如果想遍历某类别中所有记录，这样：

foreach (var gp in q)

{

　　if (gp.Key == 2)

　　{

　　　　 foreach (var item in gp)

　　　　{

　　　　　　//do something

　　　　}

　　}

}

2.Select匿名类 ：

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new { CategoryID = g.Key, g };

说明：在这句LINQ语句中，有2个property：CategoryID和g。这个匿名类，其实质是对返回结果集重新进行了包装。把g的property封装成一 个完整的分组。如下图所示：

如果想遍历某匿名类中所有记录，要这么做：

foreach (var gp in q)

{

　　if (gp.CategoryID == 2)

　　{

　　　　foreach (var item in gp.g)

　　　　{

　　　　　 　//do something

　　　　}

　　}

}

3.最大 值

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　MaxPrice = g.Max(p => p.UnitPrice)

　 　};

语句描述：使用Group By和Max查找每个CategoryID的最高 单价。

说明：先按CategoryID归类，判断各个分类产品中单价最大的 Products。取出CategoryID值，并把UnitPrice值赋给MaxPrice。

4.最小 值

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　MinPrice = g.Min(p => p.UnitPrice)

　 　};

语句描述：使用Group By和Min查找每个CategoryID的最低 单价。

说明：先按CategoryID归类，判断各个分类产品中单价最小的 Products。取出CategoryID值，并把UnitPrice值赋给MinPrice。

5.平均 值

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　AveragePrice = g.Average(p => p.UnitPrice)

　 　};

语句描述：使用Group By和Average得到每个CategoryID的 平均单价。

说明：先按CategoryID归类，取出CategoryID值和各个分类 产品中单价的平均值。

6.求和

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　TotalPrice = g.Sum(p => p.UnitPrice)

　　};

语句描述：使用Group By和Sum得到 每个CategoryID 的单价总计。

说明：先按CategoryID归类，取出 CategoryID值和各个分类产品中单价的总和。

7.计数

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　NumProducts = g.Count()

　　};

语句描述：使用Group By和Count得到每 个CategoryID中产品的数量。

说明：先按CategoryID归类，取出 CategoryID值和各个分类产品的数量。

8.带条件计数

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　select new {

　　　　g.Key,

　　　　NumProducts = g.Count(p => p.Discontinued)

　　};

语句描述：使 用Group By和Count得到每个CategoryID中断货产品的数量。

说明：先按 CategoryID归类，取出CategoryID值和各个分类产品的断货数量。Count函数里 ，使用了Lambda表达式，Lambda表达式中的p，代表这个组里的一个元素或对象 ，即某一个产品。

9.Where限制

var q =

　　from p in db.Products

　　group p by p.CategoryID into g

　　where g.Count() >= 10

　　select new {

　　　　g.Key,

　　 　　ProductCount = g.Count()

　　};

语句描述：根据产 品的―ID分组，查询产品数量大于10的ID和产品数量。这个示例在Group By子句 后使用Where子句查找所有至少有10种产品的类别。

说明：在翻译成SQL 语句时，在最外层嵌套了Where条件。

10.多列(Multiple Columns)

var categories =

　　from p in db.Products

　　 group p by new

　　{

　　　　p.CategoryID,

　　　　 p.SupplierID

　　}

　　　　into g

　　　　select new

　　　　　　{

　　　　　　　　g.Key,

　　　　　　　　g

　　　　　　};

语句描述：使用Group By按CategoryID和 SupplierID将产品分组。

说明：既按产品的分类，又按供应商分类。在 by后面，new出来一个匿名类。这里，Key其实质是一个类的对象，Key包含两个 Property：CategoryID、SupplierID。用g.Key.CategoryID可以遍历CategoryID 的值。

11.表达式(Expression)

var categories =

　　 from p in db.Products

　　group p by new { Criterion = p.UnitPrice > 10 } into g

　　select g;

语句描述 ：使用Group By返回两个产品序列。第一个序列包含单价大于10的产品。第二个 序列包含单价小于或等于10的产品。

说明：按产品单价是否大于10分类 。其结果分为两类，大于的是一类，小于及等于为另一类。

LINQ to SQL语句(7)之Exists/In/Any/All/Contains

Exists/In/Any/All/Contains操作符

适用场景：用于判断集合中元素 ，进一步缩小范围。

Any

说明：用于判断集合中是否有元素满足 某一条件；不延迟。（若条件为空，则集合只要不为空就返回True，否则为 False）。有2种形式，分别为简单形式和带条件形式。

1.简单形式：

仅返回没有订单的客户：

var q =

　　from c in db.Customers

　　where !c.Orders.Any()

　　select c;

生成SQL语句为：

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],

[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],

[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE NOT (EXISTS(

　　SELECT NULL AS [EMPTY] FROM [dbo].[Orders] AS [t1]

　　WHERE [t1].[CustomerID] = [t0]. [CustomerID]

　 ))

2.带条件形式：

仅返回至少有 一种产品断货的类别：

var q =

　　from c in db.Categories

　　where c.Products.Any(p => p.Discontinued)

　　select c;

生成SQL语句为：

SELECT [t0]. [CategoryID], [t0].[CategoryName], [t0].[Description],

[t0]. [Picture] FROM [dbo].[Categories] AS [t0]

WHERE EXISTS(

　　 SELECT NULL AS [EMPTY] FROM [dbo].[Products] AS [t1]

　　WHERE ([t1].[Discontinued] = 1) AND

　　([t1].[CategoryID] = [t0]. [CategoryID])

　　)

All

说明：用于判断集合中所有元素是 否都满足某一条件；不延迟1.带条件形式

var q =

　　from c in db.Customers

　　where c.Orders.All(o => o.ShipCity == c.City)

　　select c;

语句描述：这个例子返回所有订单 都运往其所在城市的客户或未下订单的客户。

Contains

说明：用 于判断集合中是否包含有某一元素；不延迟。它是对两个序列进行连接操作的。

string[] customerID_Set =

　　new string[] { "AROUT", "BOLID", "FISSA" };

var q = (

　　from o in db.Orders

　　where customerID_Set.Contains(o.CustomerID)

　　select o).ToList ();

语句描述：查找"AROUT", "BOLID" 和 "FISSA" 这三个客户的订单。先定义了一个数组，在LINQ to SQL中 使用Contains，数组中包含了所有的CustomerID，即返回结果中，所有的 CustomerID都在这个集合内。也就是in。 你也可以把数组的定义放在LINQ to SQL语句里。比如：

var q = (

　　from o in db.Orders

　　where (

　　new string[] { "AROUT", "BOLID", "FISSA" })

　　.Contains (o.CustomerID)

　　select o).ToList();

Not Contains则取反：

var q = (

　　from o in db.Orders

　　where !(

　 　new string[] { "AROUT", "BOLID", "FISSA" })

　　.Contains(o.CustomerID)

　　select o).ToList();

1.包含一个对象：

var order = (from o in db.Orders

　　　　　　 where o.OrderID == 10248

　　　　　　 select o).First();

var q = db.Customers.Where(p => p.Orders.Contains(order)).ToList();

foreach (var cust in q)

{

　　foreach (var ord in cust.Orders)

　　{

　 　　　//do something

　　}

}

语句描述：这个例子 使用Contain查找哪个客户包含OrderID为10248的订单。

2.包含多个值：

string[] cities =

　　new string[] { "Seattle", "London", "Vancouver", "Paris" };

var q = db.Customers.Where (p=>cities.Contains(p.City)).ToList();

语句描述：这个 例子使用Contains查找其所在城市为西雅图、伦敦、巴黎或温哥华的客户。

LINQ to SQL语句(8)之Concat/Union/Intersect/Except

Concat/Union/Intersect/Except操作

适用场景：对两个集合的处理，例 如追加、合并、取相同项、相交项等等。

Concat（连接）

说明：连接 不同的集合，不会自动过滤相同项；延迟。

1.简单形式：

var q = (

　　　　 from c in db.Customers

　　　　 select c.Phone

　　　　).Concat(

　　　　 from c in db.Customers

　　　　 select c.Fax

　　　　).Concat(

　　　　 from e in db.Employees

　　　　 select e.HomePhone

　　　 　);

语句描述：返回所有消费者和雇员的电话和传真。

2.复 合形式：

var q = (

　　　　 from c in db.Customers

　　 　　 select new

　　　　 {

　　　　　　 Name = c.CompanyName,

　　　　　　 c.Phone

　　　　 }

　　　　 ).Concat(

　　　　 from e in db.Employees

　　　　 select new

　　　　 {

　　　　　　 Name = e.FirstName + " " + e.LastName,

　　　　　　 Phone = e.HomePhone

　　　 　 }

　　　　);

语句描述：返回所有消费者和雇员的姓名和电 话。

Union（合并）

说明：连接不同的集合，自动过滤相同项；延迟 。即是将两个集合进行合并操作，过滤相同的项。

var q = (

　　　　 from c in db.Customers

　　　　 select c.Country

　　　　).Union(

　　　　 from e in db.Employees

　　　　 select e.Country

　　　　);

语句描述：查询顾客和职员所在 的国家。

Intersect（相交）

说明：取相交项；延迟。即是获取不同 集合的相同项（交集）。即先遍历第一个集合，找出所有唯一的元素，然后遍历 第二个集合，并将每个元素与前面找出的元素作对比，返回所有在两个集合内都 出现的元素。

var q = (

　　　　 from c in db.Customers

　　　　 select c.Country

　　　　).Intersect (

　　　　 from e in db.Employees

　　　　 select e.Country

　　　　);

语句描述：查询顾客和职员同在的国家 。

Except（与非）

说明：排除相交项；延迟。即是从某集合中删除与 另一个集合中相同的项。先遍历第一个集合，找出所有唯一的元素，然后再遍历 第二个集合，返回第二个集合中所有未出现在前面所得元素集合中的元素。

var q = (

　　　　 from c in db.Customers

　　 　　 select c.Country

　　　　).Except(

　　　　 from e in db.Employees

　　　　 select e.Country

　　　　);

语句描述：查询顾客和职员不同的国家。

LINQ to SQL语句(9)之Top/Bottom和Paging和SqlMethods

Top/Bottom操作

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

Take

说明：获取集合的前n个元素；延迟。即只 返回限定数量的结果集。

var q = (

　　from e in db.Employees

　　orderby e.HireDate

　　select e)

　 　.Take(5);

语句描述：选择所雇用的前5个雇员。

Skip

说 明：跳过集合的前n个元素；延迟。即我们跳过给定的数目返回后面的结果集。

var q = (

　　from p in db.Products

　　orderby p.UnitPrice descending

　　select p)

　　.Skip (10);

语句描述：选择10种最贵产品之外的所有产品。

TakeWhile

说明：直到某一条件成立就停止获取；延迟。即用其条件 去依次判断源序列中的元素，返回符合判断条件的元素，该判断操作将在返回 false或源序列的末尾结束 。

SkipWhile

说明：直到某一条件成立就 停止跳过；延迟。即用其条件去判断源序列中的元素并且跳过第一个符合判断条 件的元素，一旦判断返回false，接下来将不再进行判断并返回剩下的所有元素 。

Paging（分页）操作

适用场景：结合Skip和Take就可实现对数据分 页操作。

1.索引

var q = (

　　from c in db.Customers

　　orderby c.ContactName

　　select c)

　 　.Skip(50)

　　.Take(10);

语句描述：使用Skip和Take运算 符进行分页，跳过前50条记录，然后返回接下来10条记录，因此提供显示 Products表第6页的数据。

2.按唯一键排序

var q = (

　　 from p in db.Products

　　where p.ProductID > 50

　　 orderby p.ProductID

　　select p)

　　.Take(10);

语 句描述：使用Where子句和Take运算符进行分页，首先筛选得到仅50 (第5页最后 一个ProductID)以上的ProductID，然后按ProductID排序，最后取前10个结果， 因此提供Products表第6页的数据。请注意，此方法仅适用于按唯一键排序的情 况。

SqlMethods操作

在LINQ to SQL语句中，为我们提供了 SqlMethods操作，进一步为我们提供了方便，例如Like方法用于自定义通配表达 式，Equals用于相比较是否相等。

Like

自定义的通配表达式。%表示 零长度或任意长度的字符串；_表示一个字符；[]表示在某范围区间的一个字符 ；[^]表示不在某范围区间的一个字符。比如查询消费者ID以“C”开 头的消费者。

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 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");

语句描述：这个例子创建一个已编译查询， 然后使用它检索输入城市的客户。

Linq to Sql语法及实例大全

原文：http://www.cnblogs.com/lgx5/p/5449655.html