.NET开源

万丈高楼平地起做开发想要技术精进必须要有扎实的基础功底。

基础SQL查询语法一定要牢记于心才能应对后面更为复杂的形势。

SqlSugar

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool()..Where(ss.Idst.Id).Select(sSqlFunc.AggregateMax(s.Id))

//min:

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

z.Id

st.Id).Select(sSqlFunc.AggregateDistinctCount(s.Name))

//逗号分割列

SqlFunc.SubqueryableOrder().Where(zz.Idit.Id).SelectStringJoin(z

z.Name,

//SqlFunc.SubqueryableCustom().Where(c

c.Id

//SqlFunc.SubqueryableCustom().Where(c

c.Id

//SqlFunc.SubqueryableCustom().Where(c

c.Id

JoinQueryInfos(JoinType.Left,st.Idsc.Id)

.Where(st

SqlFunc.SubqueryableSchool().Where(s

s.Id

.ToList();/*生成的MYSQL语句如果是SqlServer就是TOP

SELECT

st.ID,st.SchoolId,st.Name,st.CreateTime

FROM

SqlFunc.SubqueryableSchool().Where(s

s.Idit.Id).Any()).ToList();/*生成的SQL(等于同于it.id

in(select

SqlFunc.SubqueryableSchool().Where(s

s.Id

it.Id).NotAny()).ToList();/*生成的SQL

SELECT

.Where(itit.IdSqlFunc.SubqueryableCustom().GroupBy(zz.OrderId).Select(zz.OrderId)).ToList()//Sql

//SELECT

db.QueryableOrder().In(itit.Id,db.QueryableOrder().Skip(1).Take(100).Select(itit.Id)).ToList();

3、子查询中使用Sql

SqlFunc.MappingColumnstring(sql)//老版本MappingColumn(default(string),sql)

}).ToList();//如果存在SQL拼接将SQL提取到外面在传进来有些VS拼接会有问题

Sql代码如下:

SqlFunc.SubqueryableCustom().Where(c

c.Id

it.CustomId).ToList()}).ToList();//也可以DTOSqlFunc.SubqueryableCustom().Where(c

c.Id

SqlFunc.SubqueryableCustom().Where(c

c.Id

it.CustomId).ToList(cc.Name)//最新版本//自动DTO

5.1.4.70

SqlFunc.SubqueryableCustom().Where(c

c.Id

SqlFunc.SubqueryableCustom().Where(c

c.Id

treeRootdb.QueryableTree().ToList();//可以联表查询只要返回List中有关联字段就可以//填充子对象

//原理

(list[0].id,list[1].id,list[2].id...)

内存分配到对应Child

进行关联查询有Select写到SetContext前面//可以额外加Where写SetContext前面但是不能带有item对象,item要写SetContext里面item.Child

db.QueryableTree().SetContext(x

x.ParentId,

item).ToList();//用例2可以多属性//item.Childxxxx//item.Parentxxxx//用例3类型转换类转成DTO

db.QueryableTree()//

ViewTree(){ParentIdx.ParentId,Idi.Id,Nameit.Name

.SetContext(x

item).ToList();//用例4联表填充//item.Childdb.QueryableTree()//.LeftJoinTree((i,y)i.ParentIdy.Id)//.Select((i,y)new

viewmodel(){

Parentidi.Parentid..........})//.SetContext(x

x.ParentId,

db.QueryableTree().ToListAsync();

await

db.QueryableTree().SetContextAsync(xx.ParentId,()item.Id,

item);

treeRootdb.QueryableTree().Where(it

it.Id

db.QueryableTree().SetContext(x

x.ParentId,

db.ThenMapper(treeRoot.SelectMany(itit.Child),

{it.Child

db.QueryableTree().SetContext(x

x.ParentId,

mydb.ThenMapper(root,item{...});

方式3导航方式

.LeftJoinB((i,y)i.ItemIdy.ItemId).InnerJoinC((i,y,z)

i.ItemId

z.ItemId).Where((i,y,z)i.ItemIdit.Aid).Any()).ToList();

WhereT1,T2((x,y)

SqlFunc.SubqueryableA().AS(A01)

A01表

//names名字1,名字2,名字3namesSqlFunc.Subqueryablecustom().Where(zz.oidit.id).SelectStringJoin(z

z.Name,

SqlFunc.MergeString(z.Name,-,z.Id.ToString()),

,)//去重复

.SelectStringJoin(itSqlFunc.MappingColumnstring($distinct

{it.UserId}),.)

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(ss.Idst.Id).Select(sSqlFunc.AggregateMax(s.Id))

//min:

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

s.Id

SqlFunc.SubqueryableSchool().Where(s

z.Id

st.Id).Select(sSqlFunc.AggregateDistinctCount(s.Name))

//逗号分割列

SqlFunc.SubqueryableOrder().Where(zz.Idit.Id).SelectStringJoin(z

z.Name,

idit.id,schoolnameSqlFunc.SubqueryableSchool().Where(ss.Idit.ShoolId).Select(ss.Name)

//schoolname(SELECT

SqlFunc.SubqueryableCustom().Where(c

c.Id

it.CustomId).First()}).ToList();

SqlFunc.SubqueryableCustom().Where(c

c.Id