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
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方法以返回關聯資料（請參閱包含關聯資料）。
參考文件：https://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));
該程式碼會提示錯誤，原因如：https://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. 參考文件
官方例子：https://docs.microsoft.com/zh-cn/ef/core/dbcontext-configuration/
微信公眾號
