Posted on by Eric

比较使用Linq To Entity、自已编写ORM映射、以及使用缓存保存常用数据之间的性能

第1步 建立数据库

首先我们模拟1个需求环境：会员参加活动，生成活动记录，会员有性别、所属区域字段。
第1步，建立Sql Server数据库，Members表保存成员的基本信息，其中SexId、AreaId为外键，分别关联Sex性别表、Areas区域表；Activitys表保存活动的标题；ActivityRecords表保存成员参加活动的记录，其中MemberId、ActivityId为外键，分别关联Members成员表、Activitys活动表。

下面是创建表结构的Sql语句：

/****** Object:  Table [dbo].[Areas]    Script Date: 12/16/2011 14:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Areas](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](10) NULL,
 CONSTRAINT [PK_Area] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Activitys]    Script Date: 12/16/2011 14:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Activitys](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](50) NULL,
 CONSTRAINT [PK_Activitys] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Sexes]    Script Date: 12/16/2011 14:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sexes](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](10) NULL,
 CONSTRAINT [PK_Sex] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Members]    Script Date: 12/16/2011 14:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Members](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](10) NULL,
	[SexId] [bigint] NULL,
	[AreaId] [bigint] NULL,
	[Email] [nvarchar](50) NULL,
 CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[ActivityRecords]    Script Date: 12/16/2011 14:10:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ActivityRecords](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[MemberId] [bigint] NULL,
	[ActivityId] [bigint] NULL,
 CONSTRAINT [PK_ActivityRecords] PRIMARY KEY CLUSTERED
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  ForeignKey [FK_ActivityRecords_Activitys]    Script Date: 12/16/2011 14:10:01 ******/
ALTER TABLE [dbo].[ActivityRecords]  WITH CHECK ADD  CONSTRAINT [FK_ActivityRecords_Activitys] FOREIGN KEY([ActivityId])
REFERENCES [dbo].[Activitys] ([Id])
GO
ALTER TABLE [dbo].[ActivityRecords] CHECK CONSTRAINT [FK_ActivityRecords_Activitys]
GO
/****** Object:  ForeignKey [FK_ActivityRecords_Members]    Script Date: 12/16/2011 14:10:01 ******/
ALTER TABLE [dbo].[ActivityRecords]  WITH CHECK ADD  CONSTRAINT [FK_ActivityRecords_Members] FOREIGN KEY([MemberId])
REFERENCES [dbo].[Members] ([Id])
GO
ALTER TABLE [dbo].[ActivityRecords] CHECK CONSTRAINT [FK_ActivityRecords_Members]
GO
/****** Object:  ForeignKey [FK_Member_Sex]    Script Date: 12/16/2011 14:10:01 ******/
ALTER TABLE [dbo].[Members]  WITH CHECK ADD  CONSTRAINT [FK_Member_Sex] FOREIGN KEY([SexId])
REFERENCES [dbo].[Sexes] ([Id])
GO
ALTER TABLE [dbo].[Members] CHECK CONSTRAINT [FK_Member_Sex]
GO
/****** Object:  ForeignKey [FK_Members_Areas]    Script Date: 12/16/2011 14:10:01 ******/
ALTER TABLE [dbo].[Members]  WITH CHECK ADD  CONSTRAINT [FK_Members_Areas] FOREIGN KEY([AreaId])
REFERENCES [dbo].[Areas] ([Id])
GO
ALTER TABLE [dbo].[Members] CHECK CONSTRAINT [FK_Members_Areas]
GO

第2步，使用以下Sql语句初始化数据库：

DBCC CHECKIDENT([Activitys], RESEED, 0)
GO
DBCC CHECKIDENT([Areas], RESEED, 0)
Go
DBCC CHECKIDENT([Sexes], RESEED, 0)
GO
DBCC CHECKIDENT([Members], RESEED, 0)
GO

INSERT INTO [Activitys]
           ([Name])
     VALUES
           ('帮助老奶奶洗衣服活动')
GO
INSERT INTO [Activitys]
           ([Name])
     VALUES
           ('帮助老爷爷过马路活动')
GO

INSERT INTO [Areas]
           ([Name])
     VALUES
           ('安徽')
GO
INSERT INTO [Areas]
           ([Name])
     VALUES
           ('江苏')
GO
INSERT INTO [Areas]
           ([Name])
     VALUES
           ('北京')
GO
INSERT INTO [Areas]
           ([Name])
     VALUES
           ('上海')
GO

