EFCore之增刪改查

2021 年 5 月 24 日
筆記
.NET, C井, netcore

1. 連接數據庫

通過依賴注入配置應用程序，通過startup類的ConfigureService方法中的AddDbContext將EFCore添加到依賴注入容器

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    services.AddDbContext<OpenDbContext>(
        options => options.UseMySql(Configuration["DbConfig:Mysql:ConnectionString"]);
}

將名為 OpenDbContext的 DbContext 子類註冊到依賴注入容器的Scope生命周期。上下文配置為使用MySQL數據庫提供程序，並從配置中讀取數據庫連接字符串。

OpenDbContext類必須公開具有 DbContextOptions 參數的公共構造函數。這是將 AddDbContext 的上下文配置傳遞到 DbContext 的方式。

    public class OpenDbContext : DbContext
    {
        public OpenDbContext(DbContextOptions options) : base(options)
        {
        }

        public DbSet<User> Users { get; set; }
        public DbSet<Score> Scores { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //另一種配置連接數據庫的方式
            //optionsBuilder.UseMySql("連接數據庫", ServerVersion.AutoDetect("連接數據庫字符串"));
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //屬性配置
            //modelBuilder.Entity<User>().Property(t => t.Account).IsRequired().HasMaxLength(20).HasComment("帳號");
            //種子數據設置
            //modelBuilder.Entity<User>().HasData(new User { Account="種子"});

            //使用下面的方法進行替換處理上面批量增加etc的操作
            modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

            base.OnModelCreating(modelBuilder);
        }
    }

然後將OpenDbContext通過構造函數注入的方式注入到應用程序的控制器或者其他服務中使用。

關於連接數據庫可以參考另一個文章： .Net之生成數據庫全流程

2. 操作數據庫

    context.Database.EnsureDeleted();//刪除數據庫，如果存在，如果沒有權限，則引發異常
    context.Database.EnsureCreated();//如果數據庫不存在，創建數據庫並初始化數據庫架構，如果存在任何錶，則不會初始化架構
    context.Database.Migrate();//根據遷移文件，遷移數據庫

3. 查詢操作

3.1 基礎查詢

db.Set<UserInfor>().ToList();

//查詢表達式
var account = (from u in _context.Users
                    where u.Id == id
                    select u.Account
                    ).ToList();

//查詢單個
_context.Movie.FirstOrDefaultAsync(m => m.ID == id);
_context.Movie.FindAsync(id); 
//查詢指定列
_context.Set<User>().AsNoTracking().Where(t=>t.Id=="11").Select(t => new { t.Account, t.PassWord }).FirstOrDefaultAsync();

// 預先加載查詢
var blogs = context.Blogs.Include(blog => blog.Posts).ToList();
// 包含多個層級的查詢
var blogs = context.Blogs.Include(blog => blog.Posts).ThenInclude(post => post.Author).ToList();

3.2 跟蹤和非跟蹤查詢

跟蹤行為決定了EFCore是否將有些實體的信息保存在其更改更跟蹤器中。如果已跟蹤某個實體，則該實體中檢測到的任何更改都會在SaveChanges()時候保存到數據庫，

不跟蹤沒有主鍵的實體類型。

# 跟蹤查詢
_context.Set<User>().ToListAsync();

# 非跟蹤查詢
_context.Set<User>().AsNoTracking().ToListAsync();

默認是跟蹤查詢

3.3 條件查詢

3.3.1 不支持異步方案

            Func<User, bool> express = x => true;       
            if (!string.IsNullOrWhiteSpace(dto.Data))
            {
                express = x => x.Mobile == dto.Data;
            }
            string userid = "";
            if (!string.IsNullOrWhiteSpace(userid))
            {
                express = x => x.UserId == userid;
            }
            var bbb = _dbContext.Set<User>().Where(express).FirstOrDefault();

3.3.2 支持異步方案

            Expression<Func<User, bool>> express = x => true;
            if (!string.IsNullOrWhiteSpace(dto.Data))
            {
                express = x => x.Mobile == dto.Data;
            }
            var bbb = await _dbContext.Set<User>().Where(express).ToListAsync();

3.4 原生SQL查詢

可使用 FromSqlRaw 擴展方法基於原始 SQL 查詢開始 LINQ 查詢。 FromSqlRaw 只能在直接位於 DbSet<> 上的查詢根上使用。

3.4.1 基本原生SQL查詢

