EF Core执行原生SQL语句

目录

EFCore执行非查询原生SQL语句

为什么要写原生SQL语句

执行非查询SQL语句

有SQL注入漏洞

ExecuteSqlInterpolatedAsync 

其他方法

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

FromSqlInterpolated

局限性

执行任意原生SQL查询语句

什么时候用ADO.NET

执行任意SQL

Dapper

总结

---

EFCore执行非查询原生SQL语句

为什么要写原生SQL语句

1. 尽管EF Core已经非常强大，但是仍然存在着无法被写成标准EF Core调用方法的SQL语句，少数情况下仍然需要写原生SQL。
2. 可能无法跨数据库。
3. 三种情况：非查询语句、实体查询、任意SQL查询。

执行非查询SQL语句

使用dbCtx.Database. ExecuteSqlInterpolated ()；

dbCtx.Database. ExecuteSqlInterpolatedAsync()方法来执行原生的非查询SQL语句。

static async Task Main(string[] args)
{using (MyDbContext ctx = new MyDbContext()){string strName = "小刘";await ctx.Database.ExecuteSqlInterpolatedAsync(@$"insert into T_Books(Name,Author,Price)select Name,{strName},Pricefrom T_Books where Price > 20");}
}

有SQL注入漏洞

字符串内插的方式会不会有SQL注入攻击漏洞吗？查看一下执行的SQL语句吧。

insert into T_Books(Name,Author,Price)
select Name,@p0,Price
from T_Books where Price > 20

ExecuteSqlInterpolatedAsync 

字符串内插如果赋值给string变量，就是字符串拼接；字符串内插如果赋值给FormattableString变量，编译器就会构造FormattableString 对象。打印FormattableString的成员试试看。

static async Task Main(string[] args)
{using (MyDbContext ctx = new MyDbContext()){string strName = "小刘";FormattableString sql = (@$"insert into T_Books(Name,Author,Price)select Name,{strName},Pricefrom T_Books where Price > 20");Console.WriteLine("Format:" + sql.Format);Console.WriteLine("参数：" + string.Join(",", sql.GetArguments()));}
}

ExecuteSqlInterpolatedAsync()的参数是FormattableString类型。因此ExecuteSqlInterpolatedAsync会进行参数化SQL的处理。

其他方法

除了ExecuteSqlInterpolated ()、ExecuteSqlInterpolatedAsync() ，还有ExecuteSqlRaw()、ExecuteSqlRawAsync() 也可以执行原生SQL语句，但需要开发人员自己处理查询参数，因此不推荐使用。

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

FromSqlInterpolated

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

static async Task Main(string[] args)
{using (MyDbContext ctx = new MyDbContext()){string NamePattern = "%计算%";var queryable = ctx.Books.FromSqlInterpolated(@$"select * from T_Books where Name Like {NamePattern} order by newid()");foreach (var item in queryable){Console.WriteLine(item.Id + item.Name);}}
}

select * from T_Books where Name Like @p0 order by newid()

FromSqlInterpolated()方法的返回值是IQueryable类型的，因此我们可以在实际执行IQueryable之前，对IQueryable进行进一步的处理。

把只能用原生SQL语句写的逻辑用FromSqlInterpolated()去执行，然后把分页、分组、二次过滤、排序、Include等其他逻辑尽可能仍然使用EF Core的标准操作去实现。

局限性

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

执行任意原生SQL查询语句

什么时候用ADO.NET

FromSqlInterpolated()只能单表查询，但是在实现报表查询等的时候，SQL语句通常是非常复杂的，不仅要多表Join，而且返回的查询结果一般也都不会和一个实体类完整对应。因此需要一种执行任意SQL查询语句的机制。

EF Core中允许把视图或存储过程映射为实体，因此可以把复杂的查询语句写成视图或存储过程，然后再声明对应的实体类，并且在DbContext中配置对应的DbSet。

不推荐写存储过程；项目复杂查询很多，导致：视图太多；非实体的DbSet；DbSet膨胀。

执行任意SQL

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

Dapper

推荐用Dapper等框架执行原生复杂查询SQL。

EF Core和Dapper并不是对立，可以同时使用，EF Core简单方便

var items = ctx.Database.GetDbConnection().Query<GroupArticleByPrice>("select Price,Count(*) PCount from T_Books group by price");
foreach (var item in items)
{Console.WriteLine($"Price{item.Price},Count{item.PCount}");
}

总结

一般Linq操作就够了，尽量不用写原生SQL；

1. 非查询SQL用ExecuteSqlInterpolated () ；
2. 针对实体的SQL查询用FromSqlInterpolated()。
3. 复杂SQL查询用ADO.NET的方式或者Dapper等。