Talk O/RM (DAL) too ...

如何把数据库中的关系转换为对象，如何把对对象的操作进行持久化保存？

 

不仅仅为了节省大量工作量，而且更重要的是，the above layer should just know Business Object, not Data Table in database.

下面是一些实践.

0. 纯手工劳动. 用上SqlHelper\Enterprise Library DAAB, 自己建类以及数据库操作方法代码.  还有CodeSmith代码生成器.

1. 起初，我们用强类型的DataSet. We can use Strongly Typed TableAdapters and DataTables in VS.Net 2005
Scott said: It is pretty useful and flexible.  I really like the fact that it enables me to avoid having to write tedious data access code.

2. Microsoft LINQ to SQL -- .Net 下的ORM解决方案

转载一篇很好的文章.

使用LINQ查询与性能分析
2009-03-12 00:03

Orcas（VS2008&Framework3.5）给我们带来了很多令人兴奋的新特性，尤其是LINQ的引进，可以说方便了一大批开发人员和框架设计人员。过去，当我们使用O/RMapping的一些框架时，最担心的应该是复杂的查询和数据库操作的性能问题，那么LINQ在这个方面给我们带来了什么呢？

一：数据查询：

一个项目中，对数据库操作最多的应该就是查询，特别是复杂一些的项目，查询往往是从很多张表里取一些数据，“东拼西凑”的呈现给用户。如果不使用O/R Mapping呢，我们处理的方式无非是两种：复杂的SQL语句或者是存储过程。但是，在使用了O/R Mapping的情况下，为了保证取出的数据是强类型，不得不用多个方法来进行拼凑，麻烦且不说，性能损失是肯定的，那么也就出现了O/R Mapping框架和未封装的ADO.NET一起用的尴尬场面。那么LINQ怎么处理的呢？首先我们先来看一些复杂的查询语法。

设计两张表，分别为表示部门（Department）和员工（Employee）：

1、 模糊查询：

模糊查询在O/RMapping的框架中是比较难实现的，即使实现，效率也不是很高，LINQ提供了我们丰富的模糊查询的方法，比如我们要在Employee表中查姓张的人，可以这样：

DBDataClassesDataContext dbdata = new DBDataClassesDataContext();

        var query = from employee in dbdata.Employees where employee.EmployeeName.StartsWith("张") select employee;

在实际执行的时候，这句话被转换成了：

SELECT [t0].[EmployeeId], [t0].[DepId], [t0].[EmployeeName], [t0].[EmployeeSalary] FROM [dbo].[Employee] AS [t0] WHERE [t0].[EmployeeName] LIKE @p0

这样的SQL语句，这个里面的@p0这个参数在执行的时候加上了“%”，也就是以@p0开头的任何符合条件的记录都被取出来了。这样的方法在LINQ中还有Contains、EndsWith等。

2、嵌套查询：

如果我们需要取出市场部的所有员工，在以往的O/R Mapping框架中，往往需要取两次（我们不考虑存在映射关系的情况），一次是取出市场部的编号，再使用编号来取出所有员工。LINQ给我们提供了更好的解决办法，我们可以这样操作：

var query = from employee in dbdata.Employees where employee.DepId ==

                    (from department in dbdata.Departments where department.DepName == "市场部"

                     select department ).Single().DepId

            select employee;

这里我们使用了嵌套查询来完成，这句话在执行的时候，被转换成了SQL的嵌套查询：

SELECT [t0].[EmployeeId], [t0].[DepId], [t0].[EmployeeName], [t0].[EmployeeSalary] FROM [dbo].[Employee] AS [t0] WHERE [t0].[DepId] = (SELECT [t1].[DepId] FROM [dbo].[Department] AS [t1] WHERE [t1].[DepName] = @p0)

当然，在这里，我们不必那么麻烦，只需要使用已经存在的实体关系，就可以轻松的完成：

var query = from employee in dbdata.Employees where employee.Department.DepName == "市场部"

            select employee;

不过，这和刚才的查询却有本质的差别，我们来看看这里，LINQ转换成什么样的SQL语句了：

SELECT [t0].[EmployeeId], [t0].[DepId], [t0].[EmployeeName], [t0].[EmployeeSalary] FROM [dbo].[Employee] AS [t0] LEFT OUTER JOIN [dbo].[Department] AS [t1] ON [t1].[DepId] = [t0].[DepId] WHERE [t1].[DepName] = @p0

        这里，转换出来的并不是嵌套的SQL语句，而被转换成了左连接查询，可以看出，嵌套查询是我们在特殊的情况下，可以使用的一个利器。

3、投影

如果说刚才的查询，还只是很简单的查询，那么结合匿名类来实现投影查询，则是LINQ提供的一个利器，这种方法非常灵活，同时也满足了我们绝大部分的查询需求。下面我们来看一个例子：我们需要查询出部门工资的总和，怎么做呢？

var query = from department in dbdata.Departments

                select new{ depid = department.DepId, depname=department.DepName, depsalary = department.Employees.Sum(e => e.EmployeeSalary) };

