EF Core 中原生SQL、存储过程、视图的使用

包括EF Core中原型Sql的执行，包括存储过程和视图数据处理方法，同时包括参数化执行sql语句的注意事项。

原生Sql查询

原生sql查询使用如下两个方法进行，查询的结构只能映射到dbset关联的对象类型

DBSet.FromSqlRaw()
DBSet.FromSqlInterpolated()

可以使用部分linq扩展方法

.FromSqlRaw("select * from authors").FirstOrDefault(a=>a.Id==3)
.FromSqlRaw("select * from authors").OrderBy(a=>a.LastName)
.FromSqlRaw("select * from authors").Include(a=>a.Books)
.FromSqlRaw("select * from authors").AsNoTracking()

Find方法不受支持

避免Sql注入

参数化查询

.FromSqlRaw("select * fro mauthors where lastnmae like '{0}%'",lastnameStart).TagWith("Fromatted_Safe").ToList()
.FromSqlRaw($"select * fro mauthors where lastnmae like '{lastnameStart}%'").TagWith("Fromatted_Safe").ToList()
.FromSqlInterpolated($"select * fro mauthors where lastnmae like '{lastnameStart}%'").TagWith("Interpolated_Safe").ToList()

不安全的查询

string sql = $"select * fro mauthors where lastnmae like '{lastnameStart}%'";
.FromSqlRaw(sql).TagWith("Interpolated_Unsafe").ToList()

.FromSqlRaw($"select * fro mauthors where lastnmae like '{lastnameStart}%'").TagWith("Interpolated_Unsafe").ToList()

存储过程使用

EXEC thesproc param1,param2,param3

添加存储过程

add-migration AddStoredProc

public partial class AddStoredProc : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
        CREATE PROCEDURE dbo.AuthorsPublishedinYearRange
        @yearstart int,
        @yearend int
        AS
        select * from authors as a
        left join books as b  on a.authorid = b.authorId
        where Year(b.PublishDate) >=@yearstart and Year(b.PublishDate) <=@yearend
        ");
    }
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(" drop procedure AuthorsPublishedinYearRange")
    }
}

执行存储过程

DBSet.FromSqlRaw("AuthorsPublishedinYearRange {0},{1}",1999,2010);
DBSet.FromSqlInterpolated($"AuthorsPublishedinYearRange {start},{end}");

不能使用的方法：Include

视图的使用方法

//示例，视图返回如下数据
public class AuthorByArtist
{
    public string Artist {get;set;}
    public string? Author {get;set;}
}
//1、定义Dbset
public virual DbSet<AuthorByArtist> AuthorByArtist {get;set;}
//2、设置Entity
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<AuthorByArtist>().HasNoKey()
    .ToView("AuthorByArtist");
    .....
}

没有主键，不能使用Find方法查询数据

数据库级别执行：Non-Query Raw SQL

_context.Database.ExecuteSQLRaw("update author set a.name = '{0}",newname);
_context.Database.ExecuteSQLRawAsync("update author set a.name = {0}",newname);

_context.Database.ExecuteSQLInterpolated(("update author set a.name = {newname}  where authorid = {id}");
_context.Database.ExecuteSQLInterpolatedAsync("update author set a.name = {newname}  where authorid = {id}");
//执行存储过程
_context.Database.ExecuteSQLRaw("DeleteCover {0}", coverId);