Linq to SQL语法及实例大全

### Linq to SQL语法及实例大全 #### 一、LINQ to SQL简介 **LINQ to SQL** 是 Microsoft .NET Framework 的一部分，它提供了一种简单的方法来将数据库中的数据映射到内存中的对象，并提供了丰富的查询功能。通过 LINQ to SQL，开发人员可以使用 C# 或 Visual Basic 编写 LINQ 查询来处理数据库数据，这极大地提高了开发效率并减少了代码编写量。 #### 二、LINQ to SQL基础语法 ##### 1. Where 操作 - **简单形式**： ```csharp var query = from p in db.Products where p.UnitPrice > 10 select p; ``` - **关系条件形式**： ```csharp var query = from p in db.Products where p.SupplierID == 1 && p.CategoryID == 2 select p; ``` - **First() 形式**： ```csharp var product = (from p in db.Products where p.ProductName == "Chai" select p).First(); ``` ##### 2. Select/Distinct - **简单用法**： ```csharp var names = from p in db.Products select p.ProductName; ``` - **匿名类型形式**： ```csharp var products = from p in db.Products select new { Name = p.ProductName, Price = p.UnitPrice }; ``` - **条件形式**： ```csharp var products = from p in db.Products where p.UnitPrice > 10 select new { p.ProductName, Price = p.UnitPrice * 2 }; ``` - **指定类型形式**： ```csharp var products = from p in db.Products select new ProductInfo { Name = p.ProductName, Price = p.UnitPrice }; ``` - **筛选形式**： ```csharp var query = from p in db.Products where p.UnitPrice > 10 select new { p.ProductName, p.UnitPrice }; var results = query.Where(q => q.Price < 20); ``` - **shaped形式(整形类型)**： ```csharp var ids = from p in db.Products select p.ProductID; ``` - **嵌套类型形式**： ```csharp var query = from p in db.Products join c in db.Categories on p.CategoryID equals c.CategoryID select new { p.ProductName, Category = c.CategoryName }; ``` - **本地方法调用形式(LocalMethodCall)**： ```csharp int CalculatePrice(int price) { return price * 2; } var prices = from p in db.Products select CalculatePrice(p.UnitPrice); ``` - **Distinct形式**： ```csharp var distinctNames = (from p in db.Products select p.ProductName).Distinct(); ``` ##### 3. Count/Sum/Min/Max/Avg - **简单形式**： ```csharp int count = db.Products.Count(); double sum = db.Products.Sum(p => p.UnitPrice); int min = db.Products.Min(p => p.ProductID); int max = db.Products.Max(p => p.ProductID); double avg = db.Products.Average(p => p.UnitPrice); ``` - **带条件形式**： ```csharp int count = db.Products.Count(p => p.UnitPrice > 10); double sum = db.Products.Where(p => p.UnitPrice > 10).Sum(p => p.UnitPrice); int min = db.Products.Where(p => p.UnitPrice > 10).Min(p => p.ProductID); int max = db.Products.Where(p => p.UnitPrice > 10).Max(p => p.ProductID); double avg = db.Products.Where(p => p.UnitPrice > 10).Average(p => p.UnitPrice); ``` ##### 4. Join - **一对多关系(1toMany)**： ```csharp var query = from p in db.Products join s in db.Suppliers on p.SupplierID equals s.SupplierID select new { Product = p, Supplier = s }; ``` - **多对多关系(ManytoMany)**： ```csharp var query = from p in db.Products join c in db.Categories on p.CategoryID equals c.CategoryID select new { Product = p, Category = c }; ``` - **自联接关系**： ```csharp var query = from p1 in db.Products join p2 in db.Products on p1.ProductID equals p2.ProductID select new { FirstProduct = p1, SecondProduct = p2 }; ``` - **左外部联接(LeftOuterJoin)**： ```csharp var query = from p in db.Products join s in db.Suppliers on p.SupplierID equals s.SupplierID into g from sub in g.DefaultIfEmpty() select new { Product = p, Supplier = sub }; ``` - **投影的Let赋值(Projected let assignment)**： ```csharp var query = from p in db.Products let price = p.UnitPrice where price > 10 select new { p.ProductName, price }; ``` - **组合键(Composite Key)**： ```csharp var query = from p1 in db.Products join p2 in db.Products on new { p1.ProductID, p1.CategoryID } equals new { p2.ProductID, p2.CategoryID } select new { FirstProduct = p1, SecondProduct = p2 }; ``` - **可为null/不可为null的键关系(Nullable/Nonnullable Key Relationship)**： ```csharp var query = from p1 in db.Products join p2 in db.Products on p1.ProductID == p2.ProductID into g from sub in g.DefaultIfEmpty() where sub != null select new { FirstProduct = p1, SecondProduct = sub }; ``` ##### 5. OrderBy - **简单形式**： ```csharp var query = from p in db.Products orderby p.ProductName select p; ``` - **带条件形式**： ```csharp var query = from p in db.Products orderby p.UnitPrice descending where p.UnitPrice > 10 select p; ``` - **降序排序**： ```csharp var query = from p in db.Products orderby p.UnitPrice descending select p; ``` - **ThenBy**： ```csharp var query = from p in db.Products orderby p.CategoryID, p.ProductName select p; ``` - **ThenByDescending**： ```csharp var query = from p in db.Products orderby p.CategoryID, p.ProductName descending select p; ``` - **带GroupBy形式**： ```csharp var query = from p in db.Products group p by p.CategoryID into g orderby g.Key select g; ``` ##### 6. GroupBy/Having - **简单形式**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, Count = g.Count() }; ``` - **Select匿名类**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, Products = g }; ``` - **最大值**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, MaxPrice = g.Max(p => p.UnitPrice) }; ``` - **最小值**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, MinPrice = g.Min(p => p.UnitPrice) }; ``` - **平均值**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, AvgPrice = g.Average(p => p.UnitPrice) }; ``` - **求和**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, SumPrice = g.Sum(p => p.UnitPrice) }; ``` - **计数**： ```csharp var query = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, Count = g.Count() }; ``` - **带条件计数**： ```csharp var query = from p in db.Products group p by p.CategoryID into g where g.Sum(p => p.UnitPrice) > 100 select new { CategoryID = g.Key, Count = g.Count() }; ``` - **Where限制**： ```csharp var query = from p in db.Products group p by p.CategoryID into g where g.Count() > 1 select new { CategoryID = g.Key, Count = g.Count() }; ``` - **多列(Multiple Columns)**： ```csharp var query = from p in db.Products group p by new { p.CategoryID, p.SupplierID } into g select new { CategoryID = g.Key.CategoryID, SupplierID = g.Key.SupplierID, Count = g.Count() }; ``` - **表达式(Expression)**： ```csharp var query = from p in db.Products group p by p.CategoryID into g where g.Count() > 1 select new { CategoryID = g.Key, Count = g.Count() }; ``` ##### 7. Exists/In/Any/All/Contains - **Exists**： ```csharp bool exists = (from p in db.Products where p.UnitPrice > 10 select p).Any(); ``` - **In**： ```csharp var products = from p in db.Products where new[] { 1, 2, 3 }.Contains(p.ProductID) select p; ``` - **Any**： ```csharp bool any = db.Products.Any(p => p.UnitPrice > 10); ``` - **All**： ```csharp bool all = db.Products.All(p => p.UnitPrice > 0); ``` - **Contains**： ```csharp bool contains = db.Products.Any(p => p.ProductName == "Chai"); ``` ##### 8. Concat/Union/Intersect/Except - **Concat（连接）**： ```csharp var query1 = from p in db.Products where p.UnitPrice > 10 select p; var query2 = from p in db.Products where p.UnitPrice < 5 select p; var combined = query1.Concat(query2); ``` - **Union（合并）**： ```csharp var query1 = from p in db.Products where p.UnitPrice > 10 select p; var query2 = from p in db.Products where p.UnitPrice < 5 select p; var combined = query1.Union(query2); ``` - **Intersect（相交）**： ```csharp var query1 = from p in db.Products where p.UnitPrice > 10 select p; var query2 = from p in db.Products where p.UnitPrice < 20 select p; var intersect = query1.Intersect(query2); ``` - **Except（与非）**： ```csharp var query1 = from p in db.Products where p.UnitPrice > 10 select p; var query2 = from p in db.Products where p.UnitPrice < 20 select p; var except = query1.Except(query2); ``` ##### 9. Top/Bottom和Paging和SqlMethods - **Top**： ```csharp var topProducts = db.Products.Take(10); ``` - **Skip**： ```csharp var skipProducts = db.Products.Skip(10); ``` - **TakeWhile**： ```csharp var takeWhileProducts = db.Products.TakeWhile(p => p.UnitPrice < 10); ``` - **SkipWhile**： ```csharp var skipWhileProducts = db.Products.SkipWhile(p => p.UnitPrice < 10); ``` - **Paging（分页）操作**： ```csharp var page = 1; var pageSize = 10; var products = db.Products.Skip((page - 1) * pageSize).Take(pageSize); ``` - **SqlMethods操作**： ```csharp var likeProducts = db.Products.Where(p => SqlMethods.Like(p.ProductName, "%Chai%")); ``` ##### 10. Insert - **简单形式**： ```csharp Product newProduct = new Product { ProductName = "New Product", UnitPrice = 10 }; db.Products.InsertOnSubmit(newProduct); db.SubmitChanges(); ``` - **一对多关系**： ```csharp Product newProduct = new Product { ProductName = "New Product", UnitPrice = 10 }; Supplier supplier = db.Suppliers.Single(s => s.SupplierID == 1); newProduct.Supplier = supplier; db.Products.InsertOnSubmit(newProduct); db.SubmitChanges(); ``` - **多对多关系**： ```csharp Product newProduct = new Product { ProductName = "New Product", UnitPrice = 10 }; Category category = db.Categories.Single(c => c.CategoryID == 1); newProduct.Category = category; db.Products.InsertOnSubmit(newProduct); db.SubmitChanges(); ``` - **使用动态CUD重写(Override using Dynamic CUD)**： ```csharp Product newProduct = new Product { ProductName = "New Product", UnitPrice = 10 }; db.Products.InsertOnSubmit(newProduct, new DynamicProperty[] { "ProductName", "UnitPrice" }); db.SubmitChanges(); ``` ##### 11. Update - **简单形式**： ```csharp var product = db.Products.Single(p => p.ProductID == 1); product.ProductName = "Updated Product"; db.SubmitChanges(); ``` - **多项更改**： ```csharp var product = db.Products.Single(p => p.ProductID == 1); product.ProductName = "Updated Product"; product.UnitPrice = 15; db.SubmitChanges(); ``` ##### 12. Delete和使用Attach - **简单形式**： ```csharp var product = db.Products.Single(p => p.ProductID == 1); db.Products.DeleteOnSubmit(product); db.SubmitChanges(); ``` - **一对多关系**： ```csharp var product = db.Products.Single(p => p.ProductID == 1); db.Products.DeleteOnSubmit(product); db.SubmitChanges(); ``` - **推理删除(Inferred Delete)**： ```csharp var product = db.Products.Single(p => p.ProductID == 1); db.Products.DeleteOnSubmit(product); db.SubmitChanges(); ``` - **使用Attach更新(Update with Attach)**： ```csharp Product updatedProduct = new Product { ProductID = 1, ProductName = "Updated Product" }; db.Products.Attach(updatedProduct); db.SubmitChanges(); ``` 总结来说，**LINQ to SQL** 提供了强大的查询语言和数据访问能力，使得开发人员能够以更简洁的方式处理数据库数据。上述语法涵盖了从简单的查询到复杂的关联查询、聚合函数等高级特性，能够满足大多数数据库操作的需求。