这句查询语句中，在new关键字后面生成了一个匿名类，这个类有三个属性，分别是 depid，depname和depsalary，其中，depsalary是经过计算获得，这句话被转换成：

SELECT [t0].[DepId] AS [depid], [t0].[DepName] AS [depname], (SELECT SUM([t1].[EmployeeSalary]) FROM [dbo].[Employee] AS [t1] WHERE [t1].[DepId] = [t0].[DepId] ) AS [depsalay] FROM [dbo].[Department] AS [t0]

好优雅的代码，实在是太方便了。

4、使用LINQ的扩展方法

LINQ提供了很多扩展方法，方便我们做各种查询，我们来看几个典型的扩展方法:

a) Average、Max

Decimal x = dbdata.Employees.Average(e => e.EmployeeSalary);

Decimal y = dbdata.Employees.Max(e => e.EmployeeSalary);

这是计算出员工的平均工资与最大工资，你不需要编写任何的SQL语句，Orcas中提供的LINQ到SQL对象关系映射器会处理获取，跟踪，和更新映射到你的数据库数据定义和存储过程的对象。你只要使用任何LINQ扩展方法对结果进行过滤和构形即可，LINQ到SQL会执行获取数据所需的SQL代码(注意，上面的 Average和Max 扩展方法很明显地不会从数据表中返回所有的数据行，它们会使用TSQL的聚合函数来计算数据库中的值，然后只返回一个标量值)。

b) Where、OrderBy

有时候，我们只对某张表做简单的查询和排序，那么，这个时候不必写冗长的LINQ语句，直接使用LINQ扩展方法即可，如：

var query = dbdata.Employees.Where(e => e.EmployeeSalary > 2000).OrderBy(e => e.EmployeeName);

这里使用了Lambda语法，这句话被转换成以下的SQL语句：

SELECT [t0].[EmployeeId], [t0].[DepId], [t0].[EmployeeName], [t0].[EmployeeSalary] FROM [dbo].[Employee] AS [t0] WHERE [t0].[EmployeeSalary] > @p0 ORDER BY [t0].[EmployeeName]

以上是通过利用由Lambda提供的对表达式树支持，以及IQueryable<T>接口来实现的，代码干净整洁。

C）Take、Skip

对于大批量的数据处理，一直是开发人员的比较头疼的事情，微软在.NET1.1到2.0中的Gridview等控件，对大批量数据的处理上一直都不是很理想，LINQ对于大批量数据的处理，可以很好的解决这个方面的问题。

var query = dbdata.Employees.Skip(10).Take(10);

这句话表示跳过该表的10条记录，再取10条，也就是取第11至20条记录，转换成SQL语句如下：

SELECT [t1].[EmployeeId], [t1].[DepId], [t1].[EmployeeName], [t1].[EmployeeSalary] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t0].[EmployeeId], [t0].[DepId], [t0].[EmployeeName], [t0].[EmployeeSalary]) AS [ROW_NUMBER], [t0].[EmployeeId], [t0].[DepId], [t0].[EmployeeName], [t0].[EmployeeSalary] FROM [dbo].[Employee] AS [t0]) AS [t1] WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1 ORDER BY [t1].[ROW_NUMBER]

从以上这句SQL语句来看，最消耗性能分页操作完全交给了数据库操作，其处理的机制不再像Gridview控件的分页，是将数据全部取出，然后再进行分页显示，因此效率上要高了很多。

D）ToList和ToArray

在默认情况下，查询结果的数据类型是IEnumerable<T>类型，可能很多开发人员并不习惯这个类型，而更加喜欢集合或者是数组，那么没关系，可以使用ToList或者是ToArray来将查询结果转换成集合或者数组。在这里，我们需要知道的是：使用查询语句查询结果的时候，实际上并没有真正操作数据库，这里是运用的延迟加载的机制，如果不希望使用延迟加载，而是需要立刻知道查询的结果时，使用ToList或者是ToArray便可以做到。这是非常有用的机制。比如我们需要显示两个部门的员工时，部门可以先取出放置在List中，然后再依次取出各个部门的员工，这时访问的效率要高一些，因为不需要每次都访问数据库去取出部门。

二：数据操作的性能分析

当我们会熟练的使用以上的查询方法对数据库里的内容做各种各样的查询以后，就应当要了解这些数据库操作的机制，及时调整各种数据操作语句，以较高的效率运行。那么，下面我们来看看LINQ的数据库操作，看看她都做了些什么工作。

与NHibernate 来比较，LINQ在O/R Mapping的性能与可控性上确实优于NHibernate，首先，Linq默认的数据映射采用的是Attribute来实现，这是.NET特有的语法，在编译时就已经决定了数据对象的各种属性，而NHibernate等大多数O/RMapping工具仍然采用XML映射文件来描述数据对象的属性，从外部文件上读取数据对象的属性，显然运行时效率要有所损失。其次,在获得数据的方式上也有所差别，LINQ中强大的SQL分析机制，可以分析出各种数据操作的SQL语句，并且进行优化，其效率的提升也是显而易见的。

