执行原生非查询SQL语句

可以自己写数据库语句而非使用linq进行转换。
由于数据库语法的差异，所以可能无法跨越数据库使用。
一般用于以下情况：非查询语句、实体查询、任意SQL查询。

简单使用

使用dbCtx.Database.ExecuteSqlInterpolated()、dbCtx.Database.ExecuteSqlInterpolatedAsync()执行原生的非查询SQL语句。

1
2
3
4
5
6
static async Task Main(string[] args) {
           using(var ctx  = new MyDbContext()) {
               await ctx.Database.ExecuteSqlInterpolatedAsync(@$"INSERT INTO t_articles (Title, Message) 
                VALUES ('WBG对战T1', 'WBG与T1将于11月19日下午四点进行比赛');");
            }
         }

SQL注入漏洞

我们必须使用插值语法作为dbCtx.Database.ExecuteSqlInterpolated()方法的参数，因此我们可以写出以下代码。

1
2
3
4
5
6
7
8
static async Task Main(string[] args) {
            string title = "WBG对战T1";
            string message = "WBG与T1将于11月19日下午四点进行比赛";
           using (var ctx  = new MyDbContext()) {
               await ctx.Database.ExecuteSqlInterpolatedAsync(@$"INSERT INTO t_articles (Title, Message) 
                VALUES ({title}, {message});");
            }
         }

上面的代码生成的SQL语句不会出现SQL注入，其生成语句如下

1
INSERT INTO t_articles (Title, Message) VALUES (@p0, @p1);

原理

1、字符串内插如果赋值给string变量就是字符串拼接；如果赋值给FormattableString变量，编译器就会构造FormattableString对象。该对象有很多方法可以通过F12键进行查看。
2、ExecuteSqlInterpolatedAsync()的参数是FormattableString类型。因此ExecuteSqlInterpolatedAsync()会进行参数化SQL的处理。
3、除了ExecuteSqlInterpolated()、ExecuteSqlInterpolatedAsync()以外还有ExecuteSqlRaw()、ExecuteSqlRawAsync()也可以执行原生SQL语句，但需要开发人员自己处理查询参数等了，因此不推荐使用。

执行与实体相关原生SQL查询语句

简单使用

1、如果要执行的原生SQL是一个查询语句，并且查询的结果也能对应一个实体，就可以调用对应实体的DbSet的FromSqlInterpolated()方法来执行一个查询SQL语句，同样使用字符串内插来传递参数。

1
2
3
4
5
6
7
8
9
static async Task Main(string[] args) {
            string str = "%T1%";
            FormattableString fms = @$"select * from t_articles where Title like {str};";
            using(var ctx = new MyDbContext()) {
                var res = ctx.Articles.FromSqlInterpolated(fms);
                foreach(var item in res) {
                    await Console.Out.WriteLineAsync(item.Message);
                }
            }

2、FromSqlInterpolated()方法的返回值是IQueryable类型的，因此可以在实际执行IQueryable之前，对IQueryable类型进行进一步处理。
3、可以把只能用原生SQL语句写的逻辑用FromSqlInterpolated()去执行，然后把分页、分组、二次过滤、排序、Include等其他逻辑尽可能仍然使用EF Core的标准去实现。

局限

SQL必须返回实体类型对应数据库表的所有列；
结果集中的列名必须与属性映射到的列名称匹配；
只能单表查询，不能使用Join语句进行关联查询。但是可以在查询后面使用Include()来进行关联数据的获取。

执行任意原生SQL查询语句

1、FromSqlInterpolated()只能单表查询，但是在实现报表查询等操作的时候，SQL语句通常是非常复杂的，不仅要多表Join而且返回的结果一般也都不会和一个实体类完整对应。因此需要一种执行任意SQL查询语句的机制。
2、EF Core中允许把视图或存储过程映射为实体，因此可以把复杂的查询语句写成视图或存储过程，然后再声明对应的实体类，并且在DbContext中配置对应的DbSet。
3、不推荐写存储过程；项目复杂查询很对，导致视图太对、DbSet膨胀。

简单使用

dbSet.Database.GetDbConnection()获得ADO.NET Core的数据库连接对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
static async Task Main(string[] args) {
            using(var ctx = new MyDbContext()) {
                DbConnection conn = ctx.Database.GetDbConnection();//拿到Context对应的底层Connection对象
                if(conn.State !=System.Data.ConnectionState.Open ) { 
                    await conn.OpenAsync();
                }
                using(var cmd =  conn.CreateCommand()) {
                    cmd.CommandText="select Price,Count(*) from T_Articles group by Price";
                    using (var reader = await cmd.ExecuteReaderAsync()) {
                        while(await reader.ReadAsync()) {
                            double price = reader.GetDouble(0);
                            int count = reader.GetInt32(1);
                            await Console.Out.WriteLineAsync($"{price}:{count}");
                        }
                    }
                }
            }
         }

不建议直接使用ADO.NET Core，可以使用Dapper写任意原生的SQL语句。

总结

一般Linq操作就够了，尽量不写原生SQL；
1、非查询SQL用ExecuteSqlInterpolated()；
2、针对实体的SQL查询用FromSqlInterpolated()；
3、复杂SQL查询用ADO.NET的方式或者Dapper等；