EF架构~对不起Include，是我冤枉你了！
之前一起认为ＥＦ的延时加载存在性能问题，主要体现在一对多关系上，它会增加与数据库的通讯，而EF本身也提供了“立即加载”include，今天主要说一下它，经过实验，证明如下：
最初接触EF延时加载时，以为只存在这种性能低下的方案，在了解include方法后，才知道这种神奇的方案，它与linq to sql中的DataLoadOptions比较类似，译为立即加载关联对象。
在这里，我对include说一声，对不起，是我冤枉你了，呵呵。
实验代码如下：
Infrastructure层：
public IQueryable<WebManageUsers> GetWebManageUsers(ISpecification<WebManageUsers> specification)
{
return base.GetEntities(specification);
}
Domain层：
public List<WebManageUsers> GetWebManageUsers(DateTime? from, DateTime? to, int? status)
{
//Create specification
UserListSpecification dateSpecification = new UserListSpecification(from, to, status);
var linq = _webManageUsers.GetWebManageUsers(dateSpecification)
.Include(i => i.WebManageRoles)
.Select(i => new WebManageUsers_Ext
{
CreateDate = i.CreateDate,
ManageUserID = i.ManageUserID,
RealName = i.RealName,
WebManageRoles = i.WebManageRoles,
});
return linq.ToList<WebManageUsers>();
}
而前台显示就很简单了，代码省略。
产生的ＳＱＬ代码：
exec sp_executesql N'SELECT
[Project1].[ManageUserID] AS [ManageUserID],
[Project1].[CreateDate] AS [CreateDate],
[Project1].[RealName] AS [RealName],
[Project1].[C1] AS [C1],
[Project1].[ManageRoleID] AS [ManageRoleID],
[Project1].[RoleName] AS [RoleName],
[Project1].[DepartmentID] AS [DepartmentID],
[Project1].[About] AS [About],
[Project1].[UpdateDate] AS [UpdateDate],
[Project1].[SortNumber] AS [SortNumber],
[Project1].[Operator] AS [Operator],
[Project1].[Status] AS [Status],
[Project1].[OperatorAuthority] AS [OperatorAuthority]
FROM ( SELECT
[Extent1].[ManageUserID] AS [ManageUserID],
[Extent1].[RealName] AS [RealName],
[Extent1].[CreateDate] AS [CreateDate],
[Join1].[ManageRoleID1] AS [ManageRoleID],
[Join1].[RoleName] AS [RoleName],
[Join1].[DepartmentID] AS [DepartmentID],
[Join1].[About] AS [About],
[Join1].[UpdateDate] AS [UpdateDate],
[Join1].[SortNumber] AS [SortNumber],
[Join1].[Operator] AS [Operator],
[Join1].[Status] AS [Status],
[Join1].[OperatorAuthority] AS [OperatorAuthority],
CASE WHEN ([Join1].[ManageUserID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM [dbo].[WebManageUsers] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[ManageUserID] AS [ManageUserID], [Extent3].[ManageRoleID] AS [ManageRoleID1], [Extent3].[RoleName] AS [RoleName], [Extent3].[DepartmentID] AS [DepartmentID], [Extent3].[About] AS [About], [Extent3].[UpdateDate] AS [UpdateDate], [Extent3].[SortNumber] AS [SortNumber], [Extent3].[Operator] AS [Operator], [Extent3].[Status] AS [Status], [Extent3].[OperatorAuthority] AS [OperatorAuthority]
FROM [dbo].[WebManageUser_WebManageRoles] AS [Extent2]
INNER JOIN [dbo].[WebManageRoles] AS [Extent3] ON [Extent3].[ManageRoleID] = [Extent2].[ManageRoleID] ) AS [Join1] ON [Extent1].[ManageUserID] = [Join1].[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'
从上面代码上分析，主表的字段已经被过滤，include里的表字段无法过滤，这是正常的，呵呵！
最后贡献一下ObjectContext和DbContext环境下，对Include方法的扩展，使它支持lambda表达式。
namespace Domain.Core.Extensions
{
/// <summary>
/// Class for IQuerable extensions methods
/// <remarks>
/// Include method in IQueryable ( base contract for IObjectSet ) is
/// intended for mock Include method in ObjectQuery{T}.
/// Paginate solve not parametrized queries issues with skip and take L2E methods
/// </remarks>
/// </summary>
public static class IQueryableExtensions
{
#region Extension Methods
/// <summary>
/// Include method for IQueryable
/// </summary>
/// <typeparam name="TEntity">Type of elements</typeparam>
/// <param name="queryable">Queryable object</param>
/// <param name="path">Path to include</param>
/// <returns>Queryable object with include path information</returns>
public static IQueryable<TEntity> Include<TEntity>(this IQueryable<TEntity> queryable, string path)
where TEntity : class
{
if (String.IsNullOrEmpty(path))
throw new ArgumentNullException("path can not empty");
// var query = queryable as ObjectQuery<TEntity>;//ObjectContext時用
var query = queryable as DbQuery<TEntity>;//DbContext時用
if (query != null)//if is a EF ObjectQuery object
return query.Include(path);
return null;
}
/// <summary>
/// Include extension method for IQueryable
/// </summary>
/// <typeparam name="TEntity">Type of elements in IQueryable</typeparam>
/// <param name="queryable">Queryable object</param>
/// <param name="path">Expression with path to include</param>
/// <returns>Queryable object with include path information</returns>
public static IQueryable<TEntity> Include<TEntity>(this IQueryable<TEntity> queryable, Expression<Func<TEntity, object>> path)
where TEntity : class
{
return Include<TEntity>(queryable, AnalyzeExpressionPath(path));
}
/// <summary>
/// Paginate query in a specific page range
/// </summary>
/// <typeparam name="TEntity">Typeof entity in underlying query</typeparam>
/// <typeparam name="S">Typeof ordered data value</typeparam>
/// <param name="queryable">Query to paginate</param>
/// <param name="orderBy">Order by expression used in paginate method
/// <remarks>
/// At this moment Order by expression only support simple order by c=>c.CustomerCode. If you need
/// add more complex order functionality don't use this extension method
/// </remarks>
/// </param>
/// <param name="pageIndex">Page index</param>
/// <param name="pageCount">Page count</param>
/// <param name="ascending">order direction</param>
/// <returns>A paged queryable</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1006:DoNotNestGenericTypesInMemberSignatures")]
public static IQueryable<TEntity> Paginate<TEntity, S>(this IQueryable<TEntity> queryable, Expression<Func<TEntity, S>> orderBy, int pageIndex, int pageCount, bool ascending)
where TEntity : class
{
ObjectQuery<TEntity> query = queryable as ObjectQuery<TEntity>;
if (query != null)
{
//this paginate method use ESQL for solve problems with Parametrized queries
//in L2E and Skip/Take methods
string orderPath = AnalyzeExpressionPath<TEntity, S>(orderBy);
return query.Skip(string.Format(CultureInfo.InvariantCulture, "it.{0} {1}", orderPath, (ascending) ? "asc" : "desc"), "@skip", new ObjectParameter("skip", (pageIndex) * pageCount))
.Top("@limit", new ObjectParameter("limit", pageCount));
}
else // for In-Memory object set
return queryable.OrderBy(orderBy).Skip((pageIndex * pageCount)).Take(pageCount);
}
#endregion
#region Private Methods
static string AnalyzeExpressionPath<TEntity, S>(Expression<Func<TEntity, S>> expression)
where TEntity : class
{
if (expression == (Expression<Func<TEntity, S>>)null)
throw new ArgumentNullException("Argument error");
MemberExpression body = expression.Body as MemberExpression;
if (
(
(body == null)
||
!body.Member.DeclaringType.IsAssignableFrom(typeof(TEntity))
)
||
(body.Expression.NodeType != ExpressionType.Parameter))
{
throw new ArgumentException("Argument error");
}
else
return body.Member.Name;
}
#endregion
}
}
非常感谢您的阅读！
有时，一些知识需要我们自己去研究，探索，当你把一些鲜为人知的东西研究出来后，那种喜悦是发自内心的！
作者：仓储大叔，张占岭，
荣誉：微软MVP
QQ：853066980
支付宝扫一扫，为大叔打赏!