Entity Framework之查询总结

本节针对EF当前支持的查询方式进行分析比较，和简单自定义条件查询的实现。

• EF的查询方式
• 自定义条件查询

一、EF的查询方式

EF提供的查询方式有以下几种

• 原始SQL查询
• LINQ To Entity and Lambda
• ESQL 与 ObjectQuery
• ObjectQuery 查询生成器

1.原始SQL查询

在EF 4.1 新增加的DbContext 除了支持LINQ与Lambda查询外，新增了支持原始SQL查询，但是不支持ESQL与ObjectQuery查询。

            DemoDBEntities context = new DemoDBEntities();

            DbSet<BlogMaster> set = context.Set<BlogMaster>();

            List<BlogMaster> list = set.SqlQuery("select *from BlogMaster where UserId='3'").ToList();
            
            List<BlogMaster> listAll = context.Database.SqlQuery<BlogMaster>("select *from BlogMaster").ToList();

使用原始SQL查询，既灵活又方便维护，加上DbContext泛型处理，可以将最终的查询数据集映射成对象集合。而且SQL语句有错误时，提醒也比较明确。项目中，大家都会碰到查询条件经常变动的问题，针对这种情况我们以使用通过定制的查询模板以SQL拼接的方式解决，而不是修改代码。

 

2.LINQ To Entity and Lambda

这两种是比较常用的方式，也是效率比较高的，简洁方便，但是不灵活，如果条件变了，可能就需要修改代码。相信做过报表的人都曾为复杂的SQL语句以及SQL语句的执行效率头痛过，而LINQ和Lambda 方便就在于可以将复杂的SQL拆分出来，在内存中解决这些数据的合并筛选，并且效率要远高于SQL。我最喜欢的LINQ的一个功能就是他的分组。

 DemoDBEntities context = new DemoDBEntities();

            DbSet<BlogMaster> set = context.Set<BlogMaster>();

            var result = from u in set.ToList()
                                    where u.UserID == 3
                                    select u;

            var resultGroup = from u in set.ToList()
                              group u by u.UserID
                                  into g
                                  select g;

            var list = set.Where(o => o.UserID == 3);
            var listGroup = set.GroupBy(o => o.UserID);

 不管是哪种方式，LINQ To Entity and Lambda  EF 都是支持的。

 

3.ESQL 与 ObjectQuery

首先说明一点，目前DbContext不支持这种方式查询。ESQL同原始SQL 只是写法稍为有点区别，但是特点差不多，灵活易于维护。由于可以拼接ESQL，所以这种方式也可以应对查询条件变化。

 DemoDBEntities context = new DemoDBEntities();

            //DbSet<BlogMaster> set = context.Set<BlogMaster>();

            string queryString = @"SELECT VALUE it FROM DemoDBEntities.BlogMaster as
                               it WHERE it.UserId > @UserId order by it.UserId desc";
            ObjectQuery<BlogMaster> query = new ObjectQuery<BlogMaster>(queryString, context);              

            // Add parameters to the collection.
            query.Parameters.Add(new ObjectParameter("UserId",6));

            List<BlogMaster> list = query.ToList();

原始SQL与ESQL 区别在于参数类型的处理，因为原始的SQL你在拼接的条件的时候要对不同的参数值类型处理，例如是where Name='tt' and UserId=6 and Sex=true ，而ESQL则是object传入，直接实现SQL语句的转换。可惜DbContext不支持ESQL，所以只能自己去解决SQL条件不同值类型的拼接处理。

4.ObjectQuery 查询生成器

            DemoDBEntities context = new DemoDBEntities();
            
            ObjectQuery<BlogMaster> query = context.CreateObjectSet<BlogMaster>()
                .Where("it.UserId > @UserId",
                new ObjectParameter("UserId", 6))
                .OrderBy("it.UserId desc");

            List<BlogMaster> list = query.ToList();

这种方式基本上有ESQL相同，只是分组，排序，条件过滤都要单独处理，相比就没结合ESQL使用灵活了。

以上四种方式各有优缺点，如果是批量做页面的查询，每个查询页面和条件各不相同，并且查询条件可能会变动的话，建议使用DbContext的SQL查询，或者是ESQL结合ObjectQuery，这两种方式易于通过查询模板拼接生成SQL语句，但不适合生成复杂的SQL语句。而LINQ or Lambda 以及ObjectQuery方式，则不适合做一些重复查询逻辑的工作，而单独处理一些页面的查询或者复杂的报表还是比较灵活的。

