包括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);
标签:Core,set,like,FromSqlRaw,EF,视图,where,select
From: https://www.cnblogs.com/LifelongLearning/p/17362755.html