您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> 關於.NET

VS 2008 SP1 + .NET 3.5 SP1 (3) - LINQ to Entities 進階查詢運算子 (二)

編輯：關於.NET

查詢運算子對照表

本文以 Northwind 為例，示範 LINQ to Entities 中常用查詢運算子的方法語法、查詢語法與對應產生的 T-SQL。

Select - 投影

// 方法語法
var p1 = ctx.Products.Select(p => new { ProductName = "產品名稱：" + p.ProductName });
// 查詢語法
var p2 = from p in ctx.Products select new { ProductName = "產品名稱：" + p.ProductName };
/* SQL:
SELECT 1 AS [C1], N'產品名稱：' + [Extent1].[ProductName] AS [C2]
FROM [dbo].[Products] AS [Extent1]
*/

Where - 篩選

var p1 = ctx.Products.Where(p => p.ProductID > 3);
var p2 = from p in ctx.Products where p.ProductID > 3 select p;
/* SQL: WHERE [Extent1].[ProductID] > 3 */

OrderBy / OrderByDescending - 排序

var p1 = ctx.Products.OrderBy(p => p.UnitPrice);
var p2 = from p in ctx.Products orderby p.UnitPrice select p;
/* SQL: ORDER BY [Project1].[UnitPrice] ASC */

var p1 = ctx.Products.OrderByDescending(p => p.UnitPrice);
var p2 = from p in ctx.Products orderby p.UnitPrice descending select p;
/* SQL: ORDER BY [Project1].[UnitPrice] DESC */

ThenBy / ThenByDescending - 多重排序

var p1 = ctx.Products.OrderBy(p => p.UnitPrice).ThenBy(p => p.ProductID);
var p2 = from p in ctx.Products orderby p.UnitPrice ascending, p.ProductID ascending select p;
/* SQL: ORDER BY [Project1].[UnitPrice] ASC, [Project1].[ProductID] ASC */

var p1 = ctx.Products.OrderBy(p => p.UnitPrice).ThenByDescending(p => p.ProductID);
var p2 = from p in ctx.Products orderby p.UnitPrice ascending, p.ProductID descending select p;
/* SQL: ORDER BY [Project1].[UnitPrice] ASC, [Project1].[ProductID] DESC */

GroupBy - 分組

// 方法語法
var p1 = ctx.Products.GroupBy(p => p.Suppliers.SupplierID)
                     .Select(g => new { Group = g.Key, Member = g });
// 查詢語法
var p2 = from p in ctx.Products
         group p by p.Suppliers.SupplierID into g
         select new { Group = g.Key, Member = g };
/* SQL: 複雜的 LEFT OUTER JOIN 配合 DISTINCT 實現分組 */

Join - 內連接

var p1 = ctx.Products.Join(ctx.Categories,
    p => p.Categories.CategoryID, c => c.CategoryID,
    (p, c) => new { c.CategoryName, p.ProductName });

var p2 = from p in ctx.Products
         join c in ctx.Categories on p.Categories.CategoryID equals c.CategoryID
         select new { c.CategoryName, p.ProductName };
/* SQL: INNER JOIN ... ON ([Extent1].[CategoryID] = [Extent2].[CategoryID]) ... */

GroupJoin - 左外連接後分組

var p1 = ctx.Categories.GroupJoin(ctx.Products,
    c => c.CategoryID, p => p.Categories.CategoryID,
    (c, g) => new { c.CategoryName, ProductCount = g.Count() });

var p2 = from c in ctx.Categories
         join p in ctx.Products on c.CategoryID equals p.Categories.CategoryID into g
         select new { CategoryName = c.CategoryName, ProductCount = g.Count() };
/* SQL: 子查詢 COUNT 配合 LEFT OUTER JOIN 語意 */