INSERT INTO [Sexes]
           ([Name])
     VALUES
           ('男')
GO
INSERT INTO [Sexes]
           ([Name])
     VALUES
           ('女')
GO

INSERT INTO [Members]
           ([Name],[AreaId],[Email],[SexId])
     VALUES
           ('Chocolate','1','[email protected]','1')
GO
INSERT INTO [Members]
           ([Name],[AreaId],[Email],[SexId])
     VALUES
           ('niyouzhu','1','[email protected]','1')
GO
INSERT INTO [Members]
           ([Name],[AreaId],[Email],[SexId])
     VALUES
           ('lingling','1','[email protected]','2')
GO

OK，数据库建设完毕。现在我们使用VS2010建立项目。

第2步 建立项目结构

本项目假设日后有可能使用别的数据库，所以在此我们使用数据工厂的模式，先看项目结构图：

Chocolate.Common是公共库，里面存放着1些通用的helper、接口之类，是我1直以来做项目收集的1些类库；IFactory是工厂接口，定义工厂的服务，比如GetMemberServices；IServices是服务接口，定义服务的具体动作；Model是通过自己手动进行ORM映射的类库；MsSqlFactory是使用MsSql数据库对工厂接口的具体实现；MsSqlModel是通过Ado.net Entity Framework4进行ORM映射的类库；MsSqlServices是使用MsSql数据库对服务接口的具体实现；Web是用来演示环境的Asp.net MVC 3网站；Win是添加10万条模拟数据的Windows应用程序。

第3步 建立基础文件

现在我们来创建代码。
在IFactory项目中创建IServicesFactory.cs文件：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace IFactory
{
    /// <summary>
    /// 工厂服务接口
    /// </summary>
    public interface IServicesFactory
    {
        /// <summary>
        /// 获得成员服务
        /// </summary>
        /// <returns></returns>
        IServices.IMemberServices GetMemberServices();
        /// <summary>
        /// 获得活动服务
        /// </summary>
        /// <returns></returns>
        IServices.IActivityServices GetActivityServices();
        /// <summary>
        /// 获得活动记录服务
        /// </summary>
        /// <returns></returns>
        IServices.IActivityRecordServices GetActivityRecordServices();

    }
}

在IServcies项目中建立IActivityRecordServices.cs文件：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace IServices
{
    /// <summary>
    /// 活动记录服务
    /// </summary>
    public interface IActivityRecordServices
    {
        /// <summary>
        /// 通过活动id查找活动记录，使用自己建立ORM的方式
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        List<Model.All> SelectWhereId(int id);
        /// <summary>
        /// 通过活动id查找活动记录，使用Entity Framework 4的方式
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        List<MsSqlModel.All> SelectWhereIdEntity4(int id);
        /// <summary>
        /// 使用Entity Framework 4插入单个对象
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        int Insert(MsSqlModel.ActivityRecord data);
        /// <summary>
        /// 使用Entity Framework 批量插入对象
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        int Insert(List<MsSqlModel.ActivityRecord> data);
    }
}

下面我们开始自己编写ORM映射：

在Model项目中建立以下关系对象映射文件：

Activity.cs，映射Activitys表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Model
{
    public class Activity
    {
        public long Id { get; set; }
        public string Name { get; set; }
    }
}

ActivityRecord.cs，映射ActivityRecords表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    public class ActivityRecord
    {
        public long Id { get; set; }
        public long MemberId { get; set; }
        public long ActivityId { get; set; }
    }
}

Area.cs，映射Areas表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    public class Area
    {
        public long Id { get; set; }
        public string Name { get; set; }
    }
}

Member.cs，映射Members表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    public class Member
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public long SexId { get; set; }
        public long AreaId { get; set; }
        public string Email { get; set; }
    }
}

Sex.cs，映射Sexes表

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    public class Sex
    {
        public long Id { get; set; }
        public string Name { get; set; }
    }
}

通过这几个文件，我们已经将数据库中的表结构与对象进行了映射，但是我们在实际应用中会使用到join等多表查询的语句，结果集并不是1个对象集合就能够表示，在前面的文章《关于使用Linq进行join查询及匿名类型的有关操作和探索》中我们已经提到可以使用匿名类型或者定义1个包含所有对象的总对象，我们这里使用的是后1种方法，为此我们定义了1个总对象：All.cs

