10、Entity Framework Core 3.1入门教程-执行原生SQL

无主键的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，应该把这个类映射到一个视图上

例子

[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()方法

[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语句两个都一样