sqlserver 批量导入分析对比

#region 方式一  （EFBatchOperation，先插入主表，再查询主表结果再linq对应匹配，再批量插入从表）


                List<Customer> insert_customers_list = new List<Customer>();
                List<CustomerDetail> insert_customerDetail_list = new List<CustomerDetail>();

                foreach (var item in list)
                {
                    Customer customer = new Customer()
                    {
                        Name = item.Name,
                        PhoneNo = item.PhoneNo,
                        CreateUserId = this.Token_UserId,
                        CreateTime = DateTime.Now,
                        AddSource = (int)AddSource.Excel,
                        PartnerId = this.Token_PartnerId,
                    };
                    insert_customers_list.Add(customer);
                }

                if (insert_customers_list.Count > 0)
                {
                    EFBatchOperation.For(this.DbCtx, this.DbCtx.Customers)
                        .InsertAll(insert_customers_list);
                }

                //查询数据库刚插入的
                List<Customer> dbCustomer_list = await this.DbCtx.Customers.AsNoTracking()
                                                                                    .Where(a => phoneNoList.Contains(a.PhoneNo) && a.PartnerId == this.Token_PartnerId)
                                                                                    .ToListAsync();


                foreach (var item in list)
                {
                    Customer customer = dbCustomer_list.FirstOrDefault(a => a.PhoneNo == item.PhoneNo);
                    if (customer != null)
                    {
                        insert_customerDetail_list.Add(new CustomerDetail()
                        {
                            CustomerId = customer.Id,
                            Sex = item.DbSex,
                            WeChatNo = item.WeChatNo,
                            Qq = item.QQ,
                            Email = item.Email,
                            CompanyName = item.CompanyName,
                            CompanyAddress = item.CompanyAddress,
                            CompanyWebSite = item.CompanyWebSite,
                        });
                    }
                }

                if (insert_customerDetail_list.Count > 0)
                {
                    EFBatchOperation.For(this.DbCtx, this.DbCtx.CustomerDetails)
                        .InsertAll(insert_customerDetail_list);
                }

            #endregion 方式一

#region 方式二  （主表用 EF的 表上下文context.Add方法，再批量 SaveChanges 保存 并能返回主表插入后的id，再用linq 去关联 数据，再 从表用   EFBatchOperation 插入）


                List<Customer> insert_customers_list = new List<Customer>();
                List<CustomerDetail> insert_customerDetail_list = new List<CustomerDetail>();

                foreach (var item in list)
                {
                    Customer customer = DbCtx.Customers.Add(new Customer()
                    {
                        Name = item.Name,
                        PhoneNo = item.PhoneNo,
                        CreateUserId = this.Token_UserId,
                        CreateTime = DateTime.Now,
                        AddSource = (int)AddSource.Excel,
                        PartnerId = this.Token_PartnerId,
                    });

                    insert_customers_list.Add(customer);
                }

                var count = await this.DbCtx.SaveChangesAsync();




                foreach (var item in insert_customers_list)
                {
                    CustomerExcelData customer = list.FirstOrDefault(a => a.PhoneNo == item.PhoneNo);
                    if (customer != null)
                    {
                        insert_customerDetail_list.Add(new CustomerDetail()
                        {
                            CustomerId = item.Id,
                            Sex = customer.DbSex,
                            WeChatNo = customer.WeChatNo,
                            Qq = customer.QQ,
                            Email = customer.Email,
                            CompanyName = customer.CompanyName,
                            CompanyAddress = customer.CompanyAddress,
                            CompanyWebSite = customer.CompanyWebSite,
                        });
                    }
                }

                if (insert_customerDetail_list.Count > 0)
                {
                    EFBatchOperation.For(this.DbCtx, this.DbCtx.CustomerDetails)
                        .InsertAll(insert_customerDetail_list);
                }

            #endregion 方式二

