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);

@诸葛卧龙