艾伟_转载:打造优雅的Linq To SQL动态查询-阿里云开发者社区

开发者社区> 数据库> 正文

艾伟_转载:打造优雅的Linq To SQL动态查询

简介:   首先我们来看看日常比较典型的一种查询Form   这个场景很简单:就是根据客户名、订单日期、负责人来作筛选条件,然后找出符合要求的订单。   在那遥远的时代,可能避免不了要写这样的简单接口: public interface IOrderService{  IList Search(string customer, DateTime dateFrom, DateTime dateTo, int employeeID);}   具体爱怎么实现就怎么实现啦,存储过程,ORM框架。

  首先我们来看看日常比较典型的一种查询Form

clip_image002

  这个场景很简单:就是根据客户名、订单日期、负责人来作筛选条件,然后找出符合要求的订单。

  在那遥远的时代,可能避免不了要写这样的简单接口:

public interface IOrderService
{
  IList<Order> Search(string customer, DateTime dateFrom, DateTime dateTo, int employeeID);
}

  具体爱怎么实现就怎么实现啦,存储过程,ORM框架。这里假定是用了孩童时代就开始有的存储过程吧:

Create Procedure usp_SearchOrder
@Customer nVarchar(20),
@DateFrom DateTime,
@DateTo DateTime,
@EmployeeID Int
AS
/*以下省去几百行SQL语句*/

  接着写一个类OrderService实现IOrderService, 调用以上存储过程,洋洋洒洒的写上几句代码就可以“安枕睡觉”了。但是,噩梦就从此开始了。

  客户的需求是不断变化的。过了一段时间,设计这个接口的工程师就先被夸赞一番,然后说客户提出需要加多“一个”筛选条件。工程师可能也想过这一点,加一个筛选条件“不外乎“给接口加个参数,存储过程加个参数,where那里加个条件,……苦力活一堆。

  客户的筛选条件是这样:既然订单里有“国家”字段,就想根据国家来筛选条件,并且国家可多选,如下图:

clip_image008

  工程师看到图可能就倒下了……

  以上可以当作笑话看看,不过话说回来,没有一个通用的查询框架,单靠这样的接口

public interface IOrderService
{
  IList<Order> Search(string customer, DateTime dateFrom, DateTime dateTo, int employeeID);
}

  是根本不能适应需求变化。

  在没有Linq 的时代, SQL“ 强人”就试图通过拼接字符串的方法来结束存储过程带来的痛苦,

IList<Order> Search(string sqlQurey);

  结果进入另一个被“SQL注入”的时代(注:我大学时也有一段时间玩过“SQL注入”,不亦乐乎,现在基本上很少找到能够简单注入的网站了,有磨难就有前进的动力嘛 )。

  来到Linq To SQL 的时代 (不得不赞叹Linq把查询发挥到淋漓尽致), 某些朋友就能轻易地挥洒Linq表达式来解决查询问题:

IList<Order> Search(Expression<Func<Order, bool>> expression);

  查询语句:

Expression<Func<Order, bool>> expression = c =>
c.Customer.ContactName.Contains(txtCustomer.Text) &&
c.OrderDate >= DateTime.Parse(txtDateFrom.Text) && c.OrderDate <= DateTime.Parse(txtDateTo.Text) &&
c.EmployeeID == int.Parse(ddlEmployee.SelectedValue);

  然后再一次 “安枕睡觉”。一觉醒来还是不行。

  客户又来新需求:负责人的下拉框加个“ALL”的选项,如果选了“ALL”就搜索所有的负责人相关的Order。

  工程师刷刷几下,又加if else,又加 and 来拼装expression;接着又来新需求,…… 最后expression臃肿无比 (当然这个故事是有点夸张)。

  为什么用上“先进”的工具还是会倒在惨不忍睹的代码海洋里呢?因为Microsoft提供给我们的只是“鱼竿”。这种鱼竿不管在小河还是大海都能钓到东西,而且不管你钓的是鲨鱼还是鲸鱼,也保证鱼竿不会断。但是有些人能钓到大鱼,有些则钓到一双拖鞋。因为关键的鱼饵没用上。也就是说,Microsoft给了我们强大的Linq 表达式,可不是叫我们随便到表现层一放就了事,封装才是硬道理。

  于是,千呼万唤始出来,犹抱 QueryBuilder 半遮脸:

var queryBuilder = QueryBuilder.Create<Order>()
.Like(c => c.Customer.ContactName, txtCustomer.Text)
.Between(c => c.OrderDate, DateTime.Parse(txtDateFrom.Text), DateTime.Parse(txtDateTo.Text))
.Equals(c => c.EmployeeID, int.Parse(ddlEmployee.SelectedValue))
.In(c => c.ShipCountry, selectedCountries );

  这样代码就清爽很多了,逻辑也特别清晰,即使不懂Linq 表达式的人也能明白这些语句是干什么的,因为它的语义基本上跟SQL一样:

