none
entityframework FindAllPageData optimization RRS feed

  • Question

  • This is my pager function

    public static PageData<T> FindAllByPage<T, S>(this ObjectContext context, int PageIndex, int PageSize,

    Expression<Func<T, bool>> condition = null, List<String> includeChild = null,

    OrderByParams<T,S> orderByAndDesc = null) where T : class, new() { PageData<T> pageData = new PageData<T>(); var query = context.CreateObjectSet<T>() as ObjectQuery<T>; if (includeChild == null) { includeChild = new List<String>() { }; } foreach (var child in includeChild) { query = query.Include(child); } if (condition != null) { query = query.Where(condition) as ObjectQuery<T>; } pageData.TotalCount = query.Count(); Boolean isFirstOrderBy = false; if (orderByAndDesc != null && orderByAndDesc.OrderByParamsList != null && orderByAndDesc.OrderByParamsList.Count > 0) { foreach (var o in orderByAndDesc.OrderByParamsList) { if (!isFirstOrderBy) { if (o.Item2 == true) { query = query.OrderByDescending(o.Item1) as ObjectQuery<T>; } else { query = query.OrderBy(o.Item1) as ObjectQuery<T>; } isFirstOrderBy = true; } else { if (o.Item2 == true) { query = query.ThenByDescending(o.Item1) as ObjectQuery<T>; } else { query = query.ThenBy(o.Item1) as ObjectQuery<T>; } } } } pageData.DataList = query.Skip(PageIndex * PageSize).Take(PageSize).ToList(); // time out return pageData; }

    call function :

    var orderByParams = new OrderByParams<Model.Goods, Int32?>();
                orderByParams.Add((g => g.Creater.OrderBy), true);
                orderByParams.Add((g => g.GoodsId), true);
    
                context.FindAllByPage<Model.Goods, Int32?>(1, 20, (g => g.Creater.IsDelete == false), null, orderByParams);

    SQL generated

    SELECT TOP (20) 
    [Filter1].[GoodsId] AS [GoodsId], 
    [Filter1].[GoodsName] AS [GoodsName], 
    [Filter1].[PackagingType] AS [PackagingType], 
    [Filter1].[Weight] AS [Weight], 
    [Filter1].[Size] AS [Size], 
    [Filter1].[Freight] AS [Freight], 
    [Filter1].[ResidueCount] AS [ResidueCount], 
    [Filter1].[TotalCount] AS [TotalCount], 
    [Filter1].[Creater_CreateDate] AS [Creater_CreateDate], 
    [Filter1].[Creater_CreateUser] AS [Creater_CreateUser], 
    [Filter1].[Creater_GUID] AS [Creater_GUID], 
    [Filter1].[Creater_IsDelete] AS [Creater_IsDelete], 
    [Filter1].[Creater_UpdateDate] AS [Creater_UpdateDate], 
    [Filter1].[Creater_UpdateUser] AS [Creater_UpdateUser], 
    [Filter1].[Creater_OrderBy] AS [Creater_OrderBy], 
    [Filter1].[ReceiveSheetId] AS [ReceiveSheetId], 
    [Filter1].[GoodsNumber] AS [GoodsNumber], 
    [Filter1].[IsArrived] AS [IsArrived], 
    [Filter1].[Price] AS [Price], 
    [Filter1].[GoodsGroup] AS [GoodsGroup]
    FROM ( SELECT [Extent1].[GoodsId] AS [GoodsId], [Extent1].[GoodsName] AS [GoodsName], [Extent1].[PackagingType] AS [PackagingType], [Extent1].[Weight] AS [Weight], [Extent1].[Size] AS [Size], [Extent1].[Freight] AS [Freight], 
    [Extent1].[ResidueCount] AS [ResidueCount], [Extent1].[TotalCount] AS [TotalCount], [Extent1].[Creater_CreateDate] AS [Creater_CreateDate], [Extent1].[Creater_CreateUser] AS [Creater_CreateUser], [Extent1].[Creater_GUID] AS [Creater_GUID], [Extent1].[Creater_IsDelete] AS [Creater_IsDelete], [Extent1].[Creater_UpdateDate] AS [Creater_UpdateDate], [Extent1].[Creater_UpdateUser] AS [Creater_UpdateUser], [Extent1].[Creater_OrderBy] AS [Creater_OrderBy], [Extent1].[ReceiveSheetId] AS [ReceiveSheetId], [Extent1].[GoodsNumber] AS [GoodsNumber], [Extent1].[IsArrived] AS [IsArrived], [Extent1].[Price] AS [Price], [Extent1].[GoodsGroup] AS [GoodsGroup], row_number() OVER (ORDER BY [Extent1].[Creater_OrderBy] DESC, [Extent1].[GoodsId] DESC) AS [row_number]
    	FROM [dbo].[Goods] AS [Extent1]
    	WHERE 0 = [Extent1].[Creater_IsDelete]
    )  AS [Filter1]
    WHERE [Filter1].[row_number] > 0
    ORDER BY [Filter1].[Creater_OrderBy] DESC, [Filter1].[GoodsId] DESC
    The data record is about 10 million. The SQL is always timing out. The question is how can I optimize this funciton.



    Sunday, January 6, 2013 5:52 AM

All replies