LINQ to SQL使用教程
LINQ to SQL使用教程
前些时间用LINQ to SQL作了一些项目，如今打算总结一下，帮助新手快速入门，并写一些别的教程没提到的东西。html
1、LINQ to SQL和别的LINQ to XXX有什么关系？
2、延迟执行（Deferred Loading）
3、什么是LINQ to SQL？
四，看看LINQ to SQL到底干了些啥？——建立本身的工具类
5、建立一个基本查询
六，大体扫一扫
1，WHERE
2，DISTINCT
3，AVG/COUNT/SUM/MIN/MAX
4，GROUP BY
5，CASE WHEN
6，INNER JOIN和OUTER JOIN
6.1 內链接
6.2 外链接
7，ORDER BY
8，EXISTS
9，WHERE IN
10，UNION ALL/UNION
11，Intersect/Except
12，Skip-Take
13，直接执行SQL语句查询
14，INSERT
15，UPDATE
16，DELETE
17，First/FirstOrDefault/Single
18，字符串操做
7、查询条件拼接
8、自动事务处理
9、关于自增的ID字段
10、关于默认值
总结数据库
1、LINQ to SQL和别的LINQ to XXX有什么关系？
咱们能接触到的别的带有“LINQ”字眼的东西有：LINQ to Object和LINQ to Entity Framework。它们之间的关系能够说：除了使用了类似的语法，就没什么关系了。服务器
LINQ to Object使用的命名空间是：System.Linq，而LINQ to SQL使用的命名空间是System.Data.Linq。dom
这是一个简单的LINQ to Object的例子：ide
static IEnumerable<int> FindGreaterThan5(IEnumerable<int> list) { foreach (var i in list) { if (i >= 5) yield return i; } } static void Main(string[] args) { List<int> listTest = new List<int>{ 8, 2, 7, 9, 1, 5, 3, 4 }; //找出全部大于等于5的数 IEnumerable<int> result = FindGreaterThan5(listTest); foreach (var i in result) { Console.WriteLine(i); } }
其中FindGreaterThan5的代码能够用LINQ to Object改成：函数
static IEnumerable<int> FindGreaterThan5(IEnumerable<int> list) { return list.Where(i => i >= 5); }
执行的效果是彻底同样的，但要注意一点：LINQ to Object只是简化了代码，并不是提升了效率。并且，有时候把一个foreach语句写成一大坨LINQ表达式，其可读性也很差，因此究竟用仍是不用，这个就看你的须要了。工具
2、延迟执行（Deferred Loading）
像上述例子的那种使用yield return的方式返回一个可枚举类型的函数，都会被“延迟”执行，要证实这点很简单，改一下上面的代码：post
static IEnumerable<int> FindGreaterThan5(IEnumerable<int> list) { foreach (var i in list) { if (i >= 5) yield return i; else { throw new Exception("你看不到这个异常"); } } } static void Main(string[] args) { List<int> listTest = new List<int>{ 8, 2, 7, 9, 1, 5, 3, 4 }; //找出全部大于等于5的数 IEnumerable<int> result = FindGreaterThan5(listTest); }
这个程序执行没有任何问题，你看不到异常，由于FindGreaterThan5根本没有被执行，它只有在返回结果被用到的时候才会真正去执行（这样作的好处后面会提到）。若是你把foreach加上，改成：性能
static void Main(string[] args) { List<int> listTest = new List<int>{ 8, 2, 7, 9, 1, 5, 3, 4 }; //找出全部大于等于5的数 IEnumerable<int> result = FindGreaterThan5(listTest); foreach (var i in result) { Console.WriteLine(i); } }
那这个异常就会出现，这个地方是要十分当心的，假如你这么写：ui
IEnumerable<int> result; try { result = FindGreaterThan5(listTest); } catch(Exception) //你捕捉不到异常的 { return; }
那是捕捉不到异常的，由于它实际上发生的地方是接下来的foreach处。解决方法有两种，一是try foreach语句，另外一是“再包一层”，建立一个“Roll”函数：
static IEnumerable<int> FindGreaterThan5Roll(IEnumerable<int> list) { return FindGreaterThan5(list); }
而后try这个函数。
3、什么是LINQ to SQL？
貌似讲了一堆跟LINQ to SQL无关的东西，但我向你保证理解这些内容真的很重要！OK……终于到了其它通常的教程的那个开头：什么是LINQ to SQL？
跟LINQ to Object同样，LINQ to SQL可以使得你对Microsoft SQL Server的访问代码变得简洁，它是对ADO.net的封装。因此它并不是ADO.net的替代品，也不能带来执行效率上的提升（用的很差反而会更低效）。这里还须要特别说明的是：
- 只能用于Microsoft SQL Server这套DBMS，2005版及2008版我都试过，没问题。Oracle？MySQL？没门；
- LINQ to SQL已经有了更强大和复杂的替代品，LINQ to Entity Framework，简称LINQ to EF或者Entity Framework，详情请自行Google一下。
那为何还用LINQ to SQL？一来它继续长期有效（虽然微软中止更新它了），二来够用而且好用，而LINQ to EF则相对复杂。
使用LINQ to SQL其实仍然是使用ADO.net，只是LINQ to SQL帮助你生成各个查询语句，不须要你手工来写，这样有什么好处？最大的好处就是：只要你的代码编译经过，那么就能生成正确的SQL语句，而不是报运行时错误，而后让你去检查SQL语句。
固然，好处不止这个，还有如：简单，开发快捷，更灵活的where从句生成等。
四，看看LINQ to SQL到底干了些啥？——建立本身的工具类
前面说了，LINQ to SQL实际上是“聪明”地帮你生成查询语句，但你不能彻底相信它，由于它有时候是“自做聪明”，因此你要在调试的时候看看它究竟干了些什么。个人方法是将它的生成的SQL语句打印到Debug窗口中，这个小技巧帮我找到了很多的问题，OK，这里我把我写的这个小小的DataContext的帮助类贴出来：
public class DebugWriter : TextWriter { public override void WriteLine(string value) { Debug.WriteLine(value); } public override void WriteLine(string format, params object[] arg) { Debug.WriteLine(format, arg); } public override Encoding Encoding { get { return Encoding.UTF8; } } } public static class DataContextHelper { public static void InitForModification(this DataContext dc) { dc.DeferredLoadingEnabled = false; #if DEBUG dc.Log = new DebugWriter(); #endif } /// <summary> /// 若是一个表中的某一列引用到别的表，默认状况下LINQ to SQL会在遍历搜索结果的时候 /// 动态地去获取别的表的内容，这样就可能产生大量的SQL查询 /// 当把DeferredLoadingEnabled设置为false以后，LINQ to SQL则关闭这项功能，省去了大量的开销 /// 事实上，咱们更多的时候会用到LoadWith，直接生成一条联表查询 /// </summary> public static void InitForQuery(this DataContext dc) { dc.ObjectTrackingEnabled = false; dc.DeferredLoadingEnabled = false; #if DEBUG dc.Log = new DebugWriter(); #endif } public static DateTime GetDbDateTime(this DataContext dc) { try { return dc.ExecuteQuery<DateTime>("SELECT GETDATE()").Single(); } catch (Exception ex) { throw new Exception("Database error.", ex); //建议替换成你的自定义异常类型 } } }
若是你对C#的扩展方法语法不是很清楚，那么能够参考个人另外一篇博文：http://www.cnblogs.com/guogangj/archive/2012/03/02/2376927.html
这个帮助类颇有用，把它引入到你的工程的namespace去吧（在个人工程中，我把它放入一个公共类库中，由于好几个工程都要引用到，避免重复，DRY，OK？）
- InitForQuery - 用于查询（注意看看我写的那几行注释，后面会提到）
- InitForModification - 用于增删改
- GetDbDateTime - 因为LINQ to SQL不直接支持GetDate函数，因此写一个函数来获取DBMS的时间
接下来咱们仍是用Northwind数据库为例子，看看如何使用LINQ to SQL。（Northwind是一个小型数据库例子，不少代码都用它做为范例，在这里获取它的建立脚本：http://northwinddatabase.codeplex.com/)
5、建立一个基本查询
给你的工程add一个new item，叫Products.dbml，而后打开之，再如图把Products和Categories两张表拽进去。
而后：
static void Main(string[] args) { ProductsDataContext db = new ProductsDataContext(); db.Log = new DebugWriter(); var products = from p in db.Products select p; foreach (var product in products) { Console.WriteLine(product.ProductName+ " (" + product.Category.CategoryName + ")"); } }
这里也许你有个问题，为何from写在前面，而不是select，select写在前面不是更符合SQL的习惯么？想一想看，若是你能本身在5分钟内想出来讲明你比我聪明（^_^），OK，其实也跟技术自己关系不大，这样作的缘由彻底是为了咱们的开发环境的智能提示（intellisense），知道from什么了，后面也就有智能提示了，不然select什么？不知道。
F5，调试运行，看吧，这么一点点代码，就能把全部商品名称及其类型都给打印出来了，是否是很方便？——且慢！打开你的Debug窗口看看：
个人天啊，一个查询就能完成的事情，为何生成了这么多的SQL语句？这就是我前面所提到的DeferredLoadingEnabled这个选项，看我写的那个帮助类的InitForQuery方法的注释。避免这种状况的方法是关闭这个选项，并显式告诉LINQ to SQL，你须要哪些关联的加载内容，让LINQ to SQL自动给你生成一个联表查询。咱们来改进下：
static void Main(string[] args) { ProductsDataContext db = new ProductsDataContext(); db.InitForQuery(); DataLoadOptions ds = new DataLoadOptions(); ds.LoadWith<Product>(p => p.Category); db.LoadOptions = ds; var products = from p in db.Products select p; foreach (var product in products) { Console.WriteLine(product.ProductName+ " (" + product.Category.CategoryName + ")"); } }
注意看，此次用了我前面提供的扩展方法InitForQuery，还有一个DataloadOptions选项，告诉LINQ to SQL须要哪些额外信息，这里咱们须要Category信息。好，看看Debug窗口，此次只有一个SQL语句：
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t2].[test], [t2].[CategoryID] AS [CategoryID2], [t2].[CategoryName], [t2].[Description], [t2].[Picture] FROM [dbo].[Products] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[CategoryID], [t1].[CategoryName], [t1].[Description], [t1].[Picture] FROM [dbo].[Categories] AS [t1] ) AS [t2] ON [t2].[CategoryID] = [t0].[CategoryID]
LINQ to SQL聪明地为咱们生成了一个left outer join，这正是咱们想要的。
这个例子说明了：
- 咱们在调试的时候得关注下LINQ to SQL到底干了些什么
- 使用LoadWith，而不要使用自动延迟加载
另外，若是你不须要Product的全部列的话，你能够这样写：
var products = from p in db.Products select new { ProductId = p.CategoryID, ProductName = p.ProductName, UnitPrice = p.UnitPrice };
这样生成的SQL语句是不太同样的，本身观察下，但这样生成出来的对象，是不能用于Insert和Update的，这个要注意一下。
六，大体扫一扫
写一篇无微不至的教程并不是本文目的，这个已经有了很多好的教程，因此接下来就稍微简略一些，但愿能对你们起到必定的抛砖引玉的做用。
1，WHERE
把1994年后雇佣的，或者Title中包含“经理”的员工选出来
var result = from e in db.Employees where e.HireDate >= new DateTime(1994, 1, 1) || e.Title.Contains("Manager") select e;
2，DISTINCT
获取有交易的客户ID（重复的去掉）
var result = (from o in db.Orders select new { CustomerID = o.CustomerID }).Distinct();
3，AVG/COUNT/SUM/MIN/MAX
取得产品价格的平均值
var result = db.Products.Select(p => p.UnitPrice).Average(); var result = db.Products.Average(p => p.UnitPrice); var result = (from p in db.Products select p.UnitPrice).Average();
从这也能看出写法并非惟一的。其它集合操做函数也是相似的。
4，GROUP BY
选出每一类的最贵产品、最便宜产品及产品均价
var result = from p in db.Products group p by p.CategoryID into g select new { g.Key, Amount = g.Count(), MaxPrice = g.Max(item => item.UnitPrice), MinPrice = g.Min(item=>item.UnitPrice), AveragePrice = g.Average(item=>item.UnitPrice) };
5，CASE WHEN
var q = from c in db.Customers select new { Name = c.ContactName, Address = new { City = c.City, Region = c.Region == null ? "Unknown" : c.Region } };
翻译为：
SELECT [t0].[ContactName] AS [Name], [t0].[City], (CASE WHEN [t0].[Region] IS NULL THEN CONVERT(NVarChar(15),@p0) ELSE [t0].[Region] END) AS [Region] FROM [dbo].[Customers] AS [t0]
6，INNER JOIN和OUTER JOIN
想用LINQ to SQL直接写联表查询是很麻烦的，不过这也是惟一一个相比直接用SQL来得更麻烦的地方。
6.1 內链接
var result = from d in db.Order_Details join o in db.Orders on d.OrderID equals o.OrderID select new {d.OrderID, d.ProductID, d.UnitPrice, o.RequiredDate};
翻译为
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t1].[RequiredDate] FROM [dbo].[Order Details] AS [t0] INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[OrderID] = [t1].[OrderID]
另一种更紧凑的写法
var result = from d in db.Order_Details from o in db.Orders.Where(item=>item.OrderID==d.OrderID) select new { d.OrderID, d.ProductID, d.UnitPrice, o.RequiredDate };
翻译为
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t1].[RequiredDate] FROM [dbo].[Order Details] AS [t0], [dbo].[Orders] AS [t1] WHERE [t1].[OrderID] = [t0].[OrderID]
执行效果同样的
6.2 外链接
var result = from d in db.Order_Details join o in db.Orders on d.OrderID equals o.OrderID into temp from p in temp.DefaultIfEmpty() select new {d.OrderID, d.ProductID, d.UnitPrice, p.RequiredDate};
另外一种更紧凑的写法是：
var result = from d in db.Order_Details from o in db.Orders.Where(item => item.OrderID == d.OrderID).DefaultIfEmpty() select new {d.OrderID, d.ProductID, d.UnitPrice, o.RequiredDate };
翻译为
SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t1].[RequiredDate] AS [RequiredDate] FROM [dbo].[Order Details] AS [t0] LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t1].[OrderID] = [t0].[OrderID]
7，ORDER BY
正序的状况
var result = from p in db.Products orderby p.UnitPrice select p;
逆序的状况
var result = from p in db.Products orderby p.UnitPrice descending select p;
也能够选择多列排序
var result = from p in db.Products orderby p.UnitPrice, p.UnitsInStock select p;
上面的句子也能够这么写，同样的
var result = from p in db.Products.OrderBy(item => item.UnitPrice).ThenBy(item => item.UnitsInStock) select p;
8，EXISTS
选出没有订单的客户
var result = from c in db.Customers where !c.Orders.Any() select c;
其实Any还能够带条件参数哦。
9，WHERE IN
查看指定的几个客户的订单
var result = ( from o in db.Orders where ( new string[] { "AROUT", "BOLID", "FISSA" }) .Contains(o.CustomerID);
10，UNION ALL/UNION
Concat不会去除重复项目，至关于SQL的Union All；而Union会去除重复项，至关于SQL的Union
看看有来自哪些国家的客户和雇员
var q = ( from c in db.Customers select c.Country ).Union( from e in db.Employees select e.Country );
11，Intersect/Except (是用复合查询加EXIST实现的）
var q = ( from c in db.Customers select c.Country ).Intersect( from e in db.Employees select e.Country );
查询顾客和职员同在的国家，Interset实际上是用一个复合查询实现的。
var q = ( from c in db.Customers select c.Country ).Except( from e in db.Employees select e.Country );
去除顾客和职员同在的国家。
12，Skip-Take（是用ROW_NUMBER()函数实现的）
获取从第21个产品开始的10个产品。
var q = ( from p in db.Products select p ).Skip(20).Take(10);
13，直接执行SQL语句查询
若是发现查询语句很难写，（或者写出来LINQ会傻乎乎地生成低效的屡次执行）能够考虑直接使用SQL语句，但缺点就是失去了编译器检查的功能，而且得本身构建好一个类，用于存放数据（若是此类尚未的话）。
IEnumerable<Employee> emps = db.ExecuteQuery<Employee>("select * from Employees");
PS:直接写“*”可不太好
14，INSERT
注意：若是指定的ID存在，则会自动执行Update，而不是Insert（LINQ to SQL是否是很“智能”？都有些自做聪明了）
Region nwRegion = new Region() { RegionID = 32, RegionDescription = "Rainy" }; db.Regions.InsertOnSubmit(nwRegion); db.SubmitChanges();
15，UPDATE
真奇怪，update和insert竟然不一样，没有一个专门的Update方法，而是直接取出数据库的条目，而后修改其属性，在SubmitChanges，固然，条目的类型必须是dbml自动帮咱们生成的类型，不能是自定义的。这是简单的Update的例子：
Customer cust = db.Customers.First(c => c.CustomerID == "ALFKI"); cust.ContactTitle = "Vice President"; db.SubmitChanges();
这是Update多条的例子：
var q = from p in db.Products where p.CategoryID == 1 select p; foreach (var p in q) { p.UnitPrice += 1.00M; } db.SubmitChanges();
16，DELETE
先选后删
OrderDetail orderDetail = db.OrderDetails.First (c => c.OrderID == 10255 && c.ProductID == 36); db.OrderDetails.DeleteOnSubmit(orderDetail); db.SubmitChanges();
17，First/FirstOrDefault/Single（是用TOP实现的）
都是取出一条记录，区别是：
- First – 至少有一条，不然抛异常
- FirstOrDefault – 若是一条都没有，则返回默认值（对象的话默认值就是null）
- Single – 有且只有一条，不然抛异常
选出ID为1的雇员：
Employee theveryemp = db.Employees.Single(item => item.EmployeeID == 1);
这个语句会当即执行，若是获取不到或者不止一条，则抛出异常。
18，字符串操做
如下这些LINQ中对字符串操做的部分都会被聪明地转变为相关的SQL语句，而不是使用C#代码来操做。具体会被转换成什么，你们动手试试看。
Location = c.City + ", " + c.Country p.ProductName.Length < 10 c.ContactName.Contains("Anders") c.ContactName.IndexOf(" ") c.ContactName.StartsWith("Maria") c.ContactName.EndsWith("Anders") p.ProductName.Substring(3); e.HomePhone.Substring(6, 3) == "555" e.LastName.ToUpper() c.CategoryName.ToLower() e.HomePhone.Substring(0, 5).Trim() e.HomePhone.Insert(5, ":") e.HomePhone.Remove(9) e.HomePhone.Remove(0, 6) s.Country.Replace("UK", "United Kingdom")
7、查询条件拼接
客户端在查询的时候每每会附带一些查询条件，例如商品名称模糊查询，日期范围，商品类型范围，固然还有分页等等。咱们一般把这些查询条件封装到一个对象中去，而后让服务器来解释这个对象并拼接SQL查询语句。拼接SQL语句是极其容易出错的事情，并且检查起来还比较费劲，由于SQL语句写起来并不像C#代码那样能够自动格式化。现在使用LINQ to SQL这些问题就不存在了。
var
q =
from
p
in
db.Products
select
p;
if
(条件A)
{
q.Where(p=>p.XXX==A);
}
if
(条件B)
{
q.Where(p=>p.YYY!=B);
}
if
(条件C)
{
q.Where(p=>p.ZZZ.Contains(C));
}
//……
|
回头想一想本文初所提到的延迟执行，到这里你应该知道为何须要延迟执行了吧，就是为了方便你拼接这些LINQ表达式，若是每一个select或者where都执行一次，那但是会带来严重的性能问题的。
8、自动事务处理
也许你发现了，对于增删改，都是在SubmitChanges的时候执行，并且一次SubmitChanges，能改多个表多条记录，那事务在哪里？其实LINQ to SQL已经自动帮咱们封装好事务了，在执行的过程当中，一旦有一步失败，操做将会回滚，这个彻底不须要咱们担忧。
9、关于自增的ID字段
按设计惯例，每张表都应该有一个自增的ID字段，对于这个字段，其值老是由数据库自动生成的，因此咱们在Insert一行的时候，历来不须要指定其ID。例如，咱们查看ProductID的属性列表，有个叫Auto Generated Value的属性，其值为True，即表明这个字段的值是DBMS自动生成的，你不须要指定。
那么咱们插入了一条记录以后，咱们想取回这个自动生成的ID的值，怎么办呢？按之前的作法是：
select scope_identity()
如今的作法是在SubmitChanges()以后直接经过插入的对象带出这个自动生成的ID的值：
Category newCategory = new Category {CategoryName = "Fruit", Description = "Fruits..."}; db.Categories.InsertOnSubmit(newCategory); db.SubmitChanges(); Console.WriteLine(newCategory.CategoryID);
那如今有这么种状况，我要添加一个订单，同时给这个订单添加若干条明细，怎么办？这个看起来有点难，难在哪里？你要添加明细，你就必须知道主档的ID，但在SubmitChanges以前，你是拿不到主档的ID的；若是你在Insert了主档以后就Submit，那一旦在Insert明细的时候失败，你就没法回滚了。
OK，其实这么想的话仍是按照旧的思路，LINQ to SQL是一套ORM，咱们应该直接指定其对象的关系，而不是去关心ID的值是多少，这是正确的作法：
Order newOrder = new Order { CustomerID = "ALFKI", ShipAddress = "Shanghai ..." }; Order_Detail newDetail1 = new Order_Detail { Discount = 1.0f, ProductID = 1, Quantity = 1, UnitPrice = 9.0M }; Order_Detail newDetail2 = new Order_Detail { Discount = 0.9f, ProductID = 2, Quantity = 2, UnitPrice = 5.0M }; newOrder.Order_Details.Add(newDetail1); newOrder.Order_Details.Add(newDetail2); db.Orders.InsertOnSubmit(newOrder); db.SubmitChanges();
10、关于默认值
数据库的表中的不少字段都带有默认值，前面提到的自增ID就是一个例子，但大多字段跟ID不一样的是：ID是强制的而且必定是DBMS自动分配的，而这些带默认值的字段则不必定强制，而且容许由用户传入值。若是把这些带默认值的字段跟自增ID同样，设置其“Auto Generated Value”属性为True的话，咱们就没办法设置它的值了，它的值老是由DBMS自动分配，而事实上，咱们想要的结果是：当我没有给值的时候使用默认值，当我有给值的时候，使用个人值。很不幸，LINQ to SQL作不到，我尝试过不少种方法，结果很明确，就是作不到！这也许算是LINQ to SQL的一个缺陷吧。
因此，使用LINQ to SQL的话就把DBMS的默认值忽略掉吧，每次都手工把值传进去好了……
总结
感谢你看完本文，本文确定不是最全面的对LINQ to SQL的技术文章，但真心是本人实战的总结，若是上面提到的内容你都理解，那LINQ to SQL你也就掌握差很少了……呃，我是说即使你再遇到什么问题，你也确定有解决的思路了。