将不确定变为确定~DAL层向BLL层返回数据用IEnumerable<T>还是IQueryable<T>
DAL层从数据库中将数据读出来后,传递给BLL层,再通过BLL处理数据后,向WEB层返回,最后到页面上输出,这是一个过程,在这个过程中我需要说两句,BLL层与WEB层通讯肯定是List,IList等等,即实实在在的数据,而DAL层向BLL层返回数据时,就需要注意了,因为DAL层返回一般是完整的结果集,而在BLL层根据具体的业务,再进行字段的过滤,这对于减少数据库与网络传递的压力是有帮助的,下面是一个案值,分别是向BLL层以IEnumerable和IQueryable方式进行数据的返回,最后在BLL层进行加工数据的操作,看代码:
DAL层:
IQueryable方式:
public IQueryable<WebManageUsers> GetWebManageUsers(ISpecification<WebManageUsers> specification) { var linq = base.GetEntities(specification).AsQueryable() .Include(i => i.WebManageUser_WebManageRoles) .AsQueryable(); //include会增加链库操作 return linq; }
IEnumerable方式:
public IEnumerable<WebManageUsers> GetWebManageUsers(ISpecification<WebManageUsers> specification) { var linq = base.GetEntities(specification).AsQueryable() .Include(i => i.WebManageUser_WebManageRoles) .AsQueryable(); //include会增加链库操作 return linq; }
在BLL层进行数据重新组装:
public List<WebManageUsers> GetWebManageUsers(DateTime? from, DateTime? to, int? status) { //Create specification UserListSpecification dateSpecification = new UserListSpecification(from, to, status); return _webManageUsers.GetWebManageUsers(dateSpecification).Select(i => new WebManageUsers_Ext { CreateDate = i.CreateDate, ManageUserID = i.ManageUserID, RealName = i.RealName, }).ToList<WebManageUsers>(); }
WEB层输出结果,两种方式答到的效果是一样的,但对于一个开发人员来说,只达到效果是远远不够的,因为我们必须要知道LINQ在数据库上究竟干了什么事,即LINQ被翻译成SQL语句是什么样的:
IEnumerable方式:
exec sp_executesql N'SELECT [Project1].[ManageUserID] AS [ManageUserID], [Project1].[LoginName] AS [LoginName], [Project1].[Password] AS [Password], [Project1].[RealName] AS [RealName], [Project1].[Gender] AS [Gender], [Project1].[Phone] AS [Phone], [Project1].[Mobile] AS [Mobile], [Project1].[Email] AS [Email], [Project1].[AvatarUrl] AS [AvatarUrl], [Project1].[QQ] AS [QQ], [Project1].[MSN] AS [MSN], [Project1].[Rtx] AS [Rtx], [Project1].[Birthday] AS [Birthday], [Project1].[Description] AS [Description], [Project1].[DepartmentID] AS [DepartmentID], [Project1].[CreateDate] AS [CreateDate], [Project1].[UpdateDate] AS [UpdateDate], [Project1].[Operator] AS [Operator], [Project1].[Status] AS [Status], [Project1].[IntroductionURL] AS [IntroductionURL], [Project1].[WebSystemID] AS [WebSystemID], [Project1].[C1] AS [C1], [Project1].[ManageUserID1] AS [ManageUserID1], [Project1].[ManageRoleID] AS [ManageRoleID], [Project1].[CreateDate1] AS [CreateDate1] FROM ( SELECT [Extent1].[ManageUserID] AS [ManageUserID], [Extent1].[LoginName] AS [LoginName], [Extent1].[Password] AS [Password], [Extent1].[RealName] AS [RealName], [Extent1].[Gender] AS [Gender], [Extent1].[Phone] AS [Phone], [Extent1].[Mobile] AS [Mobile], [Extent1].[Email] AS [Email], [Extent1].[AvatarUrl] AS [AvatarUrl], [Extent1].[QQ] AS [QQ], [Extent1].[MSN] AS [MSN], [Extent1].[Rtx] AS [Rtx], [Extent1].[Birthday] AS [Birthday], [Extent1].[Description] AS [Description], [Extent1].[DepartmentID] AS [DepartmentID], [Extent1].[CreateDate] AS [CreateDate], [Extent1].[UpdateDate] AS [UpdateDate], [Extent1].[Operator] AS [Operator], [Extent1].[Status] AS [Status], [Extent1].[IntroductionURL] AS [IntroductionURL], [Extent1].[WebSystemID] AS [WebSystemID], [Extent2].[ManageUserID] AS [ManageUserID1], [Extent2].[ManageRoleID] AS [ManageRoleID], [Extent2].[CreateDate] AS [CreateDate1], CASE WHEN ([Extent2].[ManageUserID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[WebManageUsers] AS [Extent1] LEFT OUTER JOIN [dbo].[WebManageUser_WebManageRoles] AS [Extent2] ON [Extent1].[ManageUserID] = [Extent2].[ManageUserID] WHERE (@p__linq__0 = 1) AND ([Extent1].[CreateDate] > (CASE WHEN (@p__linq__1 IS NULL) THEN @p__linq__2 ELSE @p__linq__1 END)) AND ([Extent1].[CreateDate] < (CASE WHEN (@p__linq__3 IS NULL) THEN @p__linq__4 ELSE @p__linq__3 END)) ) AS [Project1] ORDER BY [Project1].[ManageUserID] ASC, [Project1].[C1] ASC',N'@p__linq__0 bit,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7)',@p__linq__0=1,@p__linq__1=NULL,@p__linq__2='0001-01-01 00:00:00',@p__linq__3=NULL,@p__linq__4='9999-12-31 23:59:59.9999999'
IQueryable方式:
exec sp_executesql N'SELECT [Extent1].[ManageUserID] AS [ManageUserID], [Extent1].[CreateDate] AS [CreateDate], [Extent1].[RealName] AS [RealName] FROM [dbo].[WebManageUsers] AS [Extent1] WHERE (@p__linq__0 = 1) AND ([Extent1].[CreateDate] > (CASE WHEN (@p__linq__1 IS NULL) THEN @p__linq__2 ELSE @p__linq__1 END)) AND ([Extent1].[CreateDate] < (CASE WHEN (@p__linq__3 IS NULL) THEN @p__linq__4 ELSE @p__linq__3 END))',N'@p__linq__0 bit,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7)',@p__linq__0=1,@p__linq__1=NULL,@p__linq__2='0001-01-01 00:00:00',@p__linq__3=NULL,@p__linq__4='9999-12-31 23:59:59.9999999'
看到这里,您应该有所体会了吧,我们希望的肯定是IQueryable方式的,呵呵,它才是LINQ世界里的延时加载,下面咱们总结一下什么时候用IQueryable方
式来返回数据:
1 结果集很大,需要在BLL层去重新组装时
2 使用take skip实现分页时