All.cs，包含所有的表结构对象

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    public class All
    {
        /// <summary>
        /// 活动对象实例
        /// </summary>
        public Activity Activity = new Activity();
        /// <summary>
        /// 活动记录对象实例
        /// </summary>
        public ActivityRecord ActivityRecord = new ActivityRecord();
        /// <summary>
        /// 区别对象实例
        /// </summary>
        public Area Area = new Area();
        /// <summary>
        /// 成员对象实例
        /// </summary>
        public Member Member = new Member();
        /// <summary>
        /// 性别对象实例
        /// </summary>
        public Sex Sex = new Sex();
    }

}

下面开始使用Ado.Net Entity Framework 4建立ORM映射：
在MsSqlModel中建立Model.edmx文件，

设置数据连接

选择数据库对象

我们发现通过Ado.net 实体数据模型，已经自动为我们生成了相关的ORM映射关系，并且和有关的对象增、删、改等，但是类似于手动建立ORM映射，由于我们的查询结果往往是复杂的，所以我们还需要建立1个统一的总对象All.cs：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MsSqlModel
{
    public class All
    {
        /// <summary>
        /// 成员ID
        /// </summary>
        public long MemberId { get; set; }
        /// <summary>
        /// 成员名称
        /// </summary>
        public string MemberName { get; set; }
        /// <summary>
        /// 成员Email
        /// </summary>
        public string MemberEmail { get; set; }
        /// <summary>
        /// 性别名称
        /// </summary>
        public string SexName { get; set; }
        /// <summary>
        /// 区域名称
        /// </summary>
        public string AreaName { get; set; }
        /// <summary>
        /// 活动名称
        /// </summary>
        public string ActivityName { get; set; }
        /// <summary>
        /// 活动ID
        /// </summary>
        public long ActivityId { get; set; }
        /// <summary>
        /// 区别ID
        /// </summary>
        public long AreaId { get; set; }
        /// <summary>
        /// 性别ID
        /// </summary>
        public long SexId { get; set; }
        /// <summary>
        /// 活动记录ID
        /// </summary>
        public long ActivityRecordId { get; set; }
    }
}

细心的朋友可能发现此处建立的All成员没有使用类成员方式，比如

Publie Member Member = new Member();

而是使用基本数据类型，这是由于linq查询结果不支持使用复杂类型的缘故，即是说类似于下面的查询是行不通的：

var sql = from activityRecord in db.ActivityRecords
                          join member in db.Members
                          on activityRecord.MemberId equals member.Id
                          join sex in db.Sexes
                          on member.SexId equals sex.Id
                          join area in db.Areas
                          on member.AreaId equals area.Id
                          join activity in db.Activitys
                          on activityRecord.ActivityId equals activity.Id
                          where activity.Id == (long)id
                          orderby activityRecord.Id descending
                          select new MsSqlModel.All()
                          {
                              Activity.Id = activity.Id,
                              Activity.Name = activity.Name,
                              ActivityRecordId.Id = activityRecord.Id,
                              Area.Id = area.Id,
                              Area.Name = area.Name,
                              Member.Email = member.Email,
                              Member.Id = member.Id,
                              Member.Name = member.Name,
                              Sex.Id = sex.Id,
                              Sex.Name = sex.Name

                          };

在MsSqlFactory项目中建立ServicesFactory.cs文件，该文件继承IFactory.IServicesFactory接口：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MsSqlFactory
{
    /// <summary>
    /// MsSql数据库的工厂服务
    /// </summary>
    public class ServicesFactory : IFactory.IServicesFactory
    {
        /// <summary>
        /// 获得成员服务
        /// </summary>
        /// <returns></returns>
        public IServices.IMemberServices GetMemberServices()
        {
            return new MsSqlServices.MemberServices();
        }

        /// <summary>
        /// 获得活动服务
        /// </summary>
        /// <returns></returns>
        public IServices.IActivityServices GetActivityServices()
        {
            return new MsSqlServices.ActivityServices();
        }

        /// <summary>
        /// 获得活动记录服务
        /// </summary>
        /// <returns></returns>
        public IServices.IActivityRecordServices GetActivityRecordServices()
        {
            return new MsSqlServices.ActivityRecordServices();
        }
    }
}

