（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查询