欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)

程序员文章站 2022-04-19 18:39:18
1.复杂查询运算符 在生产场景中,我们经常用到LINQ运算符进行查询获取数据,现在我们就来了解下生产场景经常出现几种复杂查询运算符。 1.1联接(INNER JOIN) 借助LINQ Join运算符,可根据每个源的键选择器连接两个数据源,并在键匹配时生成值的元组。 SQL: SQL Server P ......

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:
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)

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:
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)

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:

(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
分组的聚合运算符出现在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:
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
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:
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)

(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
好了,这里就不多写关于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:

(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
还可以构造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:

(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)

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:
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)

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代码,我们会看到如下结果:
(25)ASP.NET Core EF查询(复杂查询运算符、原生SQL查询、异步查询)
看到两段线程代码输出id结果没有?从上图可以观察到,当我们在进入某个视图或者方法时候,执行到await某一个方法,当前线程不会一直等待下去,会立马回收到线程池,供其他地方调用!当该await方法返回数据时候,才从线程池调用空闲线程执行await方法下文余下的步骤。所以ui界面才不会进入假死状态。

参考文献:

原生sql查询