在MsSqlServices项目中建立ActivityRecordServices.cs文件，该文件继承IServices.IActivityRecordServices和Chocolate.Common.Interface.IDataToEntity<T>接口，Chocolate.Common.Interface.IDataToEntity<T>接口的作用是将DataTable、DataReader数据转成实体：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace MsSqlServices
{
    public class ActivityRecordServices : IServices.IActivityRecordServices, Chocolate.Common.Interface.IDataToEntity<Model.All>
    {
        /// <summary>
        /// 通过活动id查找活动记录，使用自己建立ORM的方式
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Model.All> SelectWhereId(int id)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["mssql"].ConnectionString;
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string sql = @"SELECT   Activitys.Id AS ActivityId, Activitys.Name AS ActivityName, ActivityRecords.Id AS ActivityRecordId,
                            ActivityRecords.MemberId, ActivityRecords.ActivityId AS Expr1, Members.Id AS MemberId,
                            Members.Name AS MemberName, Members.SexId, Members.AreaId, Members.Email, Sexes.Id AS SexId,
                            Sexes.Name AS SexName, Areas.Id AS AreaId, Areas.Name AS AreaName
                            FROM  ActivityRecords     INNER JOIN
                            Activitys ON ActivityRecords.ActivityId = Activitys.Id INNER JOIN
                            Members ON ActivityRecords.MemberId = Members.Id INNER JOIN
                            Sexes ON Members.SexId = Sexes.Id INNER JOIN
                            Areas ON Members.AreaId = Areas.Id
                            WHERE   (Activitys.Id = @Id)
                            ORDER BY ActivityRecordId DESC";
                cmd.CommandText = sql;
                List<SqlParameter> parameters = new List<SqlParameter>();
                parameters.Add(new SqlParameter("@Id", id));
                cmd.Parameters.AddRange(parameters.ToArray());
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                //将sqldatareader转成实体集合
                List<Model.All> activityRecords = ConvertToList(reader);
                return activityRecords;
            }
        }

        /// <summary>
        /// 通过活动id查找活动记录，使用Entity Framework 4的方式
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<MsSqlModel.All> SelectWhereIdEntity4(int id)
        {
            using (MsSqlModel.ModelEntities db = new MsSqlModel.ModelEntities())
            {
                var sql = from activityRecord in db.ActivityRecords
                          join member in db.Members
                          on activityRecord.MemberId equals member.Id
                          join sex in db.Sexes
                          on member.SexId equals sex.Id
                          join area in db.Areas
                          on member.AreaId equals area.Id
                          join activity in db.Activitys
                          on activityRecord.ActivityId equals activity.Id
                          where activity.Id == (long)id
                          orderby activityRecord.Id descending
                          select new MsSqlModel.All()
                          {
                              ActivityId = activity.Id,
                              ActivityName = activity.Name,
                              ActivityRecordId = activityRecord.Id,
                              AreaId = area.Id,
                              AreaName = area.Name,
                              MemberEmail = member.Email,
                              MemberId = member.Id,
                              MemberName = member.Name,
                              SexId = sex.Id,
                              SexName = sex.Name

                          };
                return sql.ToList<MsSqlModel.All>();
            }
        }

        /// <summary>
        /// 使用Entity Framework 4插入单个对象
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int Insert(MsSqlModel.ActivityRecord data)
        {
            using (MsSqlModel.ModelEntities db = new MsSqlModel.ModelEntities())
            {
                db.ActivityRecords.AddObject(data);
                return db.SaveChanges();
            }

        }

        /// <summary>
        /// 使用Entity Framework 批量插入对象
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int Insert(List<MsSqlModel.ActivityRecord> data)
        {
            using (MsSqlModel.ModelEntities db = new MsSqlModel.ModelEntities())
            {
                foreach (MsSqlModel.ActivityRecord o in data)
                {
                    db.ActivityRecords.AddObject(o);
                }
                return db.SaveChanges();
            }
        }

        #region 实现Chocolate.Common.Interface.IDataToEntity<T>集合，将数据转成实体
        /// <summary>
        /// 将DataTable数据集转成相应的实体集合
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public List<Model.All> ConvertToList(System.Data.DataTable data)
        {
            List<Model.All> activityRecords = new List<Model.All>();
            System.Data.DataTableReader reader = data.CreateDataReader();
            for (int i = 0; i < data.Rows.Count; i++)
            {
                if (reader.Read())
                {
                    Model.All o = new Model.All();
                    o.Activity.Id = Convert.ToInt64(reader["ActivityId"]);
                    o.Activity.Name = Convert.ToString(reader["ActivityName"]);
                    o.ActivityRecord.Id = Convert.ToInt64(reader["ActivityRecordId"]);
                    o.ActivityRecord.MemberId = Convert.ToInt64(reader["MemberId"]);
                    o.ActivityRecord.ActivityId = Convert.ToInt64(reader["Expr1"]);
                    o.Member.Id = Convert.ToInt64(reader["MemberId"]);
                    o.Member.Name = Convert.ToString(reader["MemberName"]);
                    o.Member.SexId = Convert.ToInt64(reader["SexId"]);
                    o.Member.AreaId = Convert.ToInt64(reader["AreaId"]);
                    o.Member.Email = Convert.ToString(reader["Email"]);
                    o.Sex.Id = Convert.ToInt64(reader["SexId"]);
                    o.Sex.Name = Convert.ToString(reader["SexName"]);
                    o.Area.Id = Convert.ToInt64(reader["AreaId"]);
                    o.Area.Name = Convert.ToString(reader["AreaName"]);
                    activityRecords.Add(o);
                }
            }
            return activityRecords;
        }

        /// <summary>
        /// 将SqlDataReader数据转成相应的实体集合
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public List<Model.All> ConvertToList(System.Data.SqlClient.SqlDataReader data)
        {
            System.Data.DataTable table = new System.Data.DataTable();
            table.Load(data);
            return ConvertToList(table);
        }

        public List<Model.All> ConvertToList(DataTableReader data)
        {
            throw new NotImplementedException();
        }

        public List<Model.All> ConvertToList(DataRow row)
        {
            throw new NotImplementedException();
        }
        #endregion
    }
}

