提前体验可以升级到 5.0.9.2-Preview01版本及以上
答:用来兼容多种数据库的弱类型操作数据库语法
可以在这个基础上,封装到XML等配置 ( 自定义XML格式 )
//聚合过滤 ISugarQueryable<T> Having(IFuncModel model); //排序 ISugarQueryable<T> OrderBy(List<OrderByModel> models); //分组 ISugarQueryable<T> GroupBy(List<GroupByModel> models); //查询列 ISugarQueryable<T> Select(List<SelectModel> models); //表名 ISugarQueryable<T> AS(string tableName); //表名+别名 ISugarQueryable<T> AS(string tableName, string shortName); //联表查询 ISugarQueryable<T> AddJoinInfo(string tableName, string shortName, IFuncModel models, JoinType type = JoinType.Left); //查询条件 ISugarQueryable<T> Where(List<IConditionalModel> conditionalModels); //查询条件外面包一层 ISugarQueryable<T> Where(List<IConditionalModel> conditionalModels,bool isWrap);
//方式1:函数实现 var list=db.Queryable<object>() .AS("order", "o") .AddJoinInfo("orderdetail", "d", new ObjectFuncModel() { FuncName = "Equals", Parameters = new List<object>{ "d.orderid","o.id" } }, JoinType.Left) .Select(new List<SelectModel>() { new SelectModel() { AsName = "id", FiledName = "o.id" } }) .ToList(); //生成Sql如下 //SELECT `o`.`id` AS `id` FROM `Order` o Left JOIN `orderdetail` d ON (`d`.`orderid` = `o`.`id`) //方式2:多个条件 var onList = new ObjectFuncModel() { FuncName = "SqlFunc_Format", Parameters = new List<object>{ "d.orderid",">","o.id","&&","o.id",">","{int}:1" } }; var x = db.Queryable<object>() .AS("order", "o") .AddJoinInfo("orderdetail", "d", onList, JoinType.Left) .Select(selectItems) .ToList(); //SELECT `o`.`id` AS `id` FROM `order` o //Left JOIN `orderdetail` d ON `d`.`orderid` > `o`.`id` And `o`.`id` > @p0 //方式3:简单拼SQL (这种不能兼容多库说白了就是写SQL) AddJoinInfo("table1", "a", "a.id=o.id and a.id>1", JoinType.Left);
//方式1 多库兼容 var orderList = new List<OrderByModel>{ new OrderByModel(){ FieldName="id", OrderByType=OrderByType.Desc}, new OrderByModel(){ FieldName="name", OrderByType=OrderByType.Asc } }; var list=db.Queryable<object>().AS("order").OrderBy(orderList).ToList(); //SELECT * FROM `Order` ORDER BY `id` DESC , `name` ASC //方式2 直接写SQL,不在乎多库兼容可以用 var list=db.Queryable<object>().AS("order").OrderBy("id desc,name asc").ToList();
//方式1:多库兼容 var groupList=new List<GroupByModel> { new GroupByModel(){ FieldName="id" } }; //Sql: `id` var having=new ObjectFuncModel() { //嵌套函数的实现 FuncName= "GreaterThan", Parameters= new List<object> { new ObjectFuncModel(){ FuncName="AggregateAvg", Parameters=new List<object>(){ "ID" } },"{int}:1" } } //Sql: (AVG(`ID`) > @p0) var list=db.Queryable<object>().AS("order").GroupBy(groupList) .Having(having) .Select("ID").ToList() //SELECT ID FROM `Order` GROUP BY `id` HAVING (AVG(`ID`) > @p0) //方式2: 直接写SQL var list=db.Queryable<object>().AS("order").GroupBy("id") .Having("avg(id)>@p") .AddParameters(new {p=1}) .Select("ID").ToList()
//方式1:多库兼容 var selector= new List<SelectModel>() { new SelectModel(){AsName = "id1",FiledName = "id"}, new SelectModel(){ FiledName = "id"}}; var list=db.Queryable<object>().AS("order").Select(selector).ToList(); //SELECT `id` AS `id1` , `id` AS `id` FROM `Order` //方式2:直接写SQL var list=db.Queryable<object>().AS("order").Select("ID AS id1,id as id").ToList();
支持的函数大致上和SqlFunc.xxx 相同
new ObjectFuncModel() { //嵌套函数的实现 FuncName= "GreaterThan", Parameters= new List<object> { new ObjectFuncModel(){ FuncName="AggregateAvg", Parameters=new List<object>(){ "ID" } },"{int}:1" } } //Sql: (AVG(`ID`) > @p0) //可以使用的地方 // 带IFuncModel重载的方法, 比如 Having(IFuncModel model) 和 AddJoinInfo(+4重载) // 带有SelectModel重载方法 new SelectModel(){ FiledName=FuncModel} // 带有OrderByModel重载方法 new OrderByModel(){ FiledName=FuncModel}
支持多库 ,也方便构造或者前端序列化 ,手写硬编码逻辑比较吃力,代码量多,适合封装使用
//手动构造 var conModels = new List<IConditionalModel>(); conModels.Add(new ConditionalModel{ FieldName = "id",ConditionalType=ConditionalType.Equal,FieldValue="1"}); conModels.Add(new ConditionalModel{ FieldName = "name",ConditionalType=ConditionalType.Equal,FieldValue="jack"}); //可以设置类型 CSharpTypeName="int" var student = db.Queryable<Student>().Where(conModels).ToList(); //select * from Student where id=1 and name = 'jack' //详细用法:https://www.donet5.com/Home/Doc?typeId=2314
用起来灵活也支持多库,不方便封装,适合手写
var whereFunc = new ObjectFuncModel() { FuncName = "SqlFunc_Format", Parameters = new List<object>{ "id",">","{int}:1","&&","name","=","{string}:a" } }; db.Queryable<object>().AS("Order").Where(whereFunc).ToList(); //生成的Sql //SELECT * FROM [Order] WHERE [id] > @p0 AND [name] = @p1 参数说明: (1)变量 {string}:a 等于 @p ,new {p=a.ToString()} (2)拼接符号 ">", ">=", "<", "<=", "(", ")", "=", "||", "&&","&","|","null","is","isnot" //like nolike (5.1.3.31-Privew05版本支持) (3)函数 {SqlFunc_AggregateMin:["id"]} (4)字段 不是上面3种就认为是字段(会进行严格SQL注入验证) id 就会变成 "id" 或者 [id] 或者 `id`
用起来简单 对多库兼容差
db.Queryable<object>().AS("Order").Where("id=@id".new {id=1}).ToList();
2016 © donet5.comApache Licence 2.0