#region 方式三  （拼接sql，使用 T-SQL 的系统函数SCOPE_IDENTITY()得到主表的id，以整行对象提交少了查询和关联步骤 ）



                #region 格式化 的sql
                //       INSERT INTO[dbo].[Customer]
                //      ([Name]
                //      ,[PhoneNo]
                //      ,[CreateUserId]
                //      ,[CreateTime]
                //      ,[AddSource]
                //      ,[PartnerId]
                //      ,[Deleted])
                //VALUES
                //      ('zhege'
                //      , 'zhege'
                //      , 1
                //      , '2019-05-30'
                //      , 1
                //      , 1
                //      , 1);

                //       INSERT INTO[dbo].[CustomerDetail]
                //   ([CustomerId]
                //      ,[Sex]
                //      ,[WeChatNo]
                //      ,[QQ]
                //      ,[Email]
                //      ,[CompanyName]
                //      ,[CompanyAddress]
                //      ,[CompanyWebSite])
                //VALUES
                //      (@@identity
                //      ,1
                //      ,'zhege'
                //      ,'zhege'
                //      ,'zhege'
                //      ,'zhege'
                //      ,'zhege'
                //      ,'zhege');
                #endregion

                StringBuilder stringBuilder = new StringBuilder();
                foreach (var item in list)
                {
                    //这里使用压缩 sql 语句，减小传输数据大小
                    //stringBuilder.AppendLine($"INSERT INTO [dbo].[Customer] ([Name], [PhoneNo], [CreateUserId], [CreateTime], [AddSource] , [PartnerId], [Deleted]) VALUES ('{item.Name}', '{item.PhoneNo}', {this.Token_UserId}, '{DateTime.Now}', {(int)AddSource.Excel} , {this.Token_PartnerId}, 0); INSERT INTO [dbo].[CustomerDetail] ([CustomerId], [Sex], [WeChatNo], [QQ], [Email] , [CompanyName], [CompanyAddress], [CompanyWebSite]) VALUES (SCOPE_IDENTITY(), {item.DbSex}, '{item.WeChatNo}', '{item.QQ}', '{item.Email}' , '{item.CompanyName}', '{item.CompanyAddress}', '{item.CompanyWebSite}');");
                    //这里不压缩
                    stringBuilder.AppendLine($@"
                                            INSERT INTO [dbo].[Customer] 
                                            ([Name]
                                            , [PhoneNo]
                                            , [CreateUserId]
                                            , [CreateTime]
                                            , [AddSource] 
                                            , [PartnerId]
                                            , [Deleted]) 
                                            VALUES 
                                            ('{item.Name}'
                                            , '{item.PhoneNo}'
                                            , {this.Token_UserId}
                                            , '{DateTime.Now}'
                                            , {(int)AddSource.Excel} 
                                            , {this.Token_PartnerId}
                                            , 0); 

                                            INSERT INTO [dbo].[CustomerDetail] 
                                            ([CustomerId]
                                            , [Sex]
                                            , [WeChatNo]
                                            , [QQ]
                                            , [Email] 
                                            , [CompanyName]
                                            , [CompanyAddress]
                                            , [CompanyWebSite]) 
                                            VALUES 
                                            (SCOPE_IDENTITY()
                                            , {item.DbSex}
                                            , '{item.WeChatNo}'
                                            , '{item.QQ}'
                                            , '{item.Email}' 
                                            , '{item.CompanyName}'
                                            , '{item.CompanyAddress}'
                                            , '{item.CompanyWebSite}');


                        ");
                }
                //执行sql
                int count = await DbCtx.Database.ExecuteSqlCommandAsync(stringBuilder.ToString());



            #endregion 方式三 

 方式三如果有多个从表则像下面这样使用 T-SQL 的临时变量实现

--声明变量保存最近插入的 主表 id
DECLARE @CurrentId int;
--这里插入主表A
INSERT INTO [dbo].[Customer]
	([Name], [PhoneNo], [CreateUserId], [CreateTime], [AddSource]
	, [PartnerId], [Deleted])
VALUES ('zhege', 'zhege', 1, '2019-05-30', 1
	, 1, 1);
--赋值变量， 为最近插入的主表主键，供后面从表插入时使用的外键
SET @CurrentId = SCOPE_IDENTITY();
--这里插入从表B
INSERT INTO [dbo].[CustomerDetail]
	([CustomerId], [Sex], [WeChatNo], [QQ], [Email]
	, [CompanyName], [CompanyAddress], [CompanyWebSite])
VALUES (@CurrentId, 1, 'zhege', 'zhege', 'zhege'
	, 'zhege', 'zhege', 'zhege');
--这里插入从表C
INSERT INTO [dbo].[UserCustomer] ([SysUserId], [CustomerId], [NoteName], [Remake], [Deleted])
VALUES (3, @CurrentId, 'zhege', 'zhege', 1);
--这里插入主表A
INSERT INTO [dbo].[Customer]
	([Name], [PhoneNo], [CreateUserId], [CreateTime], [AddSource]
	, [PartnerId], [Deleted])
VALUES ('zhege', 'zhege', 1, '2019-05-30', 1
	, 1, 1);
--赋值变量， 为最近插入的主表主键，供后面从表插入时使用的外键
SET @CurrentId = SCOPE_IDENTITY();
--这里插入从表B
INSERT INTO [dbo].[CustomerDetail]
	([CustomerId], [Sex], [WeChatNo], [QQ], [Email]
	, [CompanyName], [CompanyAddress], [CompanyWebSite])
VALUES (@CurrentId, 1, 'zhege', 'zhege', 'zhege'
	, 'zhege', 'zhege', 'zhege');
--这里插入从表C
INSERT INTO [dbo].[UserCustomer] ([SysUserId], [CustomerId], [NoteName], [Remake], [Deleted])
VALUES (3, @CurrentId, 'zhege', 'zhege', 1)

 下面是测试结果

方式三的结果看出 拼接sql语句 如果语法压缩下 能提升几倍性能

在线压缩地址http://www.bejson.com/otherformat/sql/