至此相关的接口和服务代码已经结束，另外的Activity、Member代码与ActivtyRecord类似，见文章最后的源代码文件。

第4步 建立测试文件

进入Win项目，在Form1.cs上画2个文本框和1个按钮，文本框Name分别为textBoxMemberId和textBoxActivityId，按钮Name为buttonSubmit：

双击按钮，添加以下代码：

        private void buttonSubmit_Click(object sender, EventArgs e)
        {
            long activityId = Convert.ToInt64(textBoxActivityId.Text);
            long memberId = Convert.ToInt64(textBoxMemberId.Text);
            DateTime t1 = DateTime.Now;
            //实例1个List
            List<MsSqlModel.ActivityRecord> records = new List<MsSqlModel.ActivityRecord>();
            for (int i = 0; i < 100000; i++)
            {
                records.Add(new MsSqlModel.ActivityRecord() { ActivityId = activityId, MemberId = memberId });
            }
            MsSqlServices.ActivityRecordServices services = new MsSqlServices.ActivityRecordServices();
            //使用Entiry Framework 4批量插入对象
            int count = services.Insert(records);
            DateTime t2 = DateTime.Now;
            TimeSpan span = t2 - t1;
            MessageBox.Show(@"成功插入" + count);
            MessageBox.Show(@"花费时间：" + span.TotalMilliseconds);
        }

编译该Win程序，并运行，下面是在我电脑上运行得到的结果，约35秒：

进入Web项目，在Controller文件夹中添加ActivityRecordController.cs文件，并加入以下代码：

    public class ActivityRecordController : Controller
    {
        /// <summary>
        /// 自己编写ORM的Action
        /// </summary>
        /// <returns></returns>
        public ActionResult Index()
        {
            int id = int.Parse(Request["Id"]);
            DateTime t1 = DateTime.Now;
            List<Model.All> activityRecords = DependencyLocate.FactoryContainer.Factory.GetActivityRecordServices().SelectWhereId(id);
            ViewData["activityRecords"] = activityRecords;
            DateTime t2 = DateTime.Now;
            TimeSpan timeSpan = t2 - t1;
            ViewData["startTime"] = t1;
            ViewData["endTime"] = t2;
            ViewData["timeSpan"] = timeSpan.TotalMilliseconds;
            return View();
        }

        public ActionResult Form()
        {
            return View();
        }

        /// <summary>
        /// 使用Entity4的Action
        /// </summary>
        /// <returns></returns>
        public ActionResult IndexEntity4()
        {

            int id = int.Parse(Request["Id"]);
            DateTime t1 = DateTime.Now;
            List<MsSqlModel.All> activityRecords = new List<MsSqlModel.All>();
            activityRecords = DependencyLocate.FactoryContainer.Factory.GetActivityRecordServices().SelectWhereIdEntity4(id);
            ViewData["activityRecords"] = activityRecords;
            DateTime t2 = DateTime.Now;
            TimeSpan timeSpan = t2 - t1;
            ViewData["startTime"] = t1;
            ViewData["endTime"] = t2;
            ViewData["timeSpan"] = timeSpan.TotalMilliseconds;
            System.Web.Helpers.WebCache.Set("activityRecords", activityRecords, 20);

            return View();
        }

        public ActionResult FormEntity4()
        {
            return View();
        }

        /// <summary>
        /// 使用Cache的Action
        /// </summary>
        /// <returns></returns>
        public ActionResult IndexCache()
        {
            int id = int.Parse(Request["Id"]);
            DateTime t1 = DateTime.Now;
            List<MsSqlModel.All> activityRecords = new List<MsSqlModel.All>();
            //使用缓存
            activityRecords = System.Web.Helpers.WebCache.Get("activityRecords");
            if (activityRecords == null)
            {
                activityRecords = DependencyLocate.FactoryContainer.Factory.GetActivityRecordServices().SelectWhereIdEntity4(id);
                System.Web.Helpers.WebCache.Set("activityRecords", activityRecords, 20, true);
            }
            ViewData["activityRecords"] = activityRecords;
            DateTime t2 = DateTime.Now;
            TimeSpan timeSpan = t2 - t1;
            ViewData["startTime"] = t1;
            ViewData["endTime"] = t2;
            ViewData["timeSpan"] = timeSpan.TotalMilliseconds;
            System.Web.Helpers.WebCache.Set("activityRecords", activityRecords, 20);
            return View();
        }
        public ActionResult FormCache()
        {
            return View();
        }
    }