二、自定义条件查询

基于自定义条件查询，不适合处理过于复杂的条件查询语句。 我们来看一下思路

假如WEB前台或是应用界面有一些查询字段，A需要Like ,B是=，C是> 某个时间并<小于某个时间，以后可能会增加D，或E。为了不修改代码，而仅仅拖拉控件设置属性就可新增查询条件，下面以WINFORM为例，WEB类似操作（你可以将查询条件以XML或JSON形式传回后台）。

首先对Winform几个控件进行扩展处理，首先设计个扩展接口IExtControl，IExtControl 有以下几个属性

ExtUsingType 控件使用类型 用于控制是否可编辑

ExtDataField 指向数据库的查询字段

ExtDataValue 查询字段值

ExtQueryExpression 查询表达式  是like ?= ? >?

我们对TextBox，CheckBox，ComBox，DateTimePicker 进行扩展代码如下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.ComponentModel;

namespace FinancialSystem
{
    public interface IExtControl
    {      
        /// <summary>
        /// 使用类型
        /// </summary>
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("控件使用类型")]
        ExtUsingTypeEnum ExtUsingType
        {
            set;
            get;
        }
      
        /// <summary>
        /// 数据字段名称
        /// </summary>
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("指向数据库表某个字段")]
        string ExtDataField
        {
            set;
            get;
        }

        /// <summary>
        /// 数据字段名称
        /// </summary>
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("指向数据库表字段对应的值")]
        string ExtDataValue { set; get; }

        /// <summary>
        /// 查询表达式
        /// </summary>
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("查询表达式选择")]
        ExtQueryExpressionEnum ExtQueryExpression { get; set; }

        /// <summary>
        /// 清空值
        /// </summary>
        void ClearDataValue();
    }

    public class ExtTextBox : TextBox, IExtControl
    {

        #region IExtControl 成员
        private ExtUsingTypeEnum extUsingType = ExtUsingTypeEnum.Query;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("控件使用类型")]
        public ExtUsingTypeEnum ExtUsingType
        {
            get
            {
                return extUsingType;
            }
            set
            {
                extUsingType = value;
            }
        }

        private string extDataField;
    
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("指向数据库表某个字段")]
        public string ExtDataField
        {
            get
            {
                return extDataField;
            }
            set
            {
                extDataField=value;
            }
        }

        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("字段值")]
        public string ExtDataValue
        {
            get { return this.Text.ToString(); }
            set { this.Text = value; }
        }


        private ExtQueryExpressionEnum extQueryExpression = ExtQueryExpressionEnum.EQ;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("查询表达式选择")]
        public ExtQueryExpressionEnum ExtQueryExpression
        {
            get
            {
                return extQueryExpression;
            }
            set
            {
                extQueryExpression = value;
            }
        }
      
        public void ClearDataValue()
        {
            this.Text = "";
        }

        #endregion
    }

    public class ExtComBox : ComboBox, IExtControl
    {
        #region IExtControl 成员
        private ExtUsingTypeEnum extUsingType = ExtUsingTypeEnum.Query;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("控件使用类型")]
        public ExtUsingTypeEnum ExtUsingType
        {
            get
            {
                return extUsingType;
            }
            set
            {
                extUsingType = value;
            }
        }

        private string extDataField;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("指向数据库表某个字段")]
        public string ExtDataField
        {
            get
            {
                return extDataField;
            }
            set
            {
                extDataField = value;
            }
        }

        private ExtQueryExpressionEnum extQueryExpression = ExtQueryExpressionEnum.EQ;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("查询表达式选择")]
        public ExtQueryExpressionEnum ExtQueryExpression
        {
            get
            {
                return extQueryExpression;
            }
            set
            {
                extQueryExpression = value;
            }
        }

        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("字段值")]
        public string ExtDataValue
        {
            get { return this.SelectedValue==null?"":this.SelectedValue.ToString(); }
            set { this.SelectedValue = value; }
        }

        /// <summary>
        /// 清空值
        /// </summary>
        public void ClearDataValue()
        {
            this.SelectedValue = -1;           
        }
        #endregion
    }

    public class ExtCheckBox : CheckBox, IExtControl
    {
        #region IExtControl 成员
        private ExtUsingTypeEnum extUsingType = ExtUsingTypeEnum.Query;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("控件使用类型")]
        public ExtUsingTypeEnum ExtUsingType
        {
            get
            {
                return extUsingType;
            }
            set
            {
                extUsingType = value;
            }
        }

        private string extDataField;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("指向数据库表某个字段")]
        public string ExtDataField
        {
            get
            {
                return extDataField;
            }
            set
            {
                extDataField = value;
            }
        }

        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("字段值")]
        public string ExtDataValue
        {
            get 
            {
                if (this.Checked)
                    return "True";
                else
                    return "False";
            }
            set 
            {
                if (value == "True")
                    this.Checked = true;
                else
                    this.Checked = false;
            }
        }

        private ExtQueryExpressionEnum extQueryExpression = ExtQueryExpressionEnum.EQ;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("查询表达式选择")]
        public ExtQueryExpressionEnum ExtQueryExpression
        {
            get
            {
                return extQueryExpression;
            }
            set
            {
                extQueryExpression = value;
            }
        }

        /// <summary>
        /// 清空值
        /// </summary>
        public void ClearDataValue()
        {
            this.Checked = false;
        }
        #endregion
    }
    

    public class ExtDateTimePicker : DateTimePicker, IExtControl
    {
        #region IExtControl 成员
        private ExtUsingTypeEnum extUsingType = ExtUsingTypeEnum.Edit;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("控件使用类型")]
        public ExtUsingTypeEnum ExtUsingType
        {
            get
            {
                return extUsingType;
            }
            set
            {
                extUsingType = value;
            }
        }

        private string extDataField;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("指向数据库表某个字段")]
        public string ExtDataField
        {
            get
            {
                return extDataField;
            }
            set
            {
                extDataField = value;
            }
        }

        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("字段值")]
        public string ExtDataValue
        {
            get { return this.Value.ToString(); }
            set { this.Text = value; }
        }


        private ExtQueryExpressionEnum extQueryExpression = ExtQueryExpressionEnum.EQ;
        [Category("Ext-扩展属性")]
        [Browsable(true)]
        [Description("查询表达式选择")]
        public ExtQueryExpressionEnum ExtQueryExpression
        {
            get
            {
                return extQueryExpression;
            }
            set
            {
                extQueryExpression = value;
            }
        }

        /// <summary>
        /// 清空值
        /// </summary>
        public void ClearDataValue()
        {
            this.Value = DateTime.Now;
        }
        #endregion
    }

    public class ExtDataGridViewTextBoxColumn : DataGridViewTextBoxColumn, IExtControl
    {

        #region IExtControl 成员

        public ExtUsingTypeEnum ExtUsingType
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public string ExtDataField
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public string ExtDataValue
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public ExtQueryExpressionEnum ExtQueryExpression
        {
            get
            {
                throw new NotImplementedException();
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public void ClearDataValue()
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}

 

查询表达式枚举

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace FinancialSystem
{
    public enum ExtUsingTypeEnum
    {
        Query = 0,
        Edit = 1,
        Normal = 2
    }

    public enum ExtQueryExpressionEnum
    {
        LK = 0,
        EQ = 1,
        IN = 2,
        YEARANDMONTH = 3,
        FR = 4,
        TO = 5,
        UE = 6,
        GT = 7,
        LT = 8,
        NOTNULL=9,
        ISNULL=10,
        OR=11,
        SK =8       
    }
}

完成上面工作后，我们VS左侧工具栏就可以看到我们新扩展的控件

我们利用上面的扩展控件设计个查询界面，右键控件属性，分别设置ExtDataField,ExtQueryExpression值

设置控件属性，新增查询控件就直接拖到界面上，设置对应数据库查询字段及查询方式即可

上面我们就完成了自定义查询的界面的设计，接下来就是读取这些控件的扩展属性生成SQL查询条件，代码如下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.IO;

namespace EF.WinForm
{
    public class QueryHelper
    {
        /// <summary>
        /// 清空查询条件
        /// </summary>
        /// <param name="parentControl"></param>
        public static void ClearWhereString(Control parentControl)
        {
            if (parentControl == null)
                throw new ArgumentNullException("装载查询控件的容器不存在!");

            foreach (Control control in parentControl.Controls)
            {
                if (control is IExtControl)
                {
                    if (control.Enabled)
                    {
                        IExtControl extControl = (IExtControl)control;
                        extControl.ClearDataValue();
                    }
                }
            }
        }
        /// <summary>
        /// 读取生成查询条件
        /// </summary>
        /// <param name="parentControl"></param>
        /// <returns></returns>
        public static string GetWhereString(Control parentControl)
        {
            return GetWhereString(parentControl, "And");

        }

        /// <summary>
        /// 读取查询条件(web直接处理传回的XML，或者JSON字符串)
        /// </summary>
        /// <param name="parentControl"></param>
        /// <param name="logic"></param>
        /// <returns></returns>
        public static string GetWhereString(Control parentControl,string logic)
        {
            if(parentControl == null)
                throw new ArgumentNullException("装载查询控件的容器不存在!");
            
            string whereStr="";
            foreach(Control control in parentControl.Controls)
            {
                if(control is IExtControl)
                {
                    IExtControl extControl=(IExtControl)control;   
                    if(extControl.ExtDataValue.Trim()!="")                   
                        whereStr += " ("+GetMatch(extControl.ExtDataField,extControl.ExtQueryExpression.ToString(),extControl.ExtDataValue)+") "+logic;
                }
                   
            }
            if (whereStr == "")
                return "";
            else
                return " where " + whereStr.Substring(0, whereStr.Length - logic.Length);   

        }
      

        /// <summary>
        /// 根据匹配域，匹配方法，匹配域值取得匹配条件串
        /// </summary>
        /// <param name="fieldName">匹配域名称(同数据库一致)</param>
        /// <param name="match">匹配方法(null=EQ)</param>
        /// <param name="fieldValue">匹配域的值</param>
        /// <returns>域的条件串</returns>
        public static string GetMatch(string fieldName,string match,string fieldValue)
        {
            string mop="=";
            fieldValue = fieldValue.Replace("'","''");
            if(AttrIsNull(match))
                match = "EQ";
            switch(match.ToUpper())
            {
                case "EQ":    
                    mop = fieldName + "='" + fieldValue + "'";    
                    break;
                case "UE":    
                    mop = fieldName + "<>'" + fieldValue + "'";    
                    break;
                case "GT":
                    mop = fieldName + ">'" + fieldValue + "'";    
                    break;
                case "LT":    
                    mop = fieldName + "<'" + fieldValue + "'";    
                    break;
                case "IN":    
                    string[] flds=fieldValue.Split(',');
                    string fldstr="";
                    for(int i=0;i<flds.Length;i++)
                        fldstr+="'"+flds[i]+"',";
                    fldstr = fldstr.Substring(0,fldstr.Length-1);
                    mop = fieldName + " IN (" + fldstr + ")";    
                    break;
                case "FR":
                    mop = fieldName + ">='" + fieldValue + "'";    
                    break;
                case "TO":
                    mop = fieldName + "<='" + fieldValue + "'";    
                    break;
                case "LK":
                    mop = fieldName + " LIKE '%" + fieldValue + "%'";    
                    break;
                case "SK":
                    string[] flds0=fieldValue.Split(' ');
                    string fldstr0="%";
                    for(int i=0;i<flds0.Length;i++)
                        if(flds0[i].Trim()!="")
                            fldstr0+=flds0[i]+"%";                     
                    mop = fieldName + " LIKE '" + fldstr0 + "'";    
                    break;
                case "NOTNULL":
                    mop = fieldName + " IS NOT NULL and "+fieldName + " <>'' ";    
                    break;
                case "ISNULL":
                    mop = fieldName + " IS NULL or "+fieldName + " ='' ";    
                    break;
                case "OR":            
                    string[] flds1=fieldValue.Split(' ');
                    string fldstr1="";
                    for(int i=0;i<flds1.Length;i++)
                        if(flds1[i].Trim()!="")
                            fldstr1+=fieldName + " Like '%"+flds1[i]+"%' or ";
                    fldstr1 = fldstr1.Substring(0,fldstr1.Length-3);
                    mop =" (" + fldstr1 + ") ";    
                    break;
                case "YEARANDMONTH":
                    mop = string.Format("YEAR({0})=YEAR('{1}') and MONTH({0})=MONTH('{1}')", fieldName, fieldValue);
                    break;
            }
            return mop;
        }

        public static bool AttrIsNull(string attr)
        {    if(attr == null || attr == "")
                return true;
            else
                return false;
        }
      
    }
}

这段代码的基本业务就是查找所有继随IExtControl的接口控件，读取其中的字段，值，及查询方式拼接生成SQL where条件

继续来调用QueryHelper实现Winform的查询代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using EF.Model;
using EF.BLL;

namespace EF.WinForm
{
    public partial class FrmMain : Form
    {
        public FrmMain()
        {
            InitializeComponent();
        }     

        private void btnQuery_Click(object sender, EventArgs e)
        { 
            //获取自定义查询条件
            string whereStr = QueryHelper.GetWhereString(this.groupBox1);            
            //读取数据 （BLL层调用DAL层的context SqlQuery方法实现
            BlogArticleService service=new BlogArticleService();
            List<BlogArticle> list = service.SqlQuery(whereStr,"");

            this.bindingSource.DataSource = list;
            this.bindingNavigator.BindingSource = bindingSource;
            this.dataGridView.DataSource = this.bindingSource;
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            QueryHelper.ClearWhereString(this.groupBox2);
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
            BlogCategoryService service = new BlogCategoryService();
            List<BlogCategory> list = service.FindAll();
            this.extComBox1.DataSource = list;
            this.extComBox1.DisplayMember = "CateName";
            this.extComBox1.ValueMember = "CateID";
        }
    }
}

运行后查看结果

如果再新增查询条件，我们只要拖个控制设置一下对应数据库属性，这样不用修改后台代码了。例如：

以上就是EF 基于原始SQL实现的简易自定义查询功能，不是很完善。我们可以再继续封装排序，分页等查询功能。 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EF.DAL;
using EF.Model;

namespace EF.BLL
{
    public class BlogArticleService
    {
        IRepository<BlogArticle> repository;
        public BlogArticleService(IRepository<BlogArticle> repository)
        {
            this.repository = repository;
        }

        public BlogArticleService()
        {
            this.repository = new BlogArticleRepository();
        }

        public BlogArticle Create()
        {
            return repository.Create();
        }

        public BlogArticle Insert(BlogArticle entity)
        {
            return repository.Insert(entity);
        }

        public BlogArticle Update(BlogArticle entity)
        {
            return repository.Update(entity);
        }

        public void Delete(BlogArticle entity)
        {
            repository.Delete(entity);
        }

        //为了演示，查询可单独提取一个接口类 同Resposity用法相同
        public List<BlogArticle> SqlQuery(string sqlString)
        {
            return repository.SqlQuery(sqlString);
        }

        //为了演示，查询可单独提取一个接口类 同Resposity用法相同
        public List<BlogArticle> SqlQuery(string whereStr,string orderStr)
        {
            string tableName = typeof(BlogArticle).Name;
            string sqlString = string.Format("select *from {0} {1} {2}", tableName, whereStr, orderStr);
            return repository.SqlQuery(sqlString);
        }

        //实现一个简易分页功能
        public List<BlogArticle> SqlQuery(string whereStr, string orderStr,ref int pageIndex,ref int pageCount,ref int sumCount)
        {
            string tableName = typeof(BlogArticle).Name;
            string sqlString = string.Format("select *from {0} {1} {2}", tableName, whereStr, orderStr);
            List<BlogArticle> list = repository.SqlQuery(sqlString);
            sumCount = list.Count;
            return list.Skip(pageIndex).Take(pageCount).ToList();
        }

        //为了演示，查询可单独提取一个接口类 同Resposity用法相同
        public List<BlogArticle> SqlQuery(string fields,string whereStr, string orderStr)
        {
            string tableName = typeof(BlogArticle).ToString();
            string sqlString = string.Format("select {0} from {1} {2} {3}", fields, tableName, whereStr, orderStr);
            return repository.SqlQuery(sqlString);
        }

       
    }
}

利用ESQL结合ObjectQuery同样实现这个功能，并且不用处理值类型转换。如果DbContext 在后面支持了ESQL，建议还是用ESQL 处理。

源码下载