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

非常感謝您的閱讀！

有時，一些知識需要我們自己去研究，探索，當你把一些鮮為人知的東西研究出來后，那種喜悅是發自內心的！

回到目錄

posted @ 2013-02-28 17:16 張占嶺閱讀(29933) 評論(8) 收藏舉報

刷新頁面返回頂部

EF架構~對不起Include，是我冤枉你了！

有時，一些知識需要我們自己去研究，探索，當你把一些鮮為人知的東西研究出來后，那種喜悅是發自內心的！

公告

EF架構~對不起Include，是我冤枉你了！

有時，一些知識需要我們自己去研究，探索，當你把一些鮮為人知的東西研究出來后，那種喜悅是發自內心的！