10、Entity Framework Core 3.1入門教程-執行原生SQL

hllive發表於2020-08-26
FrameworkSQL

本文章是根據 微軟MVP solenovex（楊旭）老師的視訊教程編寫而來，再加上自己的一些理解。
視訊教程地址：https://www.bilibili.com/video/BV1xa4y1v7rR
GitHub原始碼：https://github.com/hllive/LearnEFCore3.1

無主鍵的Entity

  • .NetCore3.1允許無主鍵的Entity或Model
  • 它們不會被追蹤，相當於是隻讀的
  • 對映到沒有主鍵的Table或View

如何在EFCore中使用原生SQL語句或執行儲存過程以及檢視
首先執行兩個SQL指令碼，一個檢視和一個儲存過程

CREATE VIEW ViewPlayerClub
AS
    SELECT p.Id[PlayerId],p.Name[PlayerName],c.Name[ClubName],c.City[ClubCity]
    FROM Players p INNER JOIN Clubs c ON p.ClubId=c.Id
GO

CREATE PROCEDURE RemoveGamePlayer
    @PlayerId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM GamePlayer WHERE PlayerId=@PlayerId
END
GO

根據檢視ViewPlayerClub建立一個類

public class PlayerClub
{
    public Guid PlayerId { get; set; }
    public string PlayerName { get; set; }
    public string ClubName { get; set; }
    public string ClubCity { get; set; }
}

再把這個類新增到DBContext中

public DbSet<PlayerClub> PlayerClubs { get; set; }

DbSet使用這個沒有主鍵的類是不行的，怎麼才能使用無主鍵的類呢？
可以在OnModelCreating()方法裡設定一下

modelBuilder.Entity<PlayerClub>()
    .HasNoKey()//設定沒有主鍵
    .ToView("ViewPlayerClub");//如果不寫這句，當遷移的時候還會建立一個PlayerClub的Table，應該把這個類對映到一個檢視上

針對這種沒有主鍵的model查詢出來都是無法追蹤的

例子

[HttpGet("PlayerClub")]
public IActionResult GetViewPlayerClub()
{
    var playerClub = _dbContext.PlayerClubs.Where(px => px.ClubCity.Contains("貴州")).ToList();
    return Ok(playerClub);
}

生成的SQL語句

SELECT [v].[ClubCity], [v].[ClubName], [v].[PlayerId], [v].[PlayerName]
FROM [ViewPlayerClub] AS [v]
WHERE CHARINDEX(N'貴州', [v].[ClubCity]) > 0

如果使用_dbContext.PlayerClubs.Find(Guid id)在編譯的時候不會出錯，但執行肯定會報錯，因為Find()裡的引數是主鍵

原生SQL查詢

執行原生SQL查詢有兩種方法

  • .FromSqlRaw("SELECT *...") 直接寫SQL語句
  • .FromSqlRawAsync("SELECT *...")
  • .FromSqlInterpolated("$SELECT *...WHERE x={var}") SQL語句需要使用引數
  • .FromSqlInterpolatedAsync("$SELECT *...WHERE x={var}")

FromSqlRaw和FromSqlInterpolated是DbSet()的方法，所以只能針對DbSet方法執行，需要在DbSet()後使用FromSqlRaw

使用FromSqlRaw()方法

[HttpGet("SqlTest")]
public IActionResult GetSqlTest1()
{
    var leagues = _dbContext.Leagues.FromSqlRaw("SELECT * FROM Leagues").ToList();
    return Ok(leagues);
}

生成的SQL語句就是FromSqlRaw()方法裡的語句

使用FromSqlInterpolated()方法

[HttpGet("SqlTest1")]
public IActionResult GetSqlTest1([FromQuery] string name)
{
    //使用帶引數的FromSqlInterpolated
    var leagues = _dbContext.Leagues
        .FromSqlInterpolated($"SELECT * FROM Leagues WHERE Name LIKE N'%{name}%'")
        .ToList();
    return Ok(leagues);
}

生成的SQL語句就是FromSqlInterpolated()方法的語句，但是會使用SQL引數作為查詢值

exec sp_executesql N'SELECT * FROM Leagues WHERE Name LIKE N''%@p0%''
',N'@p0 nvarchar(4000)',@p0=N'足球'

原生SQL查詢的要求

  • 必須返回Entity型別的所有屬性，一般都是SELECT *
  • 欄位名和Entity的屬性名必須匹配
  • 無法包含關聯的資料，不能寫JOIN語句
  • 只能查詢已知的Entity，也就是Context中設定好的或間接能追蹤到的資料

執行非查詢類SQL

  • Context.Database.ExecuteSqlRaw()
  • Context.Database.ExecuteSqlRawAsync()
  • Context.Database.ExecuteSqlInterpolated()
  • Context.Database.ExecuteSqlInterpolatedAsync()
  • 無法用於查詢
  • 只能返回影響的行數
[HttpGet("SqlTest2")]
public IActionResult GetSqlTest2()
{
    //使用ExecuteSqlRaw
    var count = _dbContext.Database.ExecuteSqlRaw("EXEC [dbo].[RemoveGamePlayer] {0}", new Guid(""));
    //使用ExecuteSqlInterpolated
    var counts = _dbContext.Database.ExecuteSqlInterpolated($"EXEC [dbo].[RemoveGamePlayer] {new Guid("")}");
    return Ok(new { count, counts });
}

生成的SQL語句兩個都一樣

部落格文章可以轉載，但不可以宣告為原創

相關文章