Net6 EfCore 执行原生SQL
十年河东,十年河西,莫欺少年穷
学无止境,精益求精
1、适用于: --insert update delete
1.1、内插值方式:FormattableString--该方式不存在注入攻击风险,放心用
using (wechatDbContext context = new wechatDbContext()) { // --insert update delete //内插值方式:FormattableString--该方式不存在注入攻击风险,放心用 int id = 1; string name = "陈"; context.Database.ExecuteSqlInterpolated(@$"insert into tableName values({id},{name},'男')"); }
1.2、ExecuteSqlRaw 参数化模式
using (wechatDbContext context = new wechatDbContext()) { int id = 1; string name = "陈"; // var paras = new { id, name }; context.Database.ExecuteSqlRaw("insert into tableName values(@id,@name)", paras); // }
两种方式 建议使用内插值方式
2、适用于Select
2.1、和DataBase实体相关的查询语句【只能单表查询】
-- --返回 IqueryAble<T> 不会立即执行 需要toList() 等 才会执行 sql语句中不能加orderBy
using (wechatDbContext context = new wechatDbContext()) { int id = 1; string name = "陈"; //和DataBase实体相关的查询语句【只能单表查询】 --返回 IqueryAble<T> 不会立即执行 需要toList() 等 才会执行 sql语句中不能加orderBy IQueryable<Article> articles = context.Articles.FromSqlInterpolated($"select * from T_Articles where id={id}"); string likeName = "%中%"; IQueryable<Article> articles2 = context.Articles.FromSqlInterpolated($"select * from T_Articles where title like {likeName}"); articles2.Where(A => A.id == 1); articles2.OrderBy(A => A.id).ThenBy(A => A.title); var lst = articles2.ToList(); foreach(var item in lst) { } }
2.2、Ado.NEt方式【一夜回到解放前】
--支持自定义SQL,多表Join查询,但该方式使用的是ADO.NET的方式,建议使用第三方,例如Dapper、SqlSugar等
using (wechatDbContext context = new wechatDbContext()) { int id = 1; string name = "陈"; // //任意原生SQL语句【一夜回到解放前,Ado.NEt方式】 结合SqlSugar DbConnection conn = context.Database.GetDbConnection(); if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } using (var cmd = conn.CreateCommand()) { cmd.CommandText = "select title,count(1) as titleCount from T_Articles group by title"; using (var reader= await cmd.ExecuteReaderAsync()) { string title = reader.GetString(0); int titleCount = reader.GetInt32(1); Console.WriteLine($"{title}:{titleCount}"); } } // 全局过滤 context.Articles.IgnoreQueryFilters().Where(A => A.Isdeleted == true); }
3、临时笔记:全局过滤【用于软删除】
public class ArticleConfig : IEntityTypeConfiguration<Article> { public void Configure(EntityTypeBuilder<Article> builder) { builder.ToTable("T_Articles"); builder.HasQueryFilter(A => A.Isdeleted == false); builder.HasIndex(A => A.pubdate); } }
按照上述全局过滤后,所有的EFCORE LINQ表达式都会加上 isdeleted==false
排除全局过滤【例如查询回收站数据】
context.Articles.IgnoreQueryFilters().Where(A => A.Isdeleted == true);
@诸葛卧龙