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
    }
}

非常感谢您的阅读！

有时，一些知识需要我们自己去研究，探索，当你把一些鲜为人知的东西研究出来后，那种喜悦是发自内心的！

 回到目录