EF通用数据查询

     在使用EF作为数据访问层中，有时候有很多的查询，如果直接返回EF的实体对象，有时就会查询出太多列，如果查询的数据太多的话，会严重影响性能。如果要作到通用查询，首先要独立出，查询条件，排序条件，选择相应的字段。

     根据这种想法，最先想到的就是使用如下代码实现：

public List<TResult> Query<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity,int, TResult>> selector)
            where TEntity : EntityObject
            where TOrderBy : class
            where TResult:class
        {
            if (selector == null)
            {
                throw new ArgumentNullException("selector");
            }


            IQueryable<TEntity> query = CurrentObjectContext.CreateObjectSet<TEntity>();
            if (where != null)
            {
                query = query.Where(where);
            }

            if (orderby != null)
            {
                query = query.OrderBy(orderby);
            }
            return query.Select(selector).ToList();
        }

很遗憾，上面的代码可以编译通过，但运行会报如下异常

 

测试代码如下：

 using (var db = new FeeModelContainer())
            {
                var query = new QueryHelper(db);
                var list = query.Query<FeeEntity, DateTime, object>
                    (c => c.InputTime > DateTime.Now, c => c.InputTime,
                                                         (c, i) => new { ID = c.ID });

上面的代码，只有在一种情况下能正常运行，当TResult为EF实体类型时。

 

既然对象Linq不能解析，设想把加载的内容放在调用函数中，代码如下：

public List<object> Query<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector)
            where TEntity : EntityObject
        {
            if (selector == null)
            {
                throw new ArgumentNullException("selector");
            }


            IQueryable<TEntity> query = CurrentObjectContext.CreateObjectSet<TEntity>();
            if (where != null)
            {
                query = query.Where(where);
            }

            if (orderby != null)
            {
                query = query.OrderBy(orderby);
            }
            return selector(query);
        }

调用代码如下：

using (var db = new FeeModelContainer())
            {
                var query = new QueryHelper(db);
                var list = query.Query<FeeEntity, DateTime>
                    (c => c.InputTime > DateTime.Now, c => c.InputTime,
                                                         c => c.Select(p=>new {ID = p.ID,InputTime = p.InputTime}).ToList<object>());
            }

成功执行，如果加入分页相关的代码，如果在页面上直接使用查询就会提高很性能。

完整的代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq.Expressions;
namespace FeeReport
{
    public class QueryHelper
    {
        protected ObjectContext CurrentObjectContext
        {
            get;
            private set;
        }

        public QueryHelper(ObjectContext context)
        {
            CurrentObjectContext = context;
        }

        public List<TResult> Query<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity,int, TResult>> selector)
            where TEntity : EntityObject
            where TResult:class
        {
            if (selector == null)
            {
                throw new ArgumentNullException("selector");
            }


            IQueryable<TEntity> query = CurrentObjectContext.CreateObjectSet<TEntity>();
            if (where != null)
            {
                query = query.Where(where);
            }

            if (orderby != null)
            {
                query = query.OrderBy(orderby);
            }
            return query.Select(selector).ToList();
        }

        public List<object> Query<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector)
            where TEntity : EntityObject
        {
            if (selector == null)
            {
                throw new ArgumentNullException("selector");
            }


            IQueryable<TEntity> query = CurrentObjectContext.CreateObjectSet<TEntity>();
            if (where != null)
            {
                query = query.Where(where);
            }

            if (orderby != null)
            {
                query = query.OrderBy(orderby);
            }
            return selector(query);
        }

        public PageInfo<object> Query<TEntity, TOrderBy>(int index, int pageSize, Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector)
            where TEntity : EntityObject
            where TOrderBy : class
        {
            if (selector == null)
            {
                throw new ArgumentNullException("selector");
            }

            if(index <=0)
            {
                index = 1;
            }

            if(pageSize<=0)
            {
                pageSize = 10;
            }

            IQueryable<TEntity> query = CurrentObjectContext.CreateObjectSet<TEntity>();
            if (where != null)
            {
                query = query.Where(where);
            }
            int count = query.Count();

            if(index *pageSize >count)
            {
                index = count/pageSize;
            }
            if(count%pageSize >0)
            {
                index++;
            }

            if (index <= 0)
            {
                index = 1;
            }

            if (orderby != null)
            {
                query = query.OrderBy(orderby);
            }
            return new PageInfo<object>(index,pageSize,count,selector(query));
        }
    }

    public class PageInfo< TEntity> where TEntity : class
    {
        public PageInfo(int index, int pageSize, int count, List<TEntity> list)
        {
            Index = index;
            PageSize = pageSize;
            Count = count;
            List = list;
        }

        public int Index { get; private set; }
        public int PageSize { get; private set; }
        public int Count { get; private set; }
        public List<TEntity> List { get; private set; }
    }
}