全部栏目

首页 热点资讯 义务教育 高等教育 出国留学 考研考公
您的当前位置：首页lambda表达式封装对数据库的查询

lambda表达式封装对数据库的查询

2023-11-11 来源：哗拓教育

1.为什么要封装lambda表达式数据库查询，原因有一下几点：

1.1.在以往的开发中进行数据库表查询时，其实所需要的字段就是其中几个，但是在开发中，开发者往往习惯select * 进行查询，当数据多和用户量多时，查询的效率会降低。

1.2.在写查询where条件的时候，总是用string.format去拼接字符串，开发效率低。

1.3.代码不够优雅，代码中嵌套和多sql语句，如果是表字段发生改变时编译器检查不出来，代码出错的概率大。

1.4.本着 write less  do more 原则

 

2.代码展示和类结构：

技术分享

文件目录：

技术分享

分析：

设计的思路是模仿数据库的查询语句，如： select a，b..... from tab  where  a=‘’  Order by a  基本结构写，其中help是解析lambda表达式，获取到对应字段的名称。

opreationclass中看文件的名称就可以很好的区分，DbSelect 中可以获取到要查询的字段和Operationwhere的对象并把查询字段值赋值给operationwhere属性字段，

operationwhere 可以获取大查询的条件字符串，在把查询字段和查询条件字符串传递给oprationExcute 进行查询返回结果集（也可以传给oprationOderby，在传递给oprationExcute ），oprationExcute是执行返回数据集结果，在把结果集传给TransformationData进行list和datatable，json的转换。

源代码：

 

1.GetClomun：

using System;using System.Collections.Generic;using System.ComponentModel.DataAnnotations;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Threading.Tasks;

namespace GTJ.Core.Select.Helpers{ public class GetClomun { /// <summary> /// [Display(Name = "")] /// 获得类属性中标记的名称 /// </summary> /// <param name="expr"></param> /// <returns></returns> public static string GetDisplayName(Expression expr) { var memberParam = expr as MemberExpression; if (memberParam != null) { return GetDisplayName(memberParam); } var unary = expr as UnaryExpression; if (unary != null) { return GetDisplayName(unary.Operand as MemberExpression); } var call = expr as MethodCallExpression; if (call != null) { return GetDisplayName(call.Object as MemberExpression); }

return string.Empty;

}

/// <summary> /// [Display(Name = "记住帐号")] /// 获得类属性中标记的中文名 /// </summary> /// <param name="memberParam"></param> /// <returns></returns> private static string GetDisplayName(MemberExpression memberParam) { var name = memberParam.Member.Name; var property = memberParam.Member.ReflectedType.GetProperty(name); var displays = property.GetCustomAttributes(typeof(DisplayAttribute), false); if (displays == null || displays.Length == 0) return property.Name; else return (displays[0] as DisplayAttribute).Name; } }}

 

2.OprationExcute

using CYQ.Data;using CYQ.Data.Table;using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass{ public class OprationExcute<T> where T:new() { /// <summary> /// 要显示的列 /// </summary> public List<string> column = new List<string>(); /// <summary> /// 查询字符串 /// </summary> public string WhereStr = null; /// <summary> /// 排序字符串 /// </summary> public string OderByStr = null; /// <summary> /// 执行显示的条数 /// </summary> /// <param name="top"></param> /// <returns></returns> public TransformationData<T> Excute(string top) { if (this.OderByStr == null) { this.OderByStr = "CreateTime desc"; } TransformationData<T> Data = new TransformationData<T>(); Data.Table= GetMDataTable(this.column.ToArray(), top, this.WhereStr+ " order by " + this.OderByStr,null); return Data; }

private MDataTable GetMDataTable(string[] arr = null, string top = null, string where = null, string name = null) { if (name == null) { name = new T().ToString(); } using (MAction action = new MAction(name)) { if (arr != null) { var columnStr = ""; for (var i = 0; i < arr.Length; i++) { columnStr += arr[i].ToString(); columnStr += ","; } columnStr = columnStr.TrimEnd(‘,‘); action.SetSelectColumns(columnStr); } if (where != null && top != null) { return action.Select(Convert.ToInt32(top), where);

} else if (where == null && top != null) { return action.Select(Convert.ToInt32(top)); } else if (where != null && top == null) { return action.Select(where); } else { return action.Select(); } } } }}

3.OprationFindModel

using CYQ.Data;using System;using System.Collections.Generic;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass{ public class OprationFindModel<T> where T:new() { /// <summary> /// 查询model情况 /// </summary> /// <param name="funcs"></param> public T FindModel(params Expression<Func<T, bool>>[] funcs) { GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>(); for(var i = 0; i < funcs.Length; i++) { exp.AddAndWhere(funcs[i]); }

return GetModelData(exp.Where(false)); }

private T GetModelData(string where, string name = null) { try { if (name == null) { name = new T().ToString(); } using (MAction action = new MAction(name)) { if (action.Fill(where)) { return action.Data.ToEntity<T>(); } else { return new T(); } }

} catch (Exception ex) { throw new Exception(ex.Message.ToString()); } } }}

