(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
1.复杂查询运算符
在生产场景中,我们经常用到linq运算符进行查询获取数据,现在我们就来了解下生产场景经常出现几种复杂查询运算符。
1.1联接(inner join)
借助linq join运算符,可根据每个源的键选择器连接两个数据源,并在键匹配时生成值的元组。
var query = from blog in _context.set<blog>() join post in _context.set<post>() on blog.blogid equals post.blogid select new { blog, post };
sql:
select [blog].[blogid], [blog].[createtime], [blog].[updatetime], [blog].[url], [post].[postid], [post].[blogid], [post].[content], [post].[title] from [blog] as [blog] inner join [post] as [post] on [blog].[blogid] = [post].[blogid]
sql server profiler:
1.2左联接(left join)
虽然left join不是linq运算符,但关系数据库具有常用于查询的left join的概念。linq查询中的特定模式提供与服务器上的left join相同的结果。
var query = from blog in _context.set<blog>() join post in _context.set<post>() on blog.blogid equals post.blogid into grouping from post in grouping.defaultifempty() select new { blog, post };
sql:
select [blog].[blogid], [blog].[createtime], [blog].[updatetime], [blog].[url], [post].[postid], [post].[blogid], [post].[content], [post].[title] from [blog] as [blog] left join [post] as [post] on [blog].[blogid] = [post].[blogid]
sql server profiler:
1.3分组(groupby)
linq groupby运算符创建igrouping<tkey, telement>类型的结果,其中tkey和telement可以是任意类型。此外,igrouping实现了ienumerable<telement>,这意味着可在分组后使用任意linq运算符来对其进行组合。
var query = from blog in _context.set<blog>() group blog by blog.url into g select new { g.key, count = g.count() };
sql:
select [blog].[url] as [key], count(*) as [count] from [blog] as [blog] group by [blog].[url]
sql server profiler:
分组的聚合运算符出现在where或orderby(或其他排序方式)linq运算符中。它在sql中将having子句用于where子句。
var query = from blog in _context.set<blog>() group blog by blog.url into g where g.count() > 0 orderby g.key select new { g.key, count = g.count() };
sql:
select [blog].[url] as [key], count(*) as [count] from [blog] as [blog] group by [blog].[url] having count(*) > 0 order by [key]
sql server profiler:
ef core支持的聚合运算符如下所示:
●avg
●count
●longcount
●max
●min
●sum
1.4selectmany
借助linq selectmany运算符,可为每个外部元素枚举集合选择器,并从每个数据源生成值的元组。
var query0 = from b in _context.set<blog>() from p in _context.set<post>() select new { b, p }; var query1 = from b in _context.set<blog>() from p in _context.set<post>().where(p => b.blogid == p.blogid).defaultifempty() select new { b, p }; var query2 = from b in _context.set<blog>() from p in _context.set<post>().select(p => b.url + "=>" + p.title).defaultifempty() select new { b, p };
sql:
select [b].[blogid], [b].[createtime], [b].[updatetime], [b].[url], [p].[postid], [p].[blogid], [p].[content], [p].[title] from [blog] as [b] cross join [post] as [p] select [b].[blogid], [b].[createtime], [b].[updatetime], [b].[url], [t0].[postid], [t0].[blogid], [t0].[content], [t0].[title] from [blog] as [b] cross apply ( select [t].[postid], [t].[blogid], [t].[content], [t].[title] from ( select null as [empty] ) as [empty] left join ( select [p].[postid], [p].[blogid], [p].[content], [p].[title] from [post] as [p] where [b].[blogid] = [p].[blogid] ) as [t] on 1 = 1 ) as [t0] select [b].[blogid], [b].[createtime], [b].[updatetime], [b].[url], [t0].[c] from [blog] as [b] cross apply ( select [t].[c] from ( select null as [empty] ) as [empty] left join ( select ([b].[url] + n'=>') + [p].[title] as [c] from [post] as [p] ) as [t] on 1 = 1 ) as [t0]
sql server profiler:
好了,这里就不多写关于linq其他示例了,如果需要了解的小伙伴们,可以移步“101个linq示例”这里了解。
2.原生sql查询
有一些复杂业务场景,使用linq查询可能会导致sql查询效率低下并不适用,那么这时候就需要到原生sql查询了。ef core为我们提供fromsql扩展方法基于原始sql查询。 fromsql只能在直接位于dbset<>上的查询根上使用。
var blogs = _context.blog.fromsql("select * from dbo.blog").tolist();
原生sql查询可用于执行存储过程。
var blogs = _context.blog .fromsql("execute dbo.getmostpopularblogs") .tolist();
2.1原始sql查询使用参数化
向原始sql查询引入任何用户提供的值时,必须注意防范sql注入攻击。除了验证确保此类值不包含无效字符,还要将值与sql文本参数化处理。
下面的示例通过在sql查询字符串中包含形参占位符并提供额外的实参,将单个形参传递到存储过程。虽然此语法可能看上去像string.format语法,但提供的值包装在dbparameter中,且生成的参数名称插入到指定{0}占位符的位置。
var url = "http://blogs.msdn.com/webdev"; var blogs = _context.blog .fromsql("execute dbo.getmostpopularblogforurl {0}", url) .tolist();
sql:
exec sp_executesql n'execute dbo.getmostpopularblogforurl @p0 ',n'@p0 nvarchar(4000)',@p0=n'http://blogs.msdn.com/webdev'
sql server profiler:
还可以构造dbparameter并将其作为参数值提供。由于使用了常规sql参数占位符而不是字符串占位符,因此可安全地使用fromsql:
var urlparams = new sqlparameter("url", "http://blogs.msdn.com/webdev"); var blogs = _context.blog .fromsql("execute dbo.getmostpopularblogforurl @url", urlparams) .tolist();
sql:
exec sp_executesql n'execute dbo.getmostpopularblogforurl @url ',n'@url nvarchar(28)',@url=n'http://blogs.msdn.com/webdev'
sql server profiler:
2.2使用linq编写sql
可使用linq运算符在初始的原始sql查询基础上进行组合。ef core将其视为子查询,并在数据库中对其进行组合。下面的示例使用原始sql查询,该查询从表值函数 (tvf) 中进行选择。然后,使用linq进行筛选和排序,从而对其进行组合。
var searchterm = "http://blogs.msdn.com/visualstudio"; var blogs = _context.blog .fromsql($"select * from dbo.blog") .where(b => b.url == searchterm) .include(c=>c.post) .orderbydescending(b => b.createtime) .tolist();
sql:
exec sp_executesql n'select [b].[blogid], [b].[createtime], [b].[updatetime], [b].[url] from ( select * from dbo.blog ) as [b] where [b].[url] = @__searchterm_1 order by [b].[createtime] desc, [b].[blogid]',n'@__searchterm_1 nvarchar(4000)',@__searchterm_1=n'http://blogs.msdn.com/visualstudio' exec sp_executesql n'select [b.post].[postid], [b.post].[blogid], [b.post].[content], [b.post].[title] from [post] as [b.post] inner join ( select [b0].[blogid], [b0].[createtime] from ( select * from dbo.blog ) as [b0] where [b0].[url] = @__searchterm_1 ) as [t] on [b.post].[blogid] = [t].[blogid] order by [t].[createtime] desc, [t].[blogid]',n'@__searchterm_1 nvarchar(4000)',@__searchterm_1=n'http://blogs.msdn.com/visualstudio'
sql server profiler:
3.异步查询
当在数据库中执行查询时,异步查询可避免阻止线程。异步查询对于在客户端应用程序中保持响应式ui非常重要。 异步查询还可以增加web应用程序中的吞吐量,即通过释放线程,以处理其他web应用程序中的请求。
public async task<iactionresult> index() { var id1 = thread.currentthread.managedthreadid.tostring(); var blogs = await _context.blog.tolistasync(); var id2 = thread.currentthread.managedthreadid.tostring(); return view(blogs); }
当我们运行以上代码时候,通过在关键字await上下文加入两段获取线程id代码,我们会看到如下结果:
看到两段线程代码输出id结果没有?从上图可以观察到,当我们在进入某个视图或者方法时候,执行到await某一个方法,当前线程不会一直等待下去,会立马回收到线程池,供其他地方调用!当该await方法返回数据时候,才从线程池调用空闲线程执行await方法下文余下的步骤。所以ui界面才不会进入假死状态。
参考文献:
原生sql查询
上一篇: Sentinel 集群安装 step by step
下一篇: 从写一个批处理文件到远控计算机