当然，作为一个O/R Mapping的工具来说，其效率一定达不到直接使用SQL语句访问数据库的效率，也就是我们通常所说的 SqlDataReader/SqlDataAdapter访问数据库，但是，Linq的表现却给了我们非常大的惊喜，我做了一个测试，使用 SqlDataReader和LINQ做相同的大批量数据查询时，落后竟然不到10%，而NHibernate的查询效率，却低了很多，几乎慢了1倍。对于如此强大的数据映射功能，这样的效率是我们可以接受的。但是很可惜的一点是，LINQ目前只能支持对SQLServer的支持（但可以支持XML、 Entity等）。

在数据查询上，我们通过对LINQ生成的SQL语句进行分析，便可以优化查询，这是非常方便的，但是，针对数据更新的效率问题，我们不得不谈谈LINQ的数据更新机制，一般情况下，数据更新我们会这么做：

var query = from emp in dbdata.Employees where emp.DepId=="1001" select emp;

          Employee employee = query.First();

          employee.EmployeeName = "李四";

     dbdata.SubmitChanges();

对于以上这段代码，我们可以看出，其功能是从Employee表中取出部门代码为1001的所有员工，然后我们取出第一条数据（这里为了简便，我们仅仅取出第一条，其实可以用Where取出满足条件的记录），然后把名字修改成“李四”，再更新到数据库中。这段代码，LINQ都干了些什么呢？通过查询从数据库中取出若干条记录，放在内存中，并且都标记为new（未改变）状态，当修改了员工姓名的时候，被修改的对象被标记为Dirty(已改变),在 SubmitChanges的时候，再为内存中对象状态为Dirty的记录自动生成SQL语句并执行，也就是说，我们要完成一次数据的更新，至少要完成一次查询和一次更新。

由于采用了延时加载（Layze Load）的技术，在以上语句中实际从数据库中取出的记录只有1条，更新的时候也只更新这一条，因此效率仍然是非常高的，我在测试的过程中发现，从 250000条数据中随机抽取一条进行更新，实际的效率和从10条数据中随机抽取一条进行更新几乎没有差别，因为比较更新状态是在内存中进行，因此效率是比较高的。下面我们再看看实际的更新生成了什么样的SQL语句：

UPDATE [dbo].[Employee] SET [EmployeeName] = @p4 WHERE ([EmployeeId] = @p0) AND ([DepId] = @p1) AND

([EmployeeName] = @p2) AND ([EmployeeSalary] = @p3)

    原来，我们只修改了EmployeeName的字段，生成的SQL语句却也仅仅是更新了Employee字段。那么，我们再看看后面的条件，为什么会包含除了主键以外的其他条件呢？原来，这也是LINQ自动生成SQL语句的严谨所在，这是为了防止并发情况下，多个事务针对同一条记录更新时发生错误，假如A事务更新了该记录，则B事务更新会失败。我们不禁要问，假如要更新主键字段怎么办？会不会错误的更新到多条记录呢？答案是肯定的，肯定会错误的更新到其他记录，因此， LINQ中规定了主键字段是不允许更新的，如果确实要更新，那么就删除掉该记录，重新插入新纪录。这么严谨的SQL语句，会给我们带来一些麻烦，我们来看下面一个应用场景：

    如果我们在表中设有一个字段用于计数器，使用SQL语句是这样的：

    Update CountTable set CountColumn=CountColumn+1 where CountId=@countId

    但使用LINQ生成的Sql语句却是：

    UPDATE [dbo].[CountTable] SET [CountColumn] = @p2 WHERE ([CountId] = @p0) AND ([CountColumn] = @p1)

    @p2这个参数是计算好后传入的，@p1这个参数是CountColumn原来的值。也就是说，CountColumn+1这个值不是由数据库运算出来的，这样一来，当并发数很高的时候，我们往往会更新失败。我做了个测试，使用多线程模拟多用户的情况下进行计数统计，数据库中统计的值比使用静态变量保存的值要小，这也就是说数据库更新是存在失败的情况。另外，这样每次的更新，需要完成的操作有查找和更新两个步骤，因此对于效率也有比较大的影响。

在这里，我们并不是要说明LINQ存在缺陷，因为这种情况可能在任何的O/R Mapping的框架下都得不到很好的解决，这里仅仅是想告诉我们，只有了解系统内部运行的情况，才能设计出效率更高，更可靠的系统。

 

add record to db: InsertOnSubmit, InsertAllOnSubmit

Linq分页. 由于是延迟执行，直到.toList()执行的时候才执行具体的sql语句.. 所以，可以利用Linq做大数据量的分页解决方案.

var pagedC1 = (from c1 in db.Customers select c1).Skip(20).Take(10);
var arrayC1 = pagedC1.ToList();

跟踪sql server Profiler，生成的sql语句为：

exec sp_executesql N'SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER], [t0].[CustomerID],
[t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
    FROM [dbo].[Customers] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int',@p0=20,@p1=10

3. Entity Framework

4. 另外，还有NHibernate或者SubSonic