4.OprationOderByDesc

using System;using System.Collections.Generic;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass{ public class OprationOderByDesc<T> where T:new () { /// <summary> /// 要显示的列 /// </summary> public List<string> column = new List<string>(); /// <summary> /// 查询字符串 /// </summary> public string WhereStr = null; public OprationExcute<T> OderByDesc(bool result,params Expression<Func<T,string>>[] funcs) { List<string> OderBy = new List<string>(); OprationExcute<T> excute = new OprationExcute<T>(); for (var i = 0; i < funcs.Length; i++) { OderBy.Add(Helpers.GetClomun.GetDisplayName(funcs[i].Body)); } excute.OderByStr = string.Join(",", OderBy.ToArray()); if (result == true) { excute.OderByStr += " desc"; } else { excute.OderByStr += " asc"; } excute.column = this.column; excute.WhereStr = this.WhereStr; return excute; } }}

 

5.OprationWhere

using System;using System.Collections.Generic;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass{ public class OprationWhere<T> where T:new() { /// <summary> /// 要显示的列 /// </summary> public List<string> column = new List<string>();

/// <summary> /// 查询条件 /// </summary> /// <param name="funcs"></param> /// <returns></returns> public OprationExcute<T> Where(params Expression<Func<T, bool>>[] funcs) { OprationExcute<T> Excute = new OprationExcute<T>(); GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>(); for(var i = 0; i < funcs.Length; i++) { exp.AddAndWhere(funcs[i]); } Excute.WhereStr = exp.Where(false); Excute.column = this.column; return Excute; }

public OprationOderByDesc<T> Where(bool resutl ,params Expression<Func<T, bool>>[] funcs) { OprationOderByDesc<T> OderBy = new OprationOderByDesc<T>(); GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>(); for (var i = 0; i < funcs.Length; i++) { exp.AddAndWhere(funcs[i]); } OderBy.WhereStr = exp.Where(false); OderBy.column = this.column;

return OderBy; } }}

 

6.TransformationData

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;using CYQ.Data.Table;using CYQ.Data;

namespace GTJ.Core.Select.OperationClass{ public class TransformationData<T> where T : new () {

public MDataTable Table = new MDataTable(); /// <summary> /// 转成list /// </summary> public List<T> ToList() { return Table.ToList<T>(); }

public DataTable ToDataTable() { return Table.ToDataTable(); }

public string ToJson() { return Table.ToJson(); } }}

 

7.DbSelect

using System;using System.Collections.Generic;using System.Linq;using System.Linq.Expressions;using System.Text;using System.Threading.Tasks;

/// <summary>/// zouhp 2017.6.16 V1/// lambda 快速进行单表查询/// </summary>namespace GTJ.Core.Select{ /// <summary> /// lambda快速查询 /// </summary> /// <typeparam name="T"></typeparam> public class DbSelect<T> where T:new() { public OperationClass.OprationWhere<T> Select(params Expression<Func<T,string>>[] funcs) { OperationClass.OprationWhere<T> where = new OperationClass.OprationWhere<T>(); for (var i = 0; i < funcs.Length; i++) { where.column.Add(Helpers.GetClomun.GetDisplayName(funcs[i].Body)); } return where; }

public T Select(params Expression<Func<T, bool>>[] funcs) { OperationClass.OprationFindModel<T> Model = new OperationClass.OprationFindModel<T>(); return Model.FindModel(funcs); } }}

 

lambda表达式封装对数据库的查询

标签：action   ann   帐号   sage   span   als   empty   etc   中文   

小编还为您整理了以下内容，可能对您也有帮助：

lambda表达式可以在sql数据库查询里面直接用吗

1.为什么要封装lambda表达式数据库查询，原因有一下几点：

1.1.在以往的开发中进行数据库表查询时，其实所需要的字段就是其中几个，但是在开发中，开发者往往习惯select * 进行查询，当数据多和用户量多时，查询的效率会降低。