var blogs = context.Blogs
    .FromSqlRaw("select * from user")
    .ToList();

// 執行存儲過程
var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

3.4.2 參數化查詢

3.4.2.1 SQL注入

首先我們編寫一個簡單的SQL注入示例，比如就注入我們根據ID查詢的語句，輸入ID為：ididid’ or ‘1’=’1

    var strSql = string.Format("select * from user where Id='{0}'", "ididid' or '1'='1");
    var query = await _context.Set<User>().FromSqlRaw(strSql).ToListAsync();
    Console.WriteLine(JsonConvert.SerializeObject(query));

生成語句

      select * from user where Id='ididid' or '1'='1'
[{"Account":"張三","PassWord":"123456","CreateTime":"2021-05-20T22:53:44.778101","IsValid":false,"Id":"1395392302788120576"},{"Account":"李四","PassWord":"123456","CreateTime":"2021-05-20T22:53:44.849376","IsValid":false,"Id":"1395392303090110464"},{"Account":"王五","PassWord":"123456","CreateTime":"2021-05-20T22:53:44.849425","IsValid":false,"Id":"1395392303090110467"}]

3.4.2.2 FromSqlRaw參數化

通過參數化查詢，防止SQL注入問題

    //sql語句參數化查詢，防止SQL注入    
    var strSql = "select * from user where Id=@id";
    var parameter = new MySqlParameter[] {
        new MySqlParameter("@id","1395392302788120576"),
    };
    var query = await _context.Set<User>().FromSqlRaw(strSql, parameter).ToListAsync();

或者

    var strSql = "select * from user where Id={0}";
    var query = await _context.Set<User>().FromSqlRaw(strSql, "1395392302788120576").ToListAsync();
    Console.WriteLine(JsonConvert.SerializeObject(query));
    
    // 生成SQL
    select * from user where Id=@p0
    [{"Account":"張三","PassWord":"123456","CreateTime":"2021-05-20T22:53:44.778101","IsValid":false,"Id":"1395392302788120576"}]

通過佔位符形式提供額外的參數，看上去類似於String.Format語法，但是提供的值包裝在DbParameter中。可以防止SQL注入

3.4.2.3 FromSqlInterpolated參數化

FromSqlInterpolated 類似於 FromSqlRaw，但你可以藉助它使用字符串內插語法。與 FromSqlRaw 一樣，FromSqlInterpolated 只能在查詢根上使用，並且都可以防止SQL注入。

    var query = await _context.Set<User>().FromSqlInterpolated($"select * from user where Id={"1395392302788120576"}").ToListAsync();
    Console.WriteLine(JsonConvert.SerializeObject(query));

生成SQL

      select * from user where Id=@p0
[{"Account":"張三","PassWord":"123456","CreateTime":"2021-05-20T22:53:44.778101","IsValid":false,"Id":"1395392302788120576"}]

3.4.3 限制

SQL查詢必須返回實體類型的所有屬性的數據。
結果集中的列明必須與屬性映射到的列名稱匹配。
SQL查詢不能包含關聯數據，但是，在許多情況下你可以在查詢後面緊跟着使用 Include 方法以返回關聯數據（請參閱包含關聯數據）。

參考文檔：//docs.microsoft.com/zh-cn/ef/core/querying/raw-sql

3.5 複雜查詢

數據如下：

用戶表(user)

用戶成績表(score)

描述：包含三個用戶，其中兩個用戶在成績表都有語文和數學的數據。

3.5.1 內連接

內連接：分為隱式內連接和顯式內連接(寫法不同，結果相同)

3.5.1.1 Linq查詢表達式

