EF Core批量插入与删除

我就是我 2021-07-27 00:01 677阅读 0赞

using EFPlus.Models;
    using Microsoft.EntityFrameworkCore;
    using System;
    using System.Diagnostics;
    using System.Linq;
    using System.Threading.Tasks;
    
    namespace EFPlus
    {
        class Program
        {
            static async Task Main(string[] args)
            {
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                //GetCount();
                await MultiDeleteWithSql();
    
                stopwatch.Stop();  //停止Stopwatch
                Console.WriteLine("Using Elapsed output runTime:{0}", stopwatch.Elapsed.ToString());//这里使用时间差来输出,如：时：分：秒
    
                Console.WriteLine("Hello World!");
            }
    
            //static void Main(string[] args)
            //{
            //    Stopwatch stopwatch = new Stopwatch();
            //    stopwatch.Start();
    
            //    MultiDelete();
    
            //    stopwatch.Stop();  //停止Stopwatch
            //    Console.WriteLine("Using Elapsed output runTime:{0}", stopwatch.Elapsed.ToString());//这里使用时间差来输出,如：时：分：秒
    
            //    Console.WriteLine("Hello World!");
            //}
    
    
            static void GetCount()
            {
                using (var db = new BlogDbContext())
                {
                    var allblogs = db.Blogs.Count();
                    Console.WriteLine(allblogs);
                }
    
            }
    
            static void MultiDelete()
            {
                using (var db = new BlogDbContext())
                {
                    var allblogs = db.Blogs.ToArrayAsync();
                    db.Blogs.RemoveRange(allblogs.Result);
                    db.SaveChanges();
                }
                
            }
    
            static async Task MultiDeleteAsync()
            {
                using (var db = new BlogDbContext())
                {
                    var allblogs = db.Blogs.ToArrayAsync();
                    db.Blogs.RemoveRange(allblogs.Result);
                    await db.SaveChangesAsync();
                }
            }
    
            /// <summary>
            /// 批量插入
            /// </summary>
            static void MultiInsert()
            {
                using (var db = new BlogDbContext())
                {
                    for (int i = 0; i < 1000000; i++)
                    {
                        var blog = new Blog { Url = "https://www.cnblog.com/p/" + i.ToString() };
                        db.Blogs.Add(blog);
                    }
                    db.SaveChanges();
                }
    
            }
    
            static async Task MultiInsertAsync()
            {
                using (var db = new BlogDbContext())
                {
    
                    for (int i = 0; i < 1000000; i++)
                    {
                        var blog = new Blog { Url = "https://www.cnblog.com/p/" + i.ToString() };
                        db.Blogs.Add(blog);
                    }
                    await db.SaveChangesAsync();
                }
            }
    
            static async Task MultiDeleteWithSql()
            {
                var db = new BlogDbContext();
                var conn = db.Database.GetDbConnection();
                try
                {
                    await conn.OpenAsync();
                    using (var command = conn.CreateCommand())
                    {
                        string query = "delete from blogs where Id<1000001";
                        command.CommandText = query;
                        var rows = await command.ExecuteNonQueryAsync();
                    }
                }
                finally
                {
                    conn.Close();
                }
            }
    
        }
    }

在release模式下，localdb批量插入一百万数据，用时：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70][]

localdb批量删除一百万，用时（等了3分钟...不等了）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 1][]

异步方法下批量插入一百万数据：用时：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 2][]

异步方法下批量删除一百万数据：用时：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 3][]

EF执行原生sql删除一百万：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 4][]

在sql server 2008 R2数据库批量插入一百万数据：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 5][]

在sql server 2008 R2数据库批量删除一百万数据(3分钟不等了...)：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 6][]

用EF执行原生sql批量删除：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70]: /images/20210726/eaa6dd3d08e84dd6806b399994185615.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 1]: /images/20210726/d2cdcf90fb004aa4afe7f57afef1927f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 2]: /images/20210726/e98dac7625d24ee39f3db8876f77fba0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 3]: /images/20210726/4d8c8e919486430c87b713ffa6f0760a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 4]: /images/20210726/917c0c99ab394c2688e1ab3833025cf5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 5]: /images/20210726/f5fe7a470c474d759e229991ba00a1d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 6]: /images/20210726/f51ce1dfb41540859ada272aa3418953.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpbmdrb25ndGlhbnl1emhhbw_size_16_color_FFFFFF_t_70 7]: /images/20210726/7035a36c04bc404bbd70c44d0a0ccb95.png