1.2.在写查询where条件的时候，总是用string.format去拼接字符串，开发效率低。

1.3.代码不够优雅，代码中嵌套和多sql语句，如果是表字段发生改变时编译器检查不出来，代码出错的概率大。

1.4.本着 write less  do more 原则

 

2.代码展示和类结构：

技术分享

文件目录：

技术分享

分析：

设计的思路是模仿数据库的查询语句，如： select a，b..... from tab  where  a=‘’  Order by a  基本结构写，其中help是解析lambda表达式，获取到对应字段的名称。

opreationclass中看文件的名称就可以很好的区分，DbSelect 中可以获取到要查询的字段和Operationwhere的对象并把查询字段值赋值给operationwhere属性字段，

operationwhere 可以获取大查询的条件字符串，在把查询字段和查询条件字符串传递给oprationExcute 进行查询返回结果集（也可以传给oprationOderby，在传递给oprationExcute ），oprationExcute是执行返回数据集结果，在把结果集传给TransformationData进行list和datatable，json的转换。

源代码：

 

1.GetClomun：

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.Helpers
{
public class GetClomun
{
/// <summary>
/// [Display(Name = "")]
/// 获得类属性中标记的名称
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static string GetDisplayName(Expression expr)
{
var memberParam = expr as MemberExpression;
if (memberParam != null)
{
return GetDisplayName(memberParam);
}
var unary = expr as UnaryExpression;
if (unary != null)
{
return GetDisplayName(unary.Operand as MemberExpression);
}
var call = expr as MethodCallExpression;
if (call != null)
{
return GetDisplayName(call.Object as MemberExpression);
}

return string.Empty;

}

/// <summary>
/// [Display(Name = "记住帐号")]
/// 获得类属性中标记的中文名
/// </summary>
/// <param name="memberParam"></param>
/// <returns></returns>
private static string GetDisplayName(MemberExpression memberParam)
{
var name = memberParam.Member.Name;
var property = memberParam.Member.ReflectedType.GetProperty(name);
var displays = property.GetCustomAttributes(typeof(DisplayAttribute), false);
if (displays == null || displays.Length == 0)
return property.Name;
else
return (displays[0] as DisplayAttribute).Name;
}
}
}

 

2.OprationExcute

using CYQ.Data;
using CYQ.Data.Table;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationExcute<T> where T:new()
{
/// <summary>
/// 要显示的列
/// </summary>
public List<string> column = new List<string>();
/// <summary>
/// 查询字符串
/// </summary>
public string WhereStr = null;
/// <summary>
/// 排序字符串
/// </summary>
public string OderByStr = null;
/// <summary>
/// 执行显示的条数
/// </summary>
/// <param name="top"></param>
/// <returns></returns>
public TransformationData<T> Excute(string top)
{
if (this.OderByStr == null)
{
this.OderByStr = "CreateTime desc";
}
TransformationData<T> Data = new TransformationData<T>();
Data.Table= GetMDataTable(this.column.ToArray(), top, this.WhereStr+ " order by " + this.OderByStr,null);
return Data;
}

private MDataTable GetMDataTable(string[] arr = null, string top = null, string where = null, string name = null)
{
if (name == null)
{
name = new T().ToString();
}
using (MAction action = new MAction(name))
{
if (arr != null)
{
var columnStr = "";
for (var i = 0; i < arr.Length; i++)
{
columnStr += arr[i].ToString();
columnStr += ",";
}
columnStr = columnStr.TrimEnd(‘,‘);
action.SetSelectColumns(columnStr);
}
if (where != null && top != null)
{
return action.Select(Convert.ToInt32(top), where);

}
else if (where == null && top != null)
{
return action.Select(Convert.ToInt32(top));
}
else if (where != null && top == null)
{
return action.Select(where);
}
else
{
return action.Select();
}
}
}
}
}

3.OprationFindModel

