sqlce wp from查询语句详解

http://msdn.microsoft.com/zh-cn/library/bb383978(v=vs.110).aspx

from 子句（C# 参考）

Visual Studio 2012

其他版本

此主题尚未评级 - 评价此主题

[本文档仅供预览，在以后的发行版中可能会发生更改。包含的空白主题用作占位符。]

查询表达式必须以 from 子句开头。 另外，查询表达式还可以包含子查询，子查询也是以 from 子句开头。 from 子句指定以下内容：

• 将对其运行查询或子查询的数据源。

• 一个本地范围变量，表示源序列中的每个元素。

范围变量和数据源都是强类型。 from 子句中引用的数据源的类型必须为 IEnumerable、IEnumerable<T> 或一种派生类型（如 IQueryable<T>）。

在下面的示例中，numbers 是数据源，而 num 是范围变量。 请注意，这两个变量都是强类型，即使使用了 var 关键字也是如此。

C#

class LowNums
{static void Main(){   // A simple data source.int[] numbers = { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 };// Create the query.// lowNums is an IEnumerable<int>var lowNums = from num in numberswhere num < 5select num;// Execute the query.foreach (int i in lowNums){Console.Write(i + " ");}}        
}
// Output: 4 1 3 2 0

范围变量

---

如果数据源实现了 IEnumerable<T>，则编译器可以推断范围变量的类型。 例如，如果数据源的类型为 IEnumerable<Customer>，则推断出范围变量的类型为 Customer。 仅当数据源是非泛型 IEnumerable 类型（如 ArrayList）时，才必须显式指定数据源类型。 有关更多信息，请参见 如何：使用 LINQ 查询 ArrayList。

在上一个示例中，num 被推断为 int 类型。 由于范围变量是强类型，因此可以对其调用方法或者在其他操作中使用它。 例如，可以不编写 select num，而编写 select num.ToString() 使查询表达式返回一个字符串序列而不是整数序列。 或者，也可以编写 select n + 10 使表达式返回序列 14、11、13、12、10。 有关更多信息，请参见 select 子句（C# 参考）。

范围变量类似于 foreach 语句中的迭代变量，只是两者之间有一个非常重要的区别：范围变量从不实际存储来自数据源的数据。 范围变量只是提供了语法上的便利，使查询能够描述执行查询时将发生的事情。 有关更多信息，请参见LINQ 查询简介 (C#)。

复合 from 子句

---

在某些情况下，源序列中的每个元素本身可能是序列，也可能包含序列。 例如，数据源可能是一个 IEnumerable<Student>，其中，序列中的每个 Student 对象都包含一个测验得分列表。 若要访问每个 Student 元素中的内部列表，可以使用复合 from 子句。 该技术类似于使用嵌套的 foreach 语句。 可以向任一 from 子句中添加 where 或 orderby 子句来筛选结果。 下面的示例演示了一个 Student 对象序列，其中每个对象都包含一个表示测验得分的内部整数List。 为了访问该内部列表，此示例使用了复合 from 子句。 如有必要，可在两个 from 子句之间再插入子句。

C#

class CompoundFrom
{// The element type of the data source.public class Student{public string LastName { get; set; }public List<int> Scores {get; set;}}static void Main(){// Use a collection initializer to create the data source. Note that // each element in the list contains an inner sequence of scores.List<Student> students = new List<Student>{new Student {LastName="Omelchenko", Scores= new List<int> {97, 72, 81, 60}},new Student {LastName="O'Donnell", Scores= new List<int> {75, 84, 91, 39}},new Student {LastName="Mortensen", Scores= new List<int> {88, 94, 65, 85}},new Student {LastName="Garcia", Scores= new List<int> {97, 89, 85, 82}},new Student {LastName="Beebe", Scores= new List<int> {35, 72, 91, 70}} };        // Use a compound from to access the inner sequence within each element.// Note the similarity to a nested foreach statement.var scoreQuery = from student in studentsfrom score in student.Scoreswhere score > 90select new { Last = student.LastName, score };// Execute the queries.Console.WriteLine("scoreQuery:");// Rest the mouse pointer on scoreQuery in the following line to // see its type. The type is IEnumerable<'a>, where 'a is an // anonymous type defined as new {string Last, int score}. That is,// each instance of this anonymous type has two members, a string // (Last) and an int (score).foreach (var student in scoreQuery){Console.WriteLine("{0} Score: {1}", student.Last, student.score);}// Keep the console window open in debug mode.Console.WriteLine("Press any key to exit.");Console.ReadKey();}       
}
/*
scoreQuery:
Omelchenko Score: 97
O'Donnell Score: 91
Mortensen Score: 94
Garcia Score: 97
Beebe Score: 91
*/

使用多个 from 子句执行联接

---

复合 from 子句用于访问单个数据源中的内部集合。 不过，查询还可以包含多个可从独立数据源生成补充查询的 from 子句。 使用此技术可以执行某些类型的、无法通过使用 join 子句执行的联接操作。

下面的示例演示如何使用两个 from 子句构成两个数据源的完全交叉联接。

C#

class CompoundFrom2
{static void Main(){char[] upperCase = { 'A', 'B', 'C' };char[] lowerCase = { 'x', 'y', 'z' };// The type of joinQuery1 is IEnumerable<'a>, where 'a// indicates an anonymous type. This anonymous type has two// members, upper and lower, both of type char.var joinQuery1 =from upper in upperCasefrom lower in lowerCaseselect new { upper, lower };// The type of joinQuery2 is IEnumerable<'a>, where 'a// indicates an anonymous type. This anonymous type has two// members, upper and lower, both of type char.var joinQuery2 =from lower in lowerCasewhere lower != 'x'from upper in upperCaseselect new { lower, upper };// Execute the queries.Console.WriteLine("Cross join:");// Rest the mouse pointer on joinQuery1 to verify its type.foreach (var pair in joinQuery1){Console.WriteLine("{0} is matched to {1}", pair.upper, pair.lower);}Console.WriteLine("Filtered non-equijoin:");// Rest the mouse pointer over joinQuery2 to verify its type.foreach (var pair in joinQuery2){Console.WriteLine("{0} is matched to {1}", pair.lower, pair.upper);}// Keep the console window open in debug mode.Console.WriteLine("Press any key to exit.");Console.ReadKey();}
}
/* Output:Cross join:A is matched to xA is matched to yA is matched to zB is matched to xB is matched to yB is matched to zC is matched to xC is matched to yC is matched to zFiltered non-equijoin:y is matched to Ay is matched to By is matched to Cz is matched to Az is matched to Bz is matched to C*/