EF批量新增优化

数据库创建命令

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Adress') and o.name = 'FK_ADRESS_REFERENCE_USERS')
alter table Adress
   drop constraint FK_ADRESS_REFERENCE_USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Contacts') and o.name = 'FK_CONTACTS_REFERENCE_USERS')
alter table Contacts
   drop constraint FK_CONTACTS_REFERENCE_USERS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Adress')
            and   type = 'U')
   drop table Adress
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Contacts')
            and   type = 'U')
   drop table Contacts
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Table_1')
            and   type = 'U')
   drop table Table_1
go

if exists (select 1
            from  sysobjects
           where  id = object_id('Users')
            and   type = 'U')
   drop table Users
go

/*==============================================================*/
/* Table: Adress                                                */
/*==============================================================*/
create table Adress (
   Id                   bigint               not null,
   Adress               nvarchar(300)        null,
   constraint PK_ADRESS primary key (Id)
)
go

/*==============================================================*/
/* Table: Contacts                                              */
/*==============================================================*/
create table Contacts (
   Id                   bigint               not null,
   Mobile               varchar(100)         null,
   Tel                  varchar(100)         null,
   constraint PK_CONTACTS primary key (Id)
)
go

/*==============================================================*/
/* Table: Users                                                 */
/*==============================================================*/
create table Users (
   Name                 nvarchar(50)         not null,
   Id                   bigint               identity,
   Sex                  bit                  null,
   UserName             nvarchar(50)         null,
   Pwd                  varchar(500)         null,
   constraint PK_USERS primary key (Id)
)
go

alter table Adress
   add constraint FK_ADRESS_REFERENCE_USERS foreign key (Id)
      references Users (Id)
go

alter table Contacts
   add constraint FK_CONTACTS_REFERENCE_USERS foreign key (Id)
      references Users (Id)
go

表关系：

未优化添加：

static void Main(string[] args)
        {
            DateTime startDateTime = DateTime.Now;
            using (studyEntities study = new studyEntities())
            {
                List<Users> userList = new List<Users>();
                for (int i = 0; i < 10000; i++)
                {
                    Users u = new Users();
                    u.Name = "张三";
                    u.Sex = true;
                    u.UserName = "zhangsan";
                    u.Pwd = "2017";
                    u.Adress = new Adress { Adress1 = "朝阳区" };
                    u.Contacts = new Contacts { Mobile = "15210101212", Tel = "111221221" };
                    study.Users.Add(u);
                }
                DateTime insetLocalDateTime = DateTime.Now;
                TimeSpan local = insetLocalDateTime - startDateTime;
                Console.WriteLine("数量" + 10000 + " 本地添加到数据上下文耗时 " + local.TotalMinutes + " 分钟 = " + local.TotalSeconds + " 秒");
                study.SaveChanges();
                DateTime insetDataDateTime = DateTime.Now;
                TimeSpan data = insetDataDateTime - insetLocalDateTime;
                Console.WriteLine("数量" + 10000 + " 数据库添加完成耗时" + data.TotalMinutes + " 分钟 = " + data.TotalSeconds + " 秒");
            }
        }

优化解决方案一：关闭AutoDetectChangesEnabled 

 static void Main(string[] args)
        {
            DateTime startDateTime = DateTime.Now;
            using (studyEntities study = new studyEntities())
            {
                study.Configuration.AutoDetectChangesEnabled = false;
                List<Users> userList = new List<Users>();
                for (int i = 0; i < 10000; i++)
                {
                    Users u = new Users();
                    u.Name = "张三";
                    u.Sex = true;
                    u.UserName = "zhangsan";
                    u.Pwd = "2017";
                    u.Adress = new Adress { Adress1 = "朝阳区" };
                    u.Contacts = new Contacts { Mobile = "15210101212", Tel = "111221221" };
                    study.Users.Add(u);
                }
                DateTime insetLocalDateTime = DateTime.Now;
                TimeSpan local = insetLocalDateTime - startDateTime;
                Console.WriteLine("数量" + 10000 + " 本地添加到数据上下文耗时 " + local.TotalMinutes + " 分钟 = " + local.TotalSeconds + " 秒");
                study.SaveChanges();
                DateTime insetDataDateTime = DateTime.Now;
                TimeSpan data = insetDataDateTime - insetLocalDateTime;
                Console.WriteLine("数量" + 10000 + " 数据库添加完成耗时" + data.TotalMinutes + " 分钟 = " + data.TotalSeconds + " 秒");
            }
        }

优化解决方案二 EF6.0以上，新增批量添加的方法，调用即可，内部已作优化，调用 AddRange 方法

 static void Main(string[] args)
        {
            DateTime startDateTime = DateTime.Now;
            using (studyEntities study = new studyEntities())
            {
                study.Configuration.AutoDetectChangesEnabled = false;
                List<Users> userList = new List<Users>();
                for (int i = 0; i < 10000; i++)
                {
                    Users u = new Users();
                    u.Name = "张三";
                    u.Sex = true;
                    u.UserName = "zhangsan";
                    u.Pwd = "2017";
                    u.Adress = new Adress { Adress1 = "朝阳区" };
                    u.Contacts = new Contacts { Mobile = "15210101212", Tel = "111221221" };
                    userList.Add(u);
                }
                study.Users.AddRange(userList);
                DateTime insetLocalDateTime = DateTime.Now;
                TimeSpan local = insetLocalDateTime - startDateTime;
                Console.WriteLine("数量" + 10000 + " 本地添加到数据上下文耗时 " + local.TotalMinutes + " 分钟 = " + local.TotalSeconds + " 秒");
                study.SaveChanges();
                DateTime insetDataDateTime = DateTime.Now;
                TimeSpan data = insetDataDateTime - insetLocalDateTime;
                Console.WriteLine("数量" + 10000 + " 数据库添加完成耗时" + data.TotalMinutes + " 分钟 = " + data.TotalSeconds + " 秒");
            }
        }