WHERE ([t1].[ContactName] LIKE '%A%') AND 
((
[t0].[OrderDate]) >= '1/1/1990 12:00:00 AM') AND (([t0].[OrderDate]) <= '9/25/2009 11:
59:59 PM
') AND
((
[t0].[EmployeeID]) = 1) AND
(
[t0].[ShipCountry] IN ('Finland', 'USA', 'UK'))

 

  对于使用这个QueryBuilder的人来说,他觉得很爽,因为他明白钓什么鱼用什么鱼饵了,模糊查询用Like,范围用Between,……

  对于编写这个QueryBuilder的人来说,也觉得很爽,因为他本身热爱写通用型的代码,就像博客园的老赵那样。

  看到使用方式,聪明人自然就已经想到大概的实现方式。就像厨师吃过别人煮的菜,自然心中也略知是怎么煮的。

  实现方式并不难,这里简单说明一下:

  QueryBuilder.Create() 返回的是IQueryBuilder 接口,而IQueryBuilder 接口只有一个 Expression 属性:

///
///
动态查询条件创建者
///
///
public interface IQueryBuilder
{
Expression<Funcbool>> Expression { get; set; }
}

  于是 Like, Between, Equals, In 就可以根据这个Expression 来无限扩展了。

  以下是实现Like的扩展方法:

///
///
建立 Like ( 模糊 ) 查询条件
///
///
实体
///
动态查询条件创建者
///
属性
///
查询值
///
public static IQueryBuilder Like(this IQueryBuilder q, Expression<Funcstring>> property, string value)
{
value = value.Trim();
if (!string.IsNullOrEmpty(value))
{
var parameter = property.GetParameters();
var constant = Expression.Constant("%" + value + "%");
MethodCallExpression methodExp = Expression.Call(null, typeof(SqlMethods).GetMethod("Like",
new Type[] { typeof(string), typeof(string) }), property.Body, constant);
Expression<Funcbool>> lambda = Expression.Lambda<Funcbool>>(methodExp, parameter);

q.Expression = q.Expression.And(lambda);
}
return q;
}

  每个方法都是对Expression进行修改,然后返回修改后的Expression,以此实现链式编程。

  稍微有点意思的就是 In 的扩展方法(这个害我费了不少时间,前前后后可能4个小时):

///
///
建立 In 查询条件
///
///
实体
///
动态查询条件创建者
///
属性
///
查询值
///
public static IQueryBuilder In(this IQueryBuilder q, Expression<Func> property, params P[] values)
{
if (values != null && values.Length > 0)
{
var parameter = property.GetParameters();
var constant = Expression.Constant(values);
Type type = typeof(P);
Expression nonNullProperty = property.Body;
//如果是Nullable类型,则转化成X类型
if (IsNullableType(type))
{
type = GetNonNullableType(type);
nonNullProperty = Expression.Convert(property.Body, type);
}
Expression<Funcbool>> InExpression = (list, el) => list.Contains(el);
var methodExp = InExpression;
var invoke = Expression.Invoke(methodExp, constant, property.Body);
Expression<Funcbool>> lambda = Expression.Lambda<Funcbool>>(invoke, parameter);
q.Expression = q.Expression.And(lambda);
}
return q;
}

  如果有兴趣的朋友可以在文章末下载源代码,看看其他两个扩展方法。

  嗯,似乎又是时候退场了。什么?你说只有Like, Between, Equals, In不够用?哦,可以自己扩展IQueryBuilder,自己动手丰衣足食嘛。

  我后来又为另外一个Project做了一个“奇怪”的扩展:

  譬如,我们知道打印设置里可以直接写页数号码来筛选要打印哪几页——1,4,9 或者 1-8 这样的方式。

clip_image021 clip_image023

  于是引发这样的需求:

clip_image025clip_image027

  左图查询Bruce和Jeffz的订单;右图查询B直到Z的客户订单。

  还美其名曰:Fuzzy ,意即:模糊不清的 (点这里 看 Fuzzy详细意思)

总结:

  其实这篇文章已经酝酿好久了,近期工作收获很多编程技巧,因为其中一个Project基本上全由我来写框架。尽管平时晚上也有两个多小时可以学习和做自己的框架,但总比不上在公司能够用上八小时来劲。一个Project下来了,又是时候总结一下,希望有空能够继续与大家分享。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章