LINQ to SQL 示例

• 下载 Northwind 数据库脚本 - 238.89 KB

引言

LINQ 是 .NET Framework 3.5 (Visual Studio 2008) 中最重要的特性之一。它是将数据库表映射到类的新方法，正如我们所知，我们称之为 对象关系映射 (O/R Mapping)。一篇关于如何快速编写 LINQ 代码的文章对于初学者来说总是受欢迎的，我认为阅读示例是学习新技术的最佳方式。

这些是在我学习和使用 LINQ 时创建的示例，现在我想分享它们。希望它们对您有所帮助。我将使用 Northwind 数据库作为示例，您可以从本文顶部的链接下载它。

如果您想了解更多信息，我建议您阅读 101 个 LINQ 示例。

示例

// Basic
    // Select * From Products 
    var query1 = from p in db.Products
                 select p;
    // Select ProductID, ProductName, UnitPrice From Products
    var query2 = from p in db.Products
                 select new { 
                     p.ProductID,
                     p.ProductName,
                     p.UnitPrice
                 };

注意：query2 将创建一个新类，其中包含三个属性，分别映射 ProductId、ProductName 和 UnitPrice。

// Where
    // Select * From Products Where ProductID = 1
    var query3 = from p in db.Products
                 where p.ProductID == 1
                 select p;

    // Select * From Products Where SupplierId =5 and UnitPrice > 20
    var query4 = from p in db.Products
                 where p.SupplierID == 5 && p.UnitPrice > 20
                 select p;


    // Select * From Products Where SupplierId =5 Or SupplierId=6 
    var query5 = from p in db.Products
                 where p.SupplierID == 5 || p.SupplierID == 6
                 select p;

注意：where 块中的条件是一个逻辑表达式，就像在 if() 中一样，会返回一个布尔值。

// Order By 
    // Select * From Products Order By ProductId
    var query6 = from p in db.Products
                 orderby p.ProductID
                 select p;

    // Select * From Products Order By ProductId Desc
    var query7 = from p in db.Products
                 orderby p.ProductID descending
                 select p;

    // Select * From Products Order By CategoryId, UnitPrice Desc
    var query8 = from p in db.Products
                 orderby p.CategoryID, p.UnitPrice descending
                 select p;

注意：默认排序是 升序，order by p.ProductID 与 order by p.ProductID ascending 相同，就像在 T-SQL 中一样。

// Top 
    // Select Top 10 * From Products
    var query9 = (from p in db.Products
                 select p).Take(10);

    // Select Top 1 * From Products
    var query10 = (from p in db.Products
                   select p).Take(1);
    // or
    var query11 = (from p in db.Products
                   select p).First();

注意：如果它只返回一条记录，我建议使用 First 而不是 Take(1)。

// Top with Order By
    // Select Top 10 * From Products Order By ProductId
    var query12 = (from p in db.Products
                   orderby p.ProductID
                   select p).Take(10);

// Distinct
    // Select Distinct CategoryId From Products
    var query13 = (from p in db.Products
                   select p.CategoryID).Distinct();

// Group By
    // Select CategoryId, Count(CategoryID) As NewField 
    // From Products Group By CategoryId
    var query14 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key, 
                      NewField = g.Count() 
                  };

    // Select CategoryId, Avg(UnitPrice) As NewField From Products Group By CategoryId
    var query15 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key, 
                      NewField = g.Average(k => k.UnitPrice) 
                  };

    // Select CategoryId, Sum(UnitPrice) As NewField From Products Group By CategoryId
    var query16 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key,
                      NewField = g.Sum(k => k.UnitPrice )
                  };

// Union
    // Select * From Products Where CategoryId =1 union Select * 
    // From Products Where CategoryId = 2
    var query17 = (from p in db.Products
                   where p.CategoryID == 1
                   select p).Union(
                       from m in db.Products
                       where m.CategoryID == 2
                       select m
                   );

// Two tables
    // Select A.ProductId, A.ProductName, B.CategoryId, B.CategoryName 
    // From Products A, Categories B 
    //    Where A.CategoryID = B.CategoryID and A.SupplierId =1 
    var query18 = from p in db.Products
                  from m in db.Categories
                  where p.CategoryID == m.CategoryID && p.SupplierID == 1
                  select new {
                      p.ProductID,
                      p.ProductName,
                      m.CategoryID,
                      m.CategoryName
                  };

历史

• 版本 1.0 - 2008-03-18：创建文章