顯式內連接：join-in-on拼接

    var list = (from u in _context.Users
                join sc in _context.Scores on u.Id equals sc.UserId
                where sc.CourseName == "語文"
                select new
                {
                    u.Account,
                    u.PassWord,
                    sc.CourseName,
                    sc.Grade
                }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

記得引用：System.Linq 否則提示：未找到源類型「DbSet」的查詢模式的實現，未找到join

生成SQL

      SELECT `u`.`Account`, `u`.`PassWord`, `s`.`CourseName`, `s`.`Grade`
      FROM `user` AS `u`
      INNER JOIN `score` AS `s` ON `u`.`Id` = `s`.`UserId`
      WHERE `s`.`CourseName` = '語文'

結果

隱式內連接：多個from並聯拼接

    var list = (from u in _context.Users
                from sc in _context.Scores
                where u.Id == sc.UserId && sc.CourseName == "語文"
                select new
                {
                    u.Account,
                    u.PassWord,
                    sc.CourseName,
                    sc.Grade
                }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

生成SQL

      SELECT `u`.`Account`, `u`.`PassWord`, `s`.`CourseName`, `s`.`Grade`
      FROM `user` AS `u`
      CROSS JOIN `score` AS `s`
      WHERE (`u`.`Id` = `s`.`UserId`) AND (`s`.`CourseName` = '語文')

結果

3.5.1.2 Linq標準查詢運算符

    var list = _context.Users.Where(t => t.Account != null)
        .Join(_context.Scores.Where(sc => sc.CourseName == "語文"), u => u.Id, sc => sc.UserId, (u, sc) => new
        {
            u.Account,
            u.PassWord,
            sc.CourseName,
            sc.Grade
        }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

生成SQL

      # 不加查詢課程
        SELECT `u`.`Account`, `u`.`PassWord`, `s`.`CourseName`, `s`.`Grade`
      FROM `user` AS `u`
      INNER JOIN `score` AS `s` ON `u`.`Id` = `s`.`UserId`
      
      # 查詢課程
      SELECT `u`.`Account`, `u`.`PassWord`, `t`.`CourseName`, `t`.`Grade`
      FROM `user` AS `u`
      INNER JOIN (
          SELECT `s`.`CourseName`, `s`.`Grade`, `s`.`UserId`
          FROM `score` AS `s`
          WHERE `s`.`CourseName` = '語文'
      ) AS `t` ON `u`.`Id` = `t`.`UserId`

結果

3.5.2 外連接

外連接join後必須有into，然後可以加上XX.DefaultIfEmpty()，表示對於引用類型將返回null,而對於值類型則返回0。對於結構體類型，則會根據其成員類型將它們相應地初始化為null(引用類型)或0(值類型)，

如果僅需要統計右表的個數或者其它屬性，可以省略XX.DefaultIfEmpty, 但如果需要點出來右表的字段，則不能省。

3.5.2.1 linq實現

查詢所有用戶對應的班級,因為用戶和成績一對多，所以會出現多條數據

    var list = (from u in _context.Users
                join sc in _context.Scores on u.Id equals sc.UserId
                into ulist
                from sco in ulist.DefaultIfEmpty()
                where u.Account != null //這個條件只是展示如何添加條件
                select new
                {
                    UserId = u.Id,
                    Account = u.Account,
                    sco.CourseName
                }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

生成SQL

      SELECT `u`.`Id` AS `UserId`, `u`.`Account`, `s`.`CourseName`
      FROM `user` AS `u`
      LEFT JOIN `score` AS `s` ON `u`.`Id` = `s`.`UserId`

結果

如果要查詢成績，應該這麼寫，上面那個寫法會直接報錯， Nullable object must have a value

3.5.3 GroupJoin

GroupJoin操作符常應用於返回「主鍵對象-外鍵對象集合」形式的查詢，例如「用戶信息-此用戶下所有科目成績」

    var list = _context.Users.Where(t => t.Account != null)
        .GroupJoin(_context.Scores, u => u.Id, sc => sc.UserId, (u, sc) => new
        {
            u.Account,
            u.PassWord,
            Scores = sc
        }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

該代碼會提示錯誤，原因如：//docs.microsoft.com/zh-cn/ef/core/querying/client-eval

3.5.4 GrouBy

分組操作根據用戶分組，求科目數

    var list = (from sc in _context.Scores
                group sc by sc.UserId
                into g
                select new
                {
                    g.Key,
                    Count = g.Count()
                }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list));

    var list2 = _context.Scores.GroupBy(sc => sc.UserId).Select(t => new
    {
        t.Key,
        Count = t.Count()
    }).ToList();
    Console.WriteLine(JsonConvert.SerializeObject(list2));

生成SQL

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `s`.`UserId` AS `Key`, COUNT(*) AS `Count`
      FROM `score` AS `s`
      GROUP BY `s`.`UserId`
[{"Key":"1395392302788120576","Count":2},{"Key":"1395392303090110464","Count":2}]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `s`.`UserId` AS `Key`, COUNT(*) AS `Count`
      FROM `score` AS `s`
      GROUP BY `s`.`UserId`
[{"Key":"1395392302788120576","Count":2},{"Key":"1395392303090110464","Count":2}]

4. 添加

4.1 基礎添加

    _context.Movie.Add(movie);
    // or
    await _context.Movie.AddRangeAsync(movies)
    await _context.SaveChangesAsync();

4.2 已經設置自增鍵的插入

先關閉自增然後插入數據後再開啟自增

        db.Database.OpenConnection();
        db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [T_RoleInfor] ON");
        var r2 = new T_RoleInfor()
        {
            id = 123,
            roleName = "管理員",
            roleDescription = "我是管理員"
            };
        db.Add(r2);
        int count2 = db.SaveChanges();
        db.Database.ExecuteSqlCommand("SET ID   ENTITY_INSERT [T_RoleInfor] OFF");

4.3 通過SQL添加

    var strSql2 = "INSERT INTO `userinfo`(`Id`, `Account`, `PassWord`) VALUES (@id, @account, @password);";
    var parameter2 = new MySqlParameter[] {
        new MySqlParameter("@id","22"),
        new MySqlParameter("@account","2222"),
        new MySqlParameter("@password","22222")
        };
    var flg = db.Database.ExecuteSqlRaw(strSql2, parameter2);

5. 修改

    var  movie = await _context.Movie.FirstOrDefaultAsync(m => m.ID == id);
    movie.Name="李思";
    await _context.SaveChangesAsync();

6. 刪除

    var movie = await _context.Movie.FirstOrDefaultAsync(m => m.ID == id);
    _context.Movie.Remove(movie);
    await _context.SaveChangesAsync();

7. 參考文檔

官方例子：//docs.microsoft.com/zh-cn/ef/core/dbcontext-configuration/

微信公眾號

Tags: .NET C井 netcore

EFCore之增刪改查

1. 連接數據庫

2. 操作數據庫

3. 查詢操作

3.1 基礎查詢

3.2 跟蹤和非跟蹤查詢

3.3 條件查詢

3.3.1 不支持異步方案

3.3.2 支持異步方案

3.4 原生SQL查詢

3.4.1 基本原生SQL查詢

3.4.2 參數化查詢

3.4.2.1 SQL注入

3.4.2.2 FromSqlRaw參數化

3.4.2.3 FromSqlInterpolated參數化

3.4.3 限制

3.5 複雜查詢

3.5.1 內連接

3.5.1.1 Linq查詢表達式

顯式內連接：join-in-on拼接

隱式內連接：多個from並聯拼接

3.5.1.2 Linq標準查詢運算符

3.5.2 外連接

3.5.2.1 linq實現

3.5.3 GroupJoin

3.5.4 GrouBy

4. 添加

4.1 基礎添加

4.2 已經設置自增鍵的插入

4.3 通過SQL添加

5. 修改

6. 刪除

7. 參考文檔

微信公眾號

VirMach 便宜 VPS

QNews

EFCore之增刪改查

1. 連接數據庫

2. 操作數據庫

3. 查詢操作

3.1 基礎查詢

3.2 跟蹤和非跟蹤查詢

3.3 條件查詢

3.3.1 不支持異步方案

3.3.2 支持異步方案

3.4 原生SQL查詢

3.4.1 基本原生SQL查詢

3.4.2 參數化查詢

3.4.2.1 SQL注入

3.4.2.2 FromSqlRaw參數化

3.4.2.3 FromSqlInterpolated參數化

3.4.3 限制

3.5 複雜查詢

3.5.1 內連接

3.5.1.1 Linq查詢表達式

顯式內連接：join-in-on拼接

隱式內連接：多個from並聯拼接

3.5.1.2 Linq標準查詢運算符

3.5.2 外連接

3.5.2.1 linq實現

3.5.3 GroupJoin

3.5.4 GrouBy

4. 添加

4.1 基礎添加

4.2 已經設置自增鍵的插入

4.3 通過SQL添加

5. 修改

6. 刪除

7. 參考文檔

微信公眾號

分享此文：

Related Posts

每個男孩的機械夢「GitHub 熱點速覽 v.21.41」

女屌絲前端逆襲記（一）之淺談float

比特幣價格今年已腰斬：馬斯克放出這句「狠」話！

員工每天「帶薪拉屎」引老闆抱怨 不少網友回懟吐槽

VirMach 便宜 VPS

QNews

熱門搜尋

員工每天「帶薪拉屎」引老闆抱怨不少網友回懟吐槽