using CYQ.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationFindModel<T> where T:new()
{
/// <summary>
/// 查询model情况
/// </summary>
/// <param name="funcs"></param>
public T FindModel(params Expression<Func<T, bool>>[] funcs)
{
GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>();

for(var i = 0; i < funcs.Length; i++)
{
exp.AddAndWhere(funcs[i]);
}

return GetModelData(exp.Where(false));
}

private T GetModelData(string where, string name = null)
{
try
{
if (name == null)
{
name = new T().ToString();
}
using (MAction action = new MAction(name))
{
if (action.Fill(where))
{
return action.Data.ToEntity<T>();
}
else
{
return new T();
}
}

}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
}

4.OprationOderByDesc

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationOderByDesc<T> where T:new ()
{
/// <summary>
/// 要显示的列
/// </summary>
public List<string> column = new List<string>();
/// <summary>
/// 查询字符串
/// </summary>
public string WhereStr = null;
public OprationExcute<T> OderByDesc(bool result,params Expression<Func<T,string>>[] funcs)
{
List<string> OderBy = new List<string>();
OprationExcute<T> excute = new OprationExcute<T>();
for (var i = 0; i < funcs.Length; i++)
{
OderBy.Add(Helpers.GetClomun.GetDisplayName(funcs[i].Body));
}
excute.OderByStr = string.Join(",", OderBy.ToArray());
if (result == true)
{
excute.OderByStr += " desc";
}
else
{
excute.OderByStr += " asc";
}
excute.column = this.column;
excute.WhereStr = this.WhereStr;
return excute;
}
}
}

 

5.OprationWhere

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationWhere<T> where T:new()
{
/// <summary>
/// 要显示的列
/// </summary>
public List<string> column = new List<string>();

/// <summary>
/// 查询条件
/// </summary>
/// <param name="funcs"></param>
/// <returns></returns>
public OprationExcute<T> Where(params Expression<Func<T, bool>>[] funcs)
{
OprationExcute<T> Excute = new OprationExcute<T>();
GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>();
for(var i = 0; i < funcs.Length; i++)
{
exp.AddAndWhere(funcs[i]);
}
Excute.WhereStr = exp.Where(false);
Excute.column = this.column;
return Excute;
}

public OprationOderByDesc<T> Where(bool resutl ,params Expression<Func<T, bool>>[] funcs)
{
OprationOderByDesc<T> OderBy = new OprationOderByDesc<T>();
GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>();
for (var i = 0; i < funcs.Length; i++)
{
exp.AddAndWhere(funcs[i]);
}
OderBy.WhereStr = exp.Where(false);
OderBy.column = this.column;

return OderBy;
}
}
}

 

6.TransformationData

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CYQ.Data.Table;
using CYQ.Data;

namespace GTJ.Core.Select.OperationClass
{
public class TransformationData<T> where T : new ()
{

public MDataTable Table = new MDataTable();
/// <summary>
/// 转成list
/// </summary>
public List<T> ToList()
{
return Table.ToList<T>();
}

public DataTable ToDataTable()
{
return Table.ToDataTable();
}

public string ToJson()
{
return Table.ToJson();
}
}
}

 

7.DbSelect

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

/// <summary>
/// zouhp 2017.6.16 V1
/// lambda 快速进行单表查询
/// </summary>
namespace GTJ.Core.Select
{
/// <summary>
/// lambda快速查询
/// </summary>
/// <typeparam name="T"></typeparam>
public class DbSelect<T> where T:new()
{
public OperationClass.OprationWhere<T> Select(params Expression<Func<T,string>>[] funcs)
{
OperationClass.OprationWhere<T> where = new OperationClass.OprationWhere<T>();
for (var i = 0; i < funcs.Length; i++)
{
where.column.Add(Helpers.GetClomun.GetDisplayName(funcs[i].Body));
}
return where;
}

public T Select(params Expression<Func<T, bool>>[] funcs)
{
OperationClass.OprationFindModel<T> Model = new OperationClass.OprationFindModel<T>();
return Model.FindModel(funcs);
}
}
}

 

lambda表达式封装对数据库的查询

标签：action   ann   帐号   sage   span   als   empty   etc   中文   

lambda表达式可以在sql数据库查询里面直接用吗

1.为什么要封装lambda表达式数据库查询，原因有一下几点：

1.1.在以往的开发中进行数据库表查询时，其实所需要的字段就是其中几个，但是在开发中，开发者往往习惯select * 进行查询，当数据多和用户量多时，查询的效率会降低。

1.2.在写查询where条件的时候，总是用string.format去拼接字符串，开发效率低。

1.3.代码不够优雅，代码中嵌套和多sql语句，如果是表字段发生改变时编译器检查不出来，代码出错的概率大。

1.4.本着 write less  do more 原则

 

2.代码展示和类结构：

技术分享

文件目录：

技术分享

分析：

设计的思路是模仿数据库的查询语句，如： select a，b..... from tab  where  a=‘’  Order by a  基本结构写，其中help是解析lambda表达式，获取到对应字段的名称。

