首页 > 代码库 > Entity Framework之查询总结

Entity Framework之查询总结

2024-09-05 00:01:48   233人阅读

本节针对EF当前支持的查询方式进行分析比较，和简单自定义条件查询的实现。
  • EF的查询方式
  • 自定义条件查询
 
一、EF的查询方式
EF提供的查询方式有以下几种
  • 原始SQL查询
  • LINQ To Entity and Lambda
  • ESQL 与 ObjectQuery
  • ObjectQuery 查询生成器
1.原始SQL查询
在EF 4.1 新增加的DbContext 除了支持LINQ与Lambda查询外，新增了支持原始SQL查询，但是不支持ESQL与ObjectQuery查询。
技术分享 View Code
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的一个功能就是他的分组。
技术分享 View Code
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，所以这种方式也可以应对查询条件变化。
技术分享 View Code
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 查询生成器
技术分享 View Code
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 进行扩展代码如下
 
技术分享 View Code
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
}
}
 
查询表达式枚举
技术分享 View Code
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查询条件，代码如下

技术分享 View Code
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的查询代码

技术分享 View Code
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实现的简易自定义查询功能，不是很完善。我们可以再继续封装排序，分页等查询功能。 

技术分享 View Code
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 处理。

源码下载

参考页面：

http://www.yuanjiaocheng.net/ASPNET-CORE/newproject.html

http://www.yuanjiaocheng.net/webapi/create-crud-api-1-put.html

http://www.yuanjiaocheng.net/CSharp/Csharp-fileinfo.html

http://www.yuanjiaocheng.net/mvc/mvc-ActionVerbs.html

http://www.yuanjiaocheng.net/webapi/mvc-consume-webapi-put.html

http://www.yuanjiaocheng.net/CSharp/csharp-event.html

http://www.yuanjiaocheng.net/CSharp/csharp-variable.html

http://www.yuanjiaocheng.net/Linq/linq-method-syntax.html

http://www.yuanjiaocheng.net/mvc/htmlhelper-validationmessage.html

http://www.yuanjiaocheng.net/CSharp/Csharp-stack.html

http://www.yuanjiaocheng.net/webapi/create-crud-api-1.html

Entity Framework之查询总结


联系
我们