有关View的代码可以在文章最后的项目压缩包里查找。

最后我们来看1下查询性能对比吧

分别打开页面/ActivityRecord/Form，/ActivityRecord/FormEntity4，/ActivityRecord/FormCache，然后每页面刷新3次，统计最后的结果如下：

自己编写ORM的方式 使用Entity4和Linq2Entity查询 使用缓存的方式
第1次 5306.3035 3212.1837 4380.6637
第2次 6597.3774 2435.1392 0
第3次 4680.2677 2173.1243 0

我们可以发现，使用缓存是最快的，使用Linq次之，使用Join查询性能最差。通过Sql Server Profiler进行追踪，我们发现使用Linq，其实最后转化的SQL查询语句是：

exec sp_executesql N'SELECT 
[Project1].[C1] AS [C1], 
[Project1].[Id4] AS [Id], 
[Project1].[Name3] AS [Name], 
[Project1].[Id] AS [Id1], 
[Project1].[Id3] AS [Id2], 
[Project1].[Name2] AS [Name1], 
[Project1].[Email] AS [Email], 
[Project1].[Id1] AS [Id3], 
[Project1].[Name] AS [Name2], 
[Project1].[Id2] AS [Id4], 
[Project1].[Name1] AS [Name3]
FROM ( SELECT 
	[Extent1].[Id] AS [Id], 
	[Extent2].[Id] AS [Id1], 
	[Extent2].[Name] AS [Name], 
	[Extent2].[Email] AS [Email], 
	[Extent3].[Id] AS [Id2], 
	[Extent3].[Name] AS [Name1], 
	[Extent4].[Id] AS [Id3], 
	[Extent4].[Name] AS [Name2], 
	[Extent5].[Id] AS [Id4], 
	[Extent5].[Name] AS [Name3], 
	1 AS [C1]
	FROM     [dbo].[ActivityRecords] AS [Extent1]
	INNER JOIN [dbo].[Members] AS [Extent2] ON [Extent1].[MemberId] = [Extent2].[Id]
	INNER JOIN [dbo].[Sexes] AS [Extent3] ON [Extent2].[SexId] = [Extent3].[Id]
	INNER JOIN [dbo].[Areas] AS [Extent4] ON [Extent2].[AreaId] = [Extent4].[Id]
	INNER JOIN [dbo].[Activitys] AS [Extent5] ON [Extent1].[ActivityId] = [Extent5].[Id]
	WHERE [Extent5].[Id] = @p__linq__0
)  AS [Project1]
ORDER BY [Project1].[Id] DESC',N'@p__linq__0 bigint',@p__linq__0=1

OK，终于结束，现在把本项目的源文件奉上。可能你看不懂这篇日志在写什么，那是因为你还没有到那个火候，慢慢来吧，不要急。源代码，各位下载后，需在解决方案里将Chocolate.Common项目卸载，然后进入Web/bin目录，将Chocolate.Common.dl文件引用到相关项目。

Leave a Reply

Your email address will not be published. Required fields are marked *