opreationclass中看文件的名称就可以很好的区分，DbSelect 中可以获取到要查询的字段和Operationwhere的对象并把查询字段值赋值给operationwhere属性字段，

operationwhere 可以获取大查询的条件字符串，在把查询字段和查询条件字符串传递给oprationExcute 进行查询返回结果集（也可以传给oprationOderby，在传递给oprationExcute ），oprationExcute是执行返回数据集结果，在把结果集传给TransformationData进行list和datatable，json的转换。

源代码：

 

1.GetClomun：

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.Helpers
{
public class GetClomun
{
/// <summary>
/// [Display(Name = "")]
/// 获得类属性中标记的名称
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
public static string GetDisplayName(Expression expr)
{
var memberParam = expr as MemberExpression;
if (memberParam != null)
{
return GetDisplayName(memberParam);
}
var unary = expr as UnaryExpression;
if (unary != null)
{
return GetDisplayName(unary.Operand as MemberExpression);
}
var call = expr as MethodCallExpression;
if (call != null)
{
return GetDisplayName(call.Object as MemberExpression);
}

return string.Empty;

}

/// <summary>
/// [Display(Name = "记住帐号")]
/// 获得类属性中标记的中文名
/// </summary>
/// <param name="memberParam"></param>
/// <returns></returns>
private static string GetDisplayName(MemberExpression memberParam)
{
var name = memberParam.Member.Name;
var property = memberParam.Member.ReflectedType.GetProperty(name);
var displays = property.GetCustomAttributes(typeof(DisplayAttribute), false);
if (displays == null || displays.Length == 0)
return property.Name;
else
return (displays[0] as DisplayAttribute).Name;
}
}
}

 

2.OprationExcute

using CYQ.Data;
using CYQ.Data.Table;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationExcute<T> where T:new()
{
/// <summary>
/// 要显示的列
/// </summary>
public List<string> column = new List<string>();
/// <summary>
/// 查询字符串
/// </summary>
public string WhereStr = null;
/// <summary>
/// 排序字符串
/// </summary>
public string OderByStr = null;
/// <summary>
/// 执行显示的条数
/// </summary>
/// <param name="top"></param>
/// <returns></returns>
public TransformationData<T> Excute(string top)
{
if (this.OderByStr == null)
{
this.OderByStr = "CreateTime desc";
}
TransformationData<T> Data = new TransformationData<T>();
Data.Table= GetMDataTable(this.column.ToArray(), top, this.WhereStr+ " order by " + this.OderByStr,null);
return Data;
}

private MDataTable GetMDataTable(string[] arr = null, string top = null, string where = null, string name = null)
{
if (name == null)
{
name = new T().ToString();
}
using (MAction action = new MAction(name))
{
if (arr != null)
{
var columnStr = "";
for (var i = 0; i < arr.Length; i++)
{
columnStr += arr[i].ToString();
columnStr += ",";
}
columnStr = columnStr.TrimEnd(‘,‘);
action.SetSelectColumns(columnStr);
}
if (where != null && top != null)
{
return action.Select(Convert.ToInt32(top), where);

}
else if (where == null && top != null)
{
return action.Select(Convert.ToInt32(top));
}
else if (where != null && top == null)
{
return action.Select(where);
}
else
{
return action.Select();
}
}
}
}
}

3.OprationFindModel

using CYQ.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationFindModel<T> where T:new()
{
/// <summary>
/// 查询model情况
/// </summary>
/// <param name="funcs"></param>
public T FindModel(params Expression<Func<T, bool>>[] funcs)
{
GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>();

for(var i = 0; i < funcs.Length; i++)
{
exp.AddAndWhere(funcs[i]);
}

return GetModelData(exp.Where(false));
}

private T GetModelData(string where, string name = null)
{
try
{
if (name == null)
{
name = new T().ToString();
}
using (MAction action = new MAction(name))
{
if (action.Fill(where))
{
return action.Data.ToEntity<T>();
}
else
{
return new T();
}
}

}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
}
}

4.OprationOderByDesc

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationOderByDesc<T> where T:new ()
{
/// <summary>
/// 要显示的列
/// </summary>
public List<string> column = new List<string>();
/// <summary>
/// 查询字符串
/// </summary>
public string WhereStr = null;
public OprationExcute<T> OderByDesc(bool result,params Expression<Func<T,string>>[] funcs)
{
List<string> OderBy = new List<string>();
OprationExcute<T> excute = new OprationExcute<T>();
for (var i = 0; i < funcs.Length; i++)
{
OderBy.Add(Helpers.GetClomun.GetDisplayName(funcs[i].Body));
}
excute.OderByStr = string.Join(",", OderBy.ToArray());
if (result == true)
{
excute.OderByStr += " desc";
}
else
{
excute.OderByStr += " asc";
}
excute.column = this.column;
excute.WhereStr = this.WhereStr;
return excute;
}
}
}

 

