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 inset.ToList()
where u.UserID ==3
select u;

var resultGroup = from u inset.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
{
publicinterface 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();
}

publicclass ExtTextBox : TextBox, IExtControl
{

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

privatestring extDataField;

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

[Category("Ext-扩展属性")]
[Browsable(true)]
[Description("字段值")]
publicstring ExtDataValue
{
get { returnthis.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;
}
}

publicvoid ClearDataValue()
{
this.Text ="";
}

#endregion
}

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

privatestring extDataField;
[Category("Ext-扩展属性")]
[Browsable(true)]
[Description("指向数据库表某个字段")]
publicstring 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("字段值")]
publicstring ExtDataValue
{
get { returnthis.SelectedValue==null?"":this.SelectedValue.ToString(); }
set { this.SelectedValue = value; }
}

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

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

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

[Category("Ext-扩展属性")]
[Browsable(true)]
[Description("字段值")]
publicstring 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>
publicvoid ClearDataValue()
{
this.Checked =false;
}
#endregion
}


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

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

[Category("Ext-扩展属性")]
[Browsable(true)]
[Description("字段值")]
publicstring ExtDataValue
{
get { returnthis.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>
publicvoid ClearDataValue()
{
this.Value = DateTime.Now;
}
#endregion
}

publicclass ExtDataGridViewTextBoxColumn : DataGridViewTextBoxColumn, IExtControl
{

#region IExtControl 成员

public ExtUsingTypeEnum ExtUsingType
{
get
{
thrownew NotImplementedException();
}
set
{
thrownew NotImplementedException();
}
}

publicstring ExtDataField
{
get
{
thrownew NotImplementedException();
}
set
{
thrownew NotImplementedException();
}
}

publicstring ExtDataValue
{
get
{
thrownew NotImplementedException();
}
set
{
thrownew NotImplementedException();
}
}

public ExtQueryExpressionEnum ExtQueryExpression
{
get
{
thrownew NotImplementedException();
}
set
{
thrownew NotImplementedException();
}
}

publicvoid ClearDataValue()
{
thrownew NotImplementedException();
}

#endregion
}
}

 

查询表达式枚举

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

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

publicenum 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
{
publicclass QueryHelper
{
///<summary>
/// 清空查询条件
///</summary>
///<param name="parentControl"></param>
publicstaticvoid ClearWhereString(Control parentControl)
{
if (parentControl ==null)
thrownew 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>
publicstaticstring GetWhereString(Control parentControl)
{
return GetWhereString(parentControl, "And");

}

///<summary>
/// 读取查询条件(web直接处理传回的XML，或者JSON字符串)
///</summary>
///<param name="parentControl"></param>
///<param name="logic"></param>
///<returns></returns>
publicstaticstring GetWhereString(Control parentControl,string logic)
{
if(parentControl ==null)
thrownew 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>
publicstaticstring 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;
}

publicstaticbool AttrIsNull(string attr)
{ if(attr ==null|| attr =="")
returntrue;
else
returnfalse;
}

}
}

这段代码的基本业务就是查找所有继随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
{
publicpartialclass FrmMain : Form
{
public FrmMain()
{
InitializeComponent();
} 

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

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

privatevoid 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
{
publicclass 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);
}

publicvoid 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,refint pageIndex,refint pageCount,refint 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 处理。