【EntityFramework 6.1.3】个人理解与问题记录（2）
前言
才看完一季动漫，完结撒花，末将于禁，原为曹家世代赴汤蹈火！想必看过的都会知道这个，等一下要不吐槽一下翻拍的真人版，○( ＾皿＾)っHiahia…，好了快醒醒改办正事儿了，好的，我们接着上一篇文章 http://www.cnblogs.com/DjlNet/p/7220720.html 的问题继续开始我们今天晚上的解读！
加戏篇(EF并发)
关于EF中的并发也是开发当中的需要注意的一点，上文中遗漏了这点且感谢园友提醒，这不赶紧来补上，然而博主这里并不会啪啦啪啦的直接去复制粘贴一坨坨的代码和文字上来，所以这里备注一下中英文相关的传送门即可，望大大们海涵。
关于EF的并发中文的地址(@tkb至简博文): http://www.cnblogs.com/farb/p/ConcurrencyAndTransctionManagement.html关于文中的前半段则是对并发的理解和在EF中对于并发的表现和解决方案，我这里就不再赘述了，文中有文字和示例描述问题的产生和解决过程。纯属个人备份记录而已，知道的园友们，可以直接跳过，O(∩_∩)O哈哈~
其实微软官方文档亦有类似文档，参考地址(英文好的可以直接实用，当然博主看英文略显吃力，得加油拉凸(艹皿艹 )): https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application ，其中MSDN的的地址: https://msdn.microsoft.com/en-us/library/jj592904(v=vs.113).aspx 该文中含有许多代码示例可以参考，可以结合当前业务开箱实战即用。
主角篇
可能下面篇幅略长图片略多，流量党慎重，请战斗人员带好护目镜做好高能预警准备！非战斗人员请尽快撤离，好中二....o(≧v≦)o~~
6、EF对复杂的查询表达式解析能力如何？
这里有三点的说明一下:1、使用数据库限定为Sql Server 2012及其以上，其他可能略微有些差异看各自的数据库驱动中的实现及其对 ExpressionTree树 是如何解析了的 2、仅限定于查询语句的解析其中可能包含LINQ基本方法翻译SQL的对照参考关系 3、特别指出当前是没有导航属性的情况，也就是没有正真的外键，这里可能有人有疑问，为何？基于现实特殊情况而言，对于数据来说插入数据很频繁且对外键的数据检查不是很强烈，把外键的逻辑控制交给应用程序也是一种提高数据库性能的做法，当然应用层要做好外键关系，注意：并不是表达外键无用之意，这里就不赘述其作用了。
前奏： 相信大家部分些许人觉得EF有时候总是给人略显神秘的感觉，时常再想与自己手写的SQ相差多远，到底它能够翻译的极致与其中比绕的坑程度在哪里？对于大多数时候亦能够比较准确的翻译为标准SQL，当然我们自然会注意到当查询逻辑略显复杂的时候，通常情况下我们自然而然会选择【查询表达式】当然混用也是可以的对于运行时来说都是一致的，当然并没有对lambda喜欢运用做查询的有敌意哈，其实简单的查询lambda更方便或者拼接where条件的时候贼好使，谁用谁知道。还有，关于 linq 的查询表达式关键字会尽数通过编译器翻译为lambda表达式和linq的扩展方法调用，所以在CLR代码运行层面，查询表达式是透明，所以查询表达式可以看做是IDE和C#给我们直观写代码的展示方便拉。
这里为了展示出稍微复杂的查询，复杂的查询可能包含一下操作：(内外)连接查询、分组、聚合、排序、分页、Case when、DbFuncations、SqlFunctions、数据库字符串处理函数调用(like..etc)、In查询、Exists判断、Distinct去重.....等等，这里先YY一个数据库Database，及其当中的Tables，然后填充一些数据，当然这里会展示出表的数据之间的大致关系这样，在写出代码示例时候能确保实验的真实和可靠性。如下图所示（请不在意数据库表设计细节，那不是重点(/≧▽≦)/）：
预热： 首先这里我们先回顾一下关于LINQ当中一些基本方法的翻译SQL，虽然有些我们都已经熟知，但是这里我还是得啰嗦一下，能够大致罗列出来一个基本对照表，其实一直想做一个对照表，原先的LINQ TO SQL讲道理有点老了。
| LINQ 方法调用 | T-SQL 翻译与解析 |
|---|---|
| FirstOrDefault / First | Top 1 |
| SingleOrDefault / Single | Top 2 特别说明：因为Single需要检测多个元素 |
| Average | AVG 特别说明：ef在执行是用了嵌套子查询的方式，不过在实际执行计划看来没有差异，如下图:  |
| Count | Count(1) 特别说明：直接上图类似上面： |
| LongCount | COUNT_BIG(1) |
| Max | MAX [Expression] |
| Min | MIN [Expression] |
| Sum | SUM [Expression] |
| Min | Min [Expression] |
| GroupBy | group by [Expression] 特别说明：注意红框处不知何意： |
| DefaultIfEmpty + Join in on equals / from | Left out join 特别说明：理解在左连接中对于 null 判断通过 case when 翻译，执行可查询结果，temp和temp3(注意红框处就明白了)依次翻译为下图SQL语句【注意红框部分】->   |
| join / left join + group by + where + aggregate | 特别说明：图中需求颇为牵强，博主站在研究的程度上实验分析生成的SQL望见谅，执行可查询结果，可能有人要问为什么不直接g.Where(x => x.Title.StartsWith("A")).Sum(x => x.Amount) * 2，相信原因你猜到了，如图异常显示如下： 其中数据库举例说明也明确显示查询结果如下图:  ，后面尽量构建符合实际需求，上图： ，其实这里的LINQ还有一种写法可行，把Count(xxx)<=0换成g.Any(x=>x.Title.StartsWith("A"))即可，SQL语句稍显复杂翻译Count(xx)<=0，图中红框可以辅助观看： ，后续如果是你手写SQL如何优化呢，你想到了吗？ |
| OrderBy / OrderByDescending | order by xxx asc (desc) |
| ThenBy / ThenByDescending | order by xx,yy asc / desc 特别说明：需要在现行排序之后使用 |
| Distinct | select distinct xx,yy 特别说明：当使用多字段去重的时候，使用linq select 匿名对象然后distinct即可 var temp = db.Rewards.Where(x => x.Amount >= 50).Select(x => new { x.RewarAccountId, x.Amount }).Distinct(); 注意: 这里temp对象是IQueryable对象表示还可以继续拼接查询，其中翻译的SQL如下图： ，同样是可以使用分组然后拿出Key也可以达到同样效果，代码和SQL见下图，有木有发现神奇的东西:  |
| Concat【直接拼接】 / Unoin【去重】 | Unoin All 对应 Linq的Concat / Unoin All +Distinct 对应 linq的Unoin，这里直接上图了，实验结果如下：LINQ代码如下图：  ，SQL如下： |
| Where | where [expression] |
| All | Case when + Not Exists 特别说明：这里只是出于实验的目的去测试，所以查询条件略显简单导致扫描许多数据，var temp = db.Rewards.All(x => x.Amount >= 95);，翻译的SQL如下图【这个没什么好说的，比较这个ALL比较少用到至于翻译SQL，只能╮(╯▽╰)╭】:  |
| Any | Case when then else end + Exists 这个没什么好说的，比较正常的翻译，var temp2 = db.Rewards.Any(x => x.Amount >= 95);，SQL代码图如下： |
| Where + Contains / Any | 总所周知在SQL中我们在如下C#的LINQ代码书写： var contains = db.Rewards.Where(x => postidsGuids.Contains(x.PostId));，无疑会生成 SQL: In 查询->>>  ，那么同样linq代码：var any = db.Rewards.Where(x => postidsGuids.Any(y => y == x.PostId));，查询结果与上面一致实验结果已经证明，那么问题来了，where中的any生成了什么代码，以及两种写法的在SQL执行计划中的百分比（这里说的一般情况并非绝对，例如：添加索引等操作）？答：直接看图首先是SQL语句的差别-->>  ，其次就是执行计划图差异： |
| Skip / Task | 这里注意分页查询之前得先行排序不然EF会主动抛出异常，当然下面贴出Sql server 2012分页的新方式OFFSET (pageNum-1)*pageSize ROWS FETCH NEXT pageSize ROWS ONLY，语法简单易懂很类似 MySql的 limit 有木有，LINQ代码->>>> var query = db.Rewards.Where(x => x.Amount >= 50).OrderBy(x => x.Amount).Skip((pageNum - 1) * pageSize).Take(pageSize);，SQL完全翻译如下： ，对了这里还得多一句嘴，在SQL SERVER 2008及其以下版本中，SQL翻译为采用 Row_Number() (Order by xxx) 这种语法，当然是 fetch next rows only 无论从语法还是效率都是性价比高的选择，当然是数据库版本有的选的时候 |
| StartsWith / EndsWith / Contains | 相信大家都很熟知的 Like 匹配操作符 ，依次是 (1) like '%xxx' (2) like 'xxx%' (3) like '%xxx%' ，这里额外提起一下：System.Data.Entity.SqlServer.SqlFunctions，这样就是进行以为更加细腻的操作，类似: var q=EFContext.Products.Where(x=>SqlFunctions.PatIndex("%CD%BLUE%", x.ProductName) > 0);，当然这里通过命名空间也得知这是Sql server 才能使用的函数集，因为它不是统一标准的API函数，来吧比卡丘传送门(关于这玩意的详细信息):--->>> https://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions(v=vs.110).aspx |
| LINQ时间日期处理 | 直接参考DbFuncations 微软官方文档使用教程即可，多说一句，能在C#处理就不要用数据库处理了，除外就是这玩意儿来数据库时间日期把，国际惯例，关于它详情使用传送门：https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions(v=vs.113).aspx |
沸腾： 看到这里的同学，肯定再想说，好呀，复杂查询，哼！MD哥TM完全看不出来复杂查询在哪里，好的，下面我们就来模拟真实需求做一些复杂查询看看，从LINQ角度出发或者SQL角度出发，各自的情况到底如何，同样在我看来SQL与LINQ本来就是不分家的，有些同学何必纠结了非要到底用哪一种这种情绪，当能够应对80%的需求的时候，用LINQ快速开发和方便调试以及维护等等甚好，当然还有20%定制化需求用SQL也无可厚非，当然这20%是什么需求，类似CTE等等...那就不得而知了，以上的80%，20%尽数博主自己瞎猜测，切莫当真哦，有些同学可能在担心哎呀，数据字段变化快业务代码深陷.....这个其实EF也有全自动化迁移解决方案，EF也能执行Proc但是这个都已经脱离EF本身的意义了，停！扯远了，好的，下面尝试复杂查询逻辑，LINQ魔幻主义开始了。
需求：现在需要统计 在7月份，整个网站中的某些博主身上的所有文章的收益情况报表，具体表头参考--->>>> 【博主ID 博主名字 总文章数 等待数量 提审数量 驳回数量 发布数量 删除数量 总评论数量 总的收益】，排序按照文章总数倒叙，然后博客主名字正序，其中分页情况--->>> int pageSize = 10;int pageNum = 2;，其中这里相对于上面的数据库表，在Post表增加了如下字段:
public DateTime CreatedTime { get; set; }
public PostStatus PostStatus { get; set; }
public DateTime? PublishTime { get; set; }
其中新增一个枚举类表示文章的状态，代码图如下图：
public enum PostStatus
{
Create = 0,
Wait = 1,
Arraigned = 2,
Rejected = 3,
Published = 4,
Removed = 5
}
代码编写：代码段中有注释，可方便理解，如果有什么其他LINQ方案或者有什么不对的地方，可以在评论区回复我哦，还有如果是你手写SQL语句的话，该怎么写呢，怎么写相对来说容易理解以及执行计划更优呢，我会认真看各位园友的评论或者问题的，O(∩_∩)O嗯!
private static void TestLinqToSqlComplex()
{
// 需求1、现在需要统计 在7月份，整个网站中的某些博主身上的所有文章的收益情况报表
var db = new DemoDbContext();
DateTime startDateTime = new DateTime(2017, 7, 1);
DateTime endDateTime = new DateTime(2017, 7, 31, 23, 59, 59);
// 博主ID 博主名字 总文章数 等待数量 提审数量 驳回数量 发布数量 删除数量 总评论数量 总的收益
// 其他：按照数量倒序排序+分页
// 0、定义 每页数量和查看第几页
int pageSize = 10;
int pageNum = 2;
// 1、缓存临时文章变量
var posts = db.Posts.Where(x => x.CreatedTime >= startDateTime && x.CreatedTime <= endDateTime);
//var temp1 = posts.ToList();
// 2、缓存文章与评论的数据变量
var postCommentsQueryable = from post in posts
join comment in db.Comments on post.PostId equals comment.PostId into leftJoined
from coment1 in leftJoined.DefaultIfEmpty()
group coment1 by new { post.BlogId, post.PostId, post.PostStatus } into grouped
select new
{
grouped.Key.BlogId,
grouped.Key.PostId,
grouped.Key.PostStatus,
TotalCommentQty = grouped.Count()
};
//var temp2 = postCommentsQueryable.ToList();
// 3、缓存文章与打赏的数据变量
var postRewardsQueryable = from post in posts
join reward in db.Rewards on post.PostId equals reward.PostId into leftJoined
from reward1 in leftJoined.DefaultIfEmpty()
group reward1 by new { post.BlogId, post.PostId, post.PostStatus } into grouped
select new
{
grouped.Key.BlogId,
grouped.Key.PostId,
grouped.Key.PostStatus,
TotalRewardAmount = grouped.Any(x => x.Amount > 0) ? grouped.Sum(x => x.Amount) : 0
};
//var temp3 = postRewardsQueryable.ToList();
// 4、合并数据
var tempQueryable = from x in postCommentsQueryable
join y in postRewardsQueryable on new { x.BlogId, x.PostId, x.PostStatus } equals new
{
y.BlogId,
y.PostId,
y.PostStatus
}
select new
{
x.BlogId,
x.PostId,
x.PostStatus,
x.TotalCommentQty,
y.TotalRewardAmount
};
//var temp4 = tempQueryable.ToList();
// 5、数据归并汇总
var query = from blog in db.Blogs
join post in tempQueryable on blog.BlogId equals post.BlogId into leftJoined
from post1 in leftJoined.DefaultIfEmpty()
group post1 by new { blog.BlogId, blog.AuthorName } into grouped
select new
{
grouped.Key.BlogId,
grouped.Key.AuthorName,
TotalPostQty = grouped.Count(),
TotalWaitPostQty = grouped.Count(x => x.PostStatus == PostStatus.Wait),
TotalArraignedPostQty = grouped.Count(x => x.PostStatus == PostStatus.Arraigned),
TotalRejectedPostQty = grouped.Count(x => x.PostStatus == PostStatus.Rejected),
TotalPublishedPostQty = grouped.Count(x => x.PostStatus == PostStatus.Published),
TotalRemovedPostQty = grouped.Count(x => x.PostStatus == PostStatus.Removed),
TotalCommemtQty = grouped.Sum(x => x.TotalCommentQty),
TotalRewardAmount = grouped.Sum(x => x.TotalRewardAmount)
};
//var temp5 = query.ToList();
// 6、排序分页
var result = query.OrderByDescending(x => x.TotalPostQty).ThenBy(x => x.AuthorName).Skip((pageNum - 1) * pageSize).Take(pageSize);
var temp6 = result.ToList();
Console.WriteLine(@"博主ID 博主名字 总文章数 等待数量 提审数量 驳回数量 发布数量 删除数量 总评论数量 总的收益");
temp6.ForEach(x =>
{
Console.WriteLine($@"{x.BlogId} {x.AuthorName} {x.TotalPostQty} {x.TotalWaitPostQty} {x.TotalArraignedPostQty} {x.TotalRejectedPostQty} {x.TotalPublishedPostQty} {x.TotalRemovedPostQty} {x.TotalCommemtQty} {x.TotalRewardAmount}");
});
Console.WriteLine(result.ToString());
}
SQL翻译成果与查询结果：【上图】---->>>:
由于这里的SQL语句太TM长了，这里贴图帖不了直接复制SQL出来贴上来把，想必大家也不想看，哈哈---->>>>>:
SELECT
[Project27].[C4] AS [C1],
[Project27].[BlogId] AS [BlogId],
[Project27].[AuthorName] AS [AuthorName],
[Project27].[C1] AS [C2],
[Project27].[C5] AS [C3],
[Project27].[C6] AS [C4],
[Project27].[C7] AS [C5],
[Project27].[C8] AS [C6],
[Project27].[C9] AS [C7],
[Project27].[C2] AS [C8],
[Project27].[C3] AS [C9]
FROM ( SELECT
[Project26].[C1] AS [C1],
[Project26].[C2] AS [C2],
[Project26].[C3] AS [C3],
[Project26].[BlogId] AS [BlogId],
[Project26].[AuthorName] AS [AuthorName],
1 AS [C4],
[Project26].[C4] AS [C5],
[Project26].[C5] AS [C6],
[Project26].[C6] AS [C7],
[Project26].[C7] AS [C8],
[Project26].[C8] AS [C9]
FROM ( SELECT
[Project22].[C1] AS [C1],
[Project22].[C2] AS [C2],
[Project22].[C3] AS [C3],
[Project22].[BlogId] AS [BlogId],
[Project22].[AuthorName] AS [AuthorName],
[Project22].[C4] AS [C4],
[Project22].[C5] AS [C5],
[Project22].[C6] AS [C6],
[Project22].[C7] AS [C7],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT
@p__linq__0 AS [p__linq__0],
@p__linq__1 AS [p__linq__1],
@p__linq__2 AS [p__linq__2],
@p__linq__3 AS [p__linq__3],
[Extent28].[BlogId] AS [BlogId],
[Extent28].[AuthorName] AS [AuthorName],
[Join24].[PostId1] AS [PostId],
[Join24].[BlogId1] AS [BlogId1],
[Join24].[PostStatus1] AS [PostStatus],
[Join24].[PostId2] AS [PostId1],
[Join24].[BlogId2] AS [BlogId2],
[Join24].[PostStatus2] AS [PostStatus1]
FROM [dbo].[Blog] AS [Extent28]
LEFT OUTER JOIN (SELECT [Distinct9].[PostId] AS [PostId1], [Distinct9].[BlogId] AS [BlogId1], [Distinct9].[PostStatus] AS [PostStatus1], [Distinct10].[PostId] AS [PostId2], [Distinct10].[BlogId] AS [BlogId2], [Distinct10].[PostStatus] AS [PostStatus2]
FROM (SELECT DISTINCT
[Extent29].[PostId] AS [PostId],
[Extent29].[BlogId] AS [BlogId],
[Extent29].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent29]
LEFT OUTER JOIN [dbo].[Comment] AS [Extent30] ON [Extent29].[PostId] = [Extent30].[PostId]
WHERE ([Extent29].[CreatedTime] >= @p__linq__0) AND ([Extent29].[CreatedTime] <= @p__linq__1) ) AS [Distinct9]
INNER JOIN (SELECT DISTINCT
[Extent31].[PostId] AS [PostId],
[Extent31].[BlogId] AS [BlogId],
[Extent31].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent31]
LEFT OUTER JOIN [dbo].[Reward] AS [Extent32] ON [Extent31].[PostId] = [Extent32].[PostId]
WHERE ([Extent31].[CreatedTime] >= @p__linq__2) AND ([Extent31].[CreatedTime] <= @p__linq__3) ) AS [Distinct10] ON ([Distinct9].[BlogId] = [Distinct10].[BlogId]) AND ([Distinct9].[PostId] = [Distinct10].[PostId]) AND ([Distinct9].[PostStatus] = [Distinct10].[PostStatus]) ) AS [Join24] ON [Extent28].[BlogId] = [Join24].[BlogId1]
WHERE ([Project22].[BlogId] = [Extent28].[BlogId]) AND (([Project22].[AuthorName] = [Extent28].[AuthorName]) OR (([Project22].[AuthorName] IS NULL) AND ([Extent28].[AuthorName] IS NULL))) AND (5 = [Join24].[PostStatus1])
) AS [Project25]) AS [C8]
FROM ( SELECT
[Project18].[C1] AS [C1],
[Project18].[C2] AS [C2],
[Project18].[C3] AS [C3],
[Project18].[BlogId] AS [BlogId],
[Project18].[AuthorName] AS [AuthorName],
[Project18].[C4] AS [C4],
[Project18].[C5] AS [C5],
[Project18].[C6] AS [C6],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT
@p__linq__0 AS [p__linq__0],
@p__linq__1 AS [p__linq__1],
@p__linq__2 AS [p__linq__2],
@p__linq__3 AS [p__linq__3],
[Extent23].[BlogId] AS [BlogId],
[Extent23].[AuthorName] AS [AuthorName],
[Join20].[PostId3] AS [PostId],
[Join20].[BlogId3] AS [BlogId1],
[Join20].[PostStatus3] AS [PostStatus],
[Join20].[PostId4] AS [PostId1],
[Join20].[BlogId4] AS [BlogId2],
[Join20].[PostStatus4] AS [PostStatus1]
FROM [dbo].[Blog] AS [Extent23]
LEFT OUTER JOIN (SELECT [Distinct7].[PostId] AS [PostId3], [Distinct7].[BlogId] AS [BlogId3], [Distinct7].[PostStatus] AS [PostStatus3], [Distinct8].[PostId] AS [PostId4], [Distinct8].[BlogId] AS [BlogId4], [Distinct8].[PostStatus] AS [PostStatus4]
FROM (SELECT DISTINCT
[Extent24].[PostId] AS [PostId],
[Extent24].[BlogId] AS [BlogId],
[Extent24].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent24]
LEFT OUTER JOIN [dbo].[Comment] AS [Extent25] ON [Extent24].[PostId] = [Extent25].[PostId]
WHERE ([Extent24].[CreatedTime] >= @p__linq__0) AND ([Extent24].[CreatedTime] <= @p__linq__1) ) AS [Distinct7]
INNER JOIN (SELECT DISTINCT
[Extent26].[PostId] AS [PostId],
[Extent26].[BlogId] AS [BlogId],
[Extent26].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent26]
LEFT OUTER JOIN [dbo].[Reward] AS [Extent27] ON [Extent26].[PostId] = [Extent27].[PostId]
WHERE ([Extent26].[CreatedTime] >= @p__linq__2) AND ([Extent26].[CreatedTime] <= @p__linq__3) ) AS [Distinct8] ON ([Distinct7].[BlogId] = [Distinct8].[BlogId]) AND ([Distinct7].[PostId] = [Distinct8].[PostId]) AND ([Distinct7].[PostStatus] = [Distinct8].[PostStatus]) ) AS [Join20] ON [Extent23].[BlogId] = [Join20].[BlogId3]
WHERE ([Project18].[BlogId] = [Extent23].[BlogId]) AND (([Project18].[AuthorName] = [Extent23].[AuthorName]) OR (([Project18].[AuthorName] IS NULL) AND ([Extent23].[AuthorName] IS NULL))) AND (4 = [Join20].[PostStatus3])
) AS [Project21]) AS [C7]
FROM ( SELECT
[Project14].[C1] AS [C1],
[Project14].[C2] AS [C2],
[Project14].[C3] AS [C3],
[Project14].[BlogId] AS [BlogId],
[Project14].[AuthorName] AS [AuthorName],
[Project14].[C4] AS [C4],
[Project14].[C5] AS [C5],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT
@p__linq__0 AS [p__linq__0],
@p__linq__1 AS [p__linq__1],
@p__linq__2 AS [p__linq__2],
@p__linq__3 AS [p__linq__3],
[Extent18].[BlogId] AS [BlogId],
[Extent18].[AuthorName] AS [AuthorName],
[Join16].[PostId5] AS [PostId],
[Join16].[BlogId5] AS [BlogId1],
[Join16].[PostStatus5] AS [PostStatus],
[Join16].[PostId6] AS [PostId1],
[Join16].[BlogId6] AS [BlogId2],
[Join16].[PostStatus6] AS [PostStatus1]
FROM [dbo].[Blog] AS [Extent18]
LEFT OUTER JOIN (SELECT [Distinct5].[PostId] AS [PostId5], [Distinct5].[BlogId] AS [BlogId5], [Distinct5].[PostStatus] AS [PostStatus5], [Distinct6].[PostId] AS [PostId6], [Distinct6].[BlogId] AS [BlogId6], [Distinct6].[PostStatus] AS [PostStatus6]
FROM (SELECT DISTINCT
[Extent19].[PostId] AS [PostId],
[Extent19].[BlogId] AS [BlogId],
[Extent19].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent19]
LEFT OUTER JOIN [dbo].[Comment] AS [Extent20] ON [Extent19].[PostId] = [Extent20].[PostId]
WHERE ([Extent19].[CreatedTime] >= @p__linq__0) AND ([Extent19].[CreatedTime] <= @p__linq__1) ) AS [Distinct5]
INNER JOIN (SELECT DISTINCT
[Extent21].[PostId] AS [PostId],
[Extent21].[BlogId] AS [BlogId],
[Extent21].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent21]
LEFT OUTER JOIN [dbo].[Reward] AS [Extent22] ON [Extent21].[PostId] = [Extent22].[PostId]
WHERE ([Extent21].[CreatedTime] >= @p__linq__2) AND ([Extent21].[CreatedTime] <= @p__linq__3) ) AS [Distinct6] ON ([Distinct5].[BlogId] = [Distinct6].[BlogId]) AND ([Distinct5].[PostId] = [Distinct6].[PostId]) AND ([Distinct5].[PostStatus] = [Distinct6].[PostStatus]) ) AS [Join16] ON [Extent18].[BlogId] = [Join16].[BlogId5]
WHERE ([Project14].[BlogId] = [Extent18].[BlogId]) AND (([Project14].[AuthorName] = [Extent18].[AuthorName]) OR (([Project14].[AuthorName] IS NULL) AND ([Extent18].[AuthorName] IS NULL))) AND (3 = [Join16].[PostStatus5])
) AS [Project17]) AS [C6]
FROM ( SELECT
[Project10].[C1] AS [C1],
[Project10].[C2] AS [C2],
[Project10].[C3] AS [C3],
[Project10].[BlogId] AS [BlogId],
[Project10].[AuthorName] AS [AuthorName],
[Project10].[C4] AS [C4],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT
@p__linq__0 AS [p__linq__0],
@p__linq__1 AS [p__linq__1],
@p__linq__2 AS [p__linq__2],
@p__linq__3 AS [p__linq__3],
[Extent13].[BlogId] AS [BlogId],
[Extent13].[AuthorName] AS [AuthorName],
[Join12].[PostId7] AS [PostId],
[Join12].[BlogId7] AS [BlogId1],
[Join12].[PostStatus7] AS [PostStatus],
[Join12].[PostId8] AS [PostId1],
[Join12].[BlogId8] AS [BlogId2],
[Join12].[PostStatus8] AS [PostStatus1]
FROM [dbo].[Blog] AS [Extent13]
LEFT OUTER JOIN (SELECT [Distinct3].[PostId] AS [PostId7], [Distinct3].[BlogId] AS [BlogId7], [Distinct3].[PostStatus] AS [PostStatus7], [Distinct4].[PostId] AS [PostId8], [Distinct4].[BlogId] AS [BlogId8], [Distinct4].[PostStatus] AS [PostStatus8]
FROM (SELECT DISTINCT
[Extent14].[PostId] AS [PostId],
[Extent14].[BlogId] AS [BlogId],
[Extent14].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent14]
LEFT OUTER JOIN [dbo].[Comment] AS [Extent15] ON [Extent14].[PostId] = [Extent15].[PostId]
WHERE ([Extent14].[CreatedTime] >= @p__linq__0) AND ([Extent14].[CreatedTime] <= @p__linq__1) ) AS [Distinct3]
INNER JOIN (SELECT DISTINCT
[Extent16].[PostId] AS [PostId],
[Extent16].[BlogId] AS [BlogId],
[Extent16].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent16]
LEFT OUTER JOIN [dbo].[Reward] AS [Extent17] ON [Extent16].[PostId] = [Extent17].[PostId]
WHERE ([Extent16].[CreatedTime] >= @p__linq__2) AND ([Extent16].[CreatedTime] <= @p__linq__3) ) AS [Distinct4] ON ([Distinct3].[BlogId] = [Distinct4].[BlogId]) AND ([Distinct3].[PostId] = [Distinct4].[PostId]) AND ([Distinct3].[PostStatus] = [Distinct4].[PostStatus]) ) AS [Join12] ON [Extent13].[BlogId] = [Join12].[BlogId7]
WHERE ([Project10].[BlogId] = [Extent13].[BlogId]) AND (([Project10].[AuthorName] = [Extent13].[AuthorName]) OR (([Project10].[AuthorName] IS NULL) AND ([Extent13].[AuthorName] IS NULL))) AND (2 = [Join12].[PostStatus7])
) AS [Project13]) AS [C5]
FROM ( SELECT
[Project6].[C1] AS [C1],
[Project6].[C2] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[BlogId] AS [BlogId],
[Project6].[AuthorName] AS [AuthorName],
(SELECT
COUNT(1) AS [A1]
FROM ( SELECT
@p__linq__0 AS [p__linq__0],
@p__linq__1 AS [p__linq__1],
@p__linq__2 AS [p__linq__2],
@p__linq__3 AS [p__linq__3],
[Extent8].[BlogId] AS [BlogId],
[Extent8].[AuthorName] AS [AuthorName],
[Join8].[PostId9] AS [PostId],
[Join8].[BlogId9] AS [BlogId1],
[Join8].[PostStatus9] AS [PostStatus],
[Join8].[PostId10] AS [PostId1],
[Join8].[BlogId10] AS [BlogId2],
[Join8].[PostStatus10] AS [PostStatus1]
FROM [dbo].[Blog] AS [Extent8]
LEFT OUTER JOIN (SELECT [Distinct1].[PostId] AS [PostId9], [Distinct1].[BlogId] AS [BlogId9], [Distinct1].[PostStatus] AS [PostStatus9], [Distinct2].[PostId] AS [PostId10], [Distinct2].[BlogId] AS [BlogId10], [Distinct2].[PostStatus] AS [PostStatus10]
FROM (SELECT DISTINCT
[Extent9].[PostId] AS [PostId],
[Extent9].[BlogId] AS [BlogId],
[Extent9].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent9]
LEFT OUTER JOIN [dbo].[Comment] AS [Extent10] ON [Extent9].[PostId] = [Extent10].[PostId]
WHERE ([Extent9].[CreatedTime] >= @p__linq__0) AND ([Extent9].[CreatedTime] <= @p__linq__1) ) AS [Distinct1]
INNER JOIN (SELECT DISTINCT
[Extent11].[PostId] AS [PostId],
[Extent11].[BlogId] AS [BlogId],
[Extent11].[PostStatus] AS [PostStatus]
FROM [dbo].[Post] AS [Extent11]
LEFT OUTER JOIN [dbo].[Reward] AS [Extent12] ON [Extent11].[PostId] = [Extent12].[PostId]
WHERE ([Extent11].[CreatedTime] >= @p__linq__2) AND ([Extent11].[CreatedTime] <= @p__linq__3) ) AS [Distinct2] ON ([Distinct1].[BlogId] = [Distinct2].[BlogId]) AND ([Distinct1].[PostId] = [Distinct2].[PostId]) AND ([Distinct1].[PostStatus] = [Distinct2].[PostStatus]) ) AS [Join8] ON [Extent8].[BlogId] = [Join8].[BlogId9]
WHERE ([Project6].[BlogId] = [Extent8].[BlogId]) AND (([Project6].[AuthorName] = [Extent8].[AuthorName]) OR (([Project6].[AuthorName] IS NULL) AND ([Extent8].[AuthorName] IS NULL))) AND (1 = [Join8].[PostStatus9])
) AS [Project9]) AS [C4]
FROM ( SELECT
[GroupBy3].[A1] AS [C1],
[GroupBy3].[A2] AS [C2],
[GroupBy3].[A3] AS [C3],
[GroupBy3].[K1] AS [BlogId],
[GroupBy3].[K2] AS [AuthorName]
FROM ( SELECT
[Project5].[BlogId] AS [K1],
[Project5].[AuthorName] AS [K2],
COUNT(1) AS [A1],
SUM([Project5].[C1]) AS [A2],
SUM([Project5].[C2]) AS [A3]
FROM ( SELECT
@p__linq__0 AS [p__linq__0],
@p__linq__1 AS [p__linq__1],
@p__linq__2 AS [p__linq__2],
@p__linq__3 AS [p__linq__3],
[Extent1].[BlogId] AS [BlogId],
[Extent1].[AuthorName] AS [AuthorName],
[Join4].[PostId11] AS [PostId],
[Join4].[BlogId11] AS [BlogId1],
[Join4].[PostStatus11] AS [PostStatus],
[Join4].[C11] AS [C1],
[Join4].[PostId12] AS [PostId1],
[Join4].[BlogId12] AS [BlogId2],
[Join4].[PostStatus12] AS [PostStatus1],
[Join4].[C12] AS [C2]
FROM [dbo].[Blog] AS [Extent1]
LEFT OUTER JOIN (SELECT [Project1].[PostId] AS [PostId11], [Project1].[BlogId] AS [BlogId11], [Project1].[PostStatus] AS [PostStatus11], [Project1].[C1] AS [C11], [Project4].[PostId] AS [PostId12], [Project4].[BlogId] AS [BlogId12], [Project4].[PostStatus] AS [PostStatus12], [Project4].[C1] AS [C12]
FROM (SELECT
[GroupBy1].[K1] AS [PostId],
[GroupBy1].[K2] AS [BlogId],
[GroupBy1].[K3] AS [PostStatus],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent2].[PostId] AS [K1],
[Extent2].[BlogId] AS [K2],
[Extent2].[PostStatus] AS [K3],
COUNT(1) AS [A1]
FROM [dbo].[Post] AS [Extent2]
LEFT OUTER JOIN [dbo].[Comment] AS [Extent3] ON [Extent2].[PostId] = [Extent3].[PostId]
WHERE ([Extent2].[CreatedTime] >= @p__linq__0) AND ([Extent2].[CreatedTime] <= @p__linq__1)
GROUP BY [Extent2].[PostId], [Extent2].[BlogId], [Extent2].[PostStatus]
) AS [GroupBy1] ) AS [Project1]
INNER JOIN (SELECT
[Project2].[PostId] AS [PostId],
[Project2].[BlogId] AS [BlogId],
[Project2].[PostStatus] AS [PostStatus],
CASE WHEN ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Post] AS [Extent6]
INNER JOIN [dbo].[Reward] AS [Extent7] ON [Extent6].[PostId] = [Extent7].[PostId]
WHERE ([Extent6].[CreatedTime] >= @p__linq__2) AND ([Extent6].[CreatedTime] <= @p__linq__3) AND ([Project2].[BlogId] = [Extent6].[BlogId]) AND ([Project2].[PostId] = [Extent6].[PostId]) AND ([Project2].[PostStatus] = [Extent6].[PostStatus]) AND ([Extent7].[Amount] > cast(0 as decimal(18)))
)) THEN [Project2].[C1] ELSE cast(0 as decimal(18)) END AS [C1]
FROM ( SELECT
[GroupBy2].[K1] AS [PostId],
[GroupBy2].[K2] AS [BlogId],
[GroupBy2].[K3] AS [PostStatus],
[GroupBy2].[A1] AS [C1]
FROM ( SELECT
[Extent4].[PostId] AS [K1],
[Extent4].[BlogId] AS [K2],
[Extent4].[PostStatus] AS [K3],
SUM([Extent5].[Amount]) AS [A1]
FROM [dbo].[Post] AS [Extent4]
LEFT OUTER JOIN [dbo].[Reward] AS [Extent5] ON [Extent4].[PostId] = [Extent5].[PostId]
WHERE ([Extent4].[CreatedTime] >= @p__linq__2) AND ([Extent4].[CreatedTime] <= @p__linq__3)
GROUP BY [Extent4].[PostId], [Extent4].[BlogId], [Extent4].[PostStatus]
) AS [GroupBy2]
) AS [Project2] ) AS [Project4] ON ([Project1].[BlogId] = [Project4].[BlogId]) AND ([Project1].[PostId] = [Project4].[PostId]) AND ([Project1].[PostStatus] = [Project4].[PostStatus]) ) AS [Join4] ON [Extent1].[BlogId] = [Join4].[BlogId11]
) AS [Project5]
GROUP BY [Project5].[BlogId], [Project5].[AuthorName]
) AS [GroupBy3]
) AS [Project6]
) AS [Project10]
) AS [Project14]
) AS [Project18]
) AS [Project22]
) AS [Project26]
) AS [Project27]
ORDER BY [Project27].[C1] DESC, [Project27].[AuthorName] ASC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
总结
至此，相信看到这里的同学，心中难免波澜，知道在复杂的情况下EF也是如此锋利和韧性，就好比那把瑞士军刀，亦可一刀致命亦可游刃有余，可以认为博主在这里打了一波EF广告，哈哈O(∩_∩)O哈哈~ ，如果觉得博主这一篇小文还可以，就施舍给我一个赞吧！回到我们的题目，EF对与复杂情况的支持度如何的问题，当然不能仅凭我一文亦或者一个DEMO一个例子，就能说明问题，是千千万万的EF使用者和 entity framework nuget package 下载量来诠释这个问题，OK，我们的第六个问题算是通过发现问题、对比问题、思考问题、解析问题、解决问题以及后面的总结，以上便是证明！现在，回到我们上期的问题当中，回顾一下便是:
7、开发者怎么审查EF翻译的SQL语句？
8、开发者怎么监控EF在网站运行情况？
其实当聪明的你也知道，博主在解决问题6的时候已经，发现了两种方式可以拿到EF的SQL语句，这里下期在说明吧，哈哈哈，以及问题8关于EF的全面监控和自定义开关来控制SQL的跟踪和展示问题，这一切都是围绕了EF构建了一整套的解决方案，ヾ(≧O≦)〃嗷~ ！！！！好的，我们下期见！求关注求互粉，(＾－＾)
后记
夜深了，博主回头看看，这篇博文原来已经耗时了好几天晚上的时间了哈，不过呢，把在寻找解决问题的途中当做是一种享受也是一个好的自我安慰拉，近期博主的公司准备去团建拉，虽然只有一天，好吧，该吐槽还是得吐槽哈，在这里呢，还多说一句，在浩浩荡荡的以业务为主的公司当中，泛指各地不止广州，不止博主自己，有时都是深陷业务代码不能自拔，一天天在完成基本编码之后都难以抽身投身于自己想法和实践当中去，当然这里并不是谁的错，这点得明确！那么问题来了，俗话说得好，时间就像乳沟，挤挤就有了！开个玩笑拉，时间不早了，老铁们，晚安广州！！！
生活本身是很艰难，但是不能成为你不努力的借口！
Remember， Hope is a good thing， maybe the best of things and no good thing ever dies !