5.OprationWhere

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GTJ.Core.Select.OperationClass
{
public class OprationWhere<T> where T:new()
{
/// <summary>
/// 要显示的列
/// </summary>
public List<string> column = new List<string>();

/// <summary>
/// 查询条件
/// </summary>
/// <param name="funcs"></param>
/// <returns></returns>
public OprationExcute<T> Where(params Expression<Func<T, bool>>[] funcs)
{
OprationExcute<T> Excute = new OprationExcute<T>();
GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>();
for(var i = 0; i < funcs.Length; i++)
{
exp.AddAndWhere(funcs[i]);
}
Excute.WhereStr = exp.Where(false);
Excute.column = this.column;
return Excute;
}

public OprationOderByDesc<T> Where(bool resutl ,params Expression<Func<T, bool>>[] funcs)
{
OprationOderByDesc<T> OderBy = new OprationOderByDesc<T>();
GTJ.Core.AiExpConditions<T> exp = new AiExpConditions<T>();
for (var i = 0; i < funcs.Length; i++)
{
exp.AddAndWhere(funcs[i]);
}
OderBy.WhereStr = exp.Where(false);
OderBy.column = this.column;

return OderBy;
}
}
}

 

6.TransformationData

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CYQ.Data.Table;
using CYQ.Data;

namespace GTJ.Core.Select.OperationClass
{
public class TransformationData<T> where T : new ()
{

public MDataTable Table = new MDataTable();
/// <summary>
/// 转成list
/// </summary>
public List<T> ToList()
{
return Table.ToList<T>();
}

public DataTable ToDataTable()
{
return Table.ToDataTable();
}

public string ToJson()
{
return Table.ToJson();
}
}
}

 

7.DbSelect

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

/// <summary>
/// zouhp 2017.6.16 V1
/// lambda 快速进行单表查询
/// </summary>
namespace GTJ.Core.Select
{
/// <summary>
/// lambda快速查询
/// </summary>
/// <typeparam name="T"></typeparam>
public class DbSelect<T> where T:new()
{
public OperationClass.OprationWhere<T> Select(params Expression<Func<T,string>>[] funcs)
{
OperationClass.OprationWhere<T> where = new OperationClass.OprationWhere<T>();
for (var i = 0; i < funcs.Length; i++)
{
where.column.Add(Helpers.GetClomun.GetDisplayName(funcs[i].Body));
}
return where;
}

public T Select(params Expression<Func<T, bool>>[] funcs)
{
OperationClass.OprationFindModel<T> Model = new OperationClass.OprationFindModel<T>();
return Model.FindModel(funcs);
}
}
}

 

lambda表达式封装对数据库的查询

标签：action   ann   帐号   sage   span   als   empty   etc   中文   

SQL多表连接查询语句，用Lambda表达式怎么写

一使用SELECT子句进行多表查询

SELECT 字段名 FROM 表1，表2 … WHERE 表1.字段 = 表2.字段 AND 其它查询条件

SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel AS b,tb_demo065 AS a WHERE a.id=b.id

注:在上面的的代码中，以两张表的id字段信息相同作为条件建立两表关联，但在实际开发中不应该这样使用，最好用主外键约束来实现

二使用表的别名进行多表查询

如:SELECT a.id,a.name,a.address,b.math,b.english,b.chinese FROM tb_demo065 a,tb_demo065_tel b WHERE a.id=b.id AND b.id='$_POST[textid]'

lambda表达式与数据库存储过程中最优化查询做比较 在千万级的数据加载查询中，谁更具优势，性能 、效益？

lambda表达式只是一个简单的复数比较吧。根据存储过程根本没可比性。

你这个比就是一行代码跟一套代码比。

而且千万级数据库只是幌子吧，实际数据库反应速度跟查询结果的返回行数成正版。

比如你lambda表达式返回三千万数据库，存储过程返回十条数据。在量的层面上已经不一致了。

要比较当然在同一个层面上。

lambda表达式逻辑单一，能走索引这个是优势

存储过程一般逻辑比较复杂，IOPS和CPU占用资源会很高。但是能不修改程序的情况下进行逻辑更新这个是优势

根本就是视情况而定，并不存在可比性。

显示全文