循序漸進學.Net Core Web Api開發系列【9】：常用的資料庫操作

系列目錄

循序漸進學.Net Core Web Api開發系列目錄

 本系列涉及到的源碼下載地址：https://github.com/seabluescn/Blog_WebApi

一、概述

本篇描述一些常用的資料庫操作，包括：條件查詢、排序、分頁、事務等基本資料庫操作。試驗的資料庫為MySQL。

二、條件查詢
1、查詢所有記錄

List<Article> articles = _context.Articles.ToList<Article>();

2、根據主鍵進行查詢

Article article = _context.Articles.Find(id);

3、根據非主鍵信息欄位進行查詢

List<Product> products = _context.Products

　　.Where(p => p.Name == name)

　　.ToList<Product>();

 如果找不到會返回Null，但不報異常。

4、查詢一條記錄

Article articles = _context.Articles
    .Single(article=>article.Title==title);

此時應要求該欄位做唯一性約束，該方法期待必須返回一條記錄，0條和多條都會報異常。

而First方法會取眾多記錄中的第一條，如果找不到會報異常，但有多條符合條件就取一條不報異常。

Article articles = _context.Articles
    .First(article=>article.Title.Contains(title));

5、模糊查詢
模糊查詢有兩種方法：

List<Article> articles = _context.Articles
　　.Where(article => article.Title.Contains(title))
　　.ToList();

List<Article> articles = _context.Articles
　　.Where(article => EF.Functions.Like(article.Title,$"{title}%"))
　　.ToList();

Like方法和String的Contains方法都能實現模糊查詢，主要區別在於：EF.Functions.Like()方法支持通配符，而StartsWith、Contains和EndsWith方法是不支持通配符的，比較下麵兩個方法，第1條語句可以實現預期效果，但第2條語句不行。

（1） EF.Functions.Like(article.Title,“%[a-z]%”)

（2） article.Title.Contains("[a-z]")

提示：如果需要查看實際執行的SQL語句，修改application.json中的日誌級別就可以了。

三、排序

List<Article> articles = _context
    .Articles.OrderBy(article=>article.Title)
    .ToList<Article>();

List<Article> articles = _context
　　.Articles.OrderByDescending(article=>article.Title)
　　.ToList<Article>();

四、 預先載入
可以使用Include方法，以便指定要包含在查詢結果的相關的數據。

Article article = _context.Articles
　　.Include(a => a.author)
　　.First();

上述代碼中article的author屬性不再為null。

載入多項

var blogs = context.Blogs
　　.Include(blog => blog.Posts)
　　.Include(blog => blog.Owner)
　　.ToList();

 多級預載入：

List<Column> columns = _context.Columns
　　.Include(column => column.Articles)
　　.ThenInclude(article=> article.author)
　　.ToList<Column>();

五、非跟蹤查詢（No-tracking queries）
如果僅僅是只讀查詢，採用非跟蹤查詢會提供查詢的性能。

List<Article> articles = _context.Articles
　　.AsNoTracking()
　　.ToList<Article>();

六、原始的 SQL 查詢
可以使用FromSql擴展方法，實現基於原始的 SQL 查詢的 LINQ 查詢。

List<Article> articles = _context.Articles
　　.FromSql("select * from cms_article")
　　.ToList();

 也可以使用原始的 SQL 查詢來執行存儲的過程。

var blogs = context.Blogs
　　.FromSql("EXECUTE dbo.GetMostPopularBlogs")
　　.ToList();

 或者實現帶參數的查詢

var sql = $"select * from cms_article where title like '%{titlewithsql}%'";
List<Article> articles = _context.Articles
　　.FromSql(sql)
　　.ToList();

 預載入Include、Where 與OrderBy都可以使用

List<Article> articles = _context.Articles
　　.FromSql(sql)
　　.Include(a=>a.author)
　　.ToList();

var searchTerm = ".NET";
var blogs = context.Blogs
　　.FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
　　.Where(b => b.Rating > 3)
　　.OrderByDescending(b => b.Rating)
　　.ToList();

註意：如果使用字元串串聯來動態生成的查詢字元串的任何部分，則你將負責驗證任何輸入，以防止受到 SQL 註入式攻擊

七、分頁

int pageSize = 10;
int pageNumber = 2;
List<Article> articles = _context.Articles
　　.AsNoTracking()
　　.Skip(pageSize* pageNumber)
　　.Take(pageSize)
　　.ToList<Article>();

八、修改數據
1、添加數據
使用DbSet.Add方法將添加的實體類的新實例。

var blog = new Blog { Url = "http://sample.com" };
context.Blogs.Add(blog);
context.SaveChanges();

2、更新數據

var blog = context.Blogs.Find("xx");
blog.Url = "http://sample.com/blog";
context.SaveChanges();

3、刪除數據

var blog = context.Blogs.First();
context.Blogs.Remove(blog);
context.SaveChanges();

4、在單個 SaveChanges 的多個操作 

context.Blogs.Add(new Blog { Url = "http://sample.com/blog_one" });
context.Blogs.Add(new Blog { Url = "http://sample.com/blog_two" });

// update
var firstBlog = context.Blogs.First();
firstBlog.Url = "";

// remove
var lastBlog = context.Blogs.Last();
context.Blogs.Remove(lastBlog);

context.SaveChanges();

SaveChanges是事務性的，以上代碼不需要額外增加任何事務性代碼。

九、 使用連接池

String connStr = Configuration.GetConnectionString("MySQLConnection");
services.AddDbContextPool<SalesContext>(builder=> builder.UseMySQL(connStr));

 建議儘量